Suppose we have a table Named
UserSessions. Each user can have multiple sessions in a day or across different days. The columns of this table are
SessionID (int) PK,
and sample data look like this:
1, 1001, 2017-01-01
2, 1001, 2017-01-01
3, 1002, 2017-01-01
4, 1002, 2017-01-02
Now, we want to write a query that display the count of distinct users in last 30 days for each day. What is the best way to do this?
An obvious solution is to use self-join, but we hope we can avoid that.
Does it has something to do with the window function? I am wondering if there is a feature that, for each line, we can aggregate within x rows ahead and y rows after.
For example, similar questions like "what is the average of orange price in a window of 30 days ahead of each day recorded so far" etc. To be more specific, it's like asking an aggregation over a period of x-30 to x-1 days on day x.