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
Anonymous 4 years 1 Answer 899 views 0

Answer ( 1 )

  1. select date, lag(count(distinct id),7) over (order by date)
    from table

Leave an answer

Browse
Browse