-1

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

UserSessions table: 
---------------------
SessionID (int) PK, 
UserID (int), 
datetime (datetime)

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.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Ken Skywalker
  • 11
  • 1

1 Answers1

0

you could use a group by ..eg:

  select UserId, count(*)
  from my_table 
  where datetime between date_sub(now(), interval 30 day ) 
                        AND date_sub(now(), interval 1 day )
  group by UserID
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107