title: Tricking Weekofyear in Hive by shifting the week, for counting
body: <p>I've been working on this problem for a while now. Basically I have a simple set of data with UserId, and TimeStamp. I want to know how many distinct UserId's appear each week, the catch is my week is measured in Sunday-Saturday, NOT Monday - Sunday, which is what Weekofyear() uses.</p>
  
  <p>Right now I'm hardcoding each week and running the query:</p>
  
  <pre><code>SELECT
  count(distinct UserId)
  FROM data.table
  where from_unixtime((CAST(timestamp as BIGINT))) 
         between TO_DATE("2016-06-05") AND TO_DATE("2016-06-12")
  </code></pre>
  
  <p>I'm trying to find a way to shift the timestamp back a day to trick weekofyear into thinking my Sunday is actually a Monday, but have not been successful. My latest futile attempt looked like:</p>
  
  <pre><code>SELECT
  count(distinct UserId), weekofyear(date_sub(from_unixtime(CAST(timestamp as BIGINT)),1))
  FROM table.data
  where from_unixtime((CAST(timestamp as BIGINT)))
         between TO_DATE("2016-06-01") AND TO_DATE("2016-06-30")
         group by weekofyear(date_sub(from_unixtime(CAST(timestamp as BIGINT)),1))
  </code></pre>
  
  <p>This results in the same numbers as if I didn't subtract a day. I not sure why this isn't working. I feel like there should be a way to manage this. Right now if I wanted to pull all the data by week WHERE X is true, I'd have to manually do each week, that won't be sustainable. Any suggestions on how to work smarter?</p>
  
  <p>Thank you. </p>