Past 7 days Rolling Count distinct
Question
I have date & ids in two columns. Ids can repeat on each date and on multiple dates as well.
I want to calculate the count distinct ids which are there present for last 7 days with respect to given date.
Eg. for 7 jan we need count distinct from 1st to 7th , for 8th we need from 2nd to 8th jan.
in progress
0
SQL
4 years
1 Answer
902 views
0
Answer ( 1 )
select date, lag(count(distinct id),7) over (order by date)
from table