CODEWITHSUNDEEP
sqlhive
DataTx
DataTx

Reputation: 1869

Restart Row Number Based on Date and Increments of N

I have a table with the following data that I generated with a date table

date     day_num (DAY_NUM % 7)
2019-07-09  0   0
2019-07-10  1   1
2019-07-11  2   2
2019-07-12  3   3
2019-07-13  4   4
2019-07-14  5   5
2019-07-15  6   6
2019-07-16  7   0

I basically want to get a week number that restarts at 0 and I need help figuring out the last part

The final output would look like this

date     day_num (DAY_NUM % 7) week num
2019-07-09  0   0        1
2019-07-10  1   1        1
2019-07-11  2   2        1
2019-07-12  3   3        1
2019-07-13  4   4        1
2019-07-14  5   5        1
2019-07-15  6   6        1
2019-07-16  7   0        2

This is the sql I have so far

select 
SUB.*,
DAY_NUM%7
FROM(
SELECT 
  DISTINCT 
  id_date,
  row_number() over(order by id_date) -1 as day_num 
FROM schema.date_tbl
WHERE Id_date BETWEEN "2019-07-09" AND date_add("2019-07-09",146)

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Building on your query:

select SUB.*, DAY_NUM%7,
       DENSE_RANK() OVER (ORDER BY FLOOR(DAY_NUM / 7)) as weeknum
FROM (SELECT DISTINCT id_date,
             row_number() over(order by id_date) -1 as day_num 
      FROM schema.date_tbl
      WHERE Id_date BETWEEN "2019-07-09" AND date_add("2019-07-09", 146)
     ) x

Upvotes: 1

Related Questions