Utku Cansever Utku Cansever - 3 months ago 8
SQL Question

Stuck on Sql Query with time interval

I want to get best 3 day of users between "2014-07-01" and "2014-08-01"

Could someone help me? I've been stuck here for 3 days.

In real score table entries are 10:00 to 22:00 and 1 entries for each hour.

Total of 12 entry for each day and each player (sometimes it could be less 1 or 2).

This is the output I'm trying to get:

ID | User_ID | Username | Sum(Score) | Date
--------------------------------------------------
1 | 1 | Xxx | 52 | 2014-07-01
2 | 1 | Xxx | 143 | 2014-07-02
3 | 2 | Yyy | 63 | 2014-07-01
...


Score
table:

ID | User_ID | Score | Datetime
-----------------------------------------
1 | 1 | 35 | 2014-07-01 11:00:00
2 | 1 | 17 | 2014-07-01 12:00:00
3 | 2 | 36 | 2014-07-01 11:00:00
4 | 2 | 27 | 2014-07-01 12:00:00
5 | 1 | 66 | 2014-07-02 11:00:00
6 | 1 | 77 | 2014-07-02 12:00:00
7 | 2 | 93 | 2014-07-02 12:00:00
...


User
table :

ID | Username
--------------
1 | Xxx
2 | Yyy
3 | Zzz
...

Answer

I think you need to aggregate first by date, and then choose the first three using row_number(). To do the aggregation:

select s.user_id, sum(s.datetime, 'day') as theday, sum(score) as score,
       row_number() over (partition by s.user_id order by sum(score) desc) as seqnum
from scores s
group by s.user_id;

To get the rest of the information, use this as a subquery or CTE:

select u.*, s.score
from (select s.user_id, sum(s.datetime, 'day') as theday, sum(s.score) as score,
             row_number() over (partition by s.user_id order by sum(s.score) desc) as seqnum
      from scores s
      group by s.user_id
     ) s join
     users u
     on s.user_id = u.users_id
where seqnum <= 3
order by u.user_id, s.score desc;
Comments