Top n per week

[MargateSteve]

On the surface, the top n per group query is a fairly common one and I have used a few variations on other projects. However, I really cannot get my head around this one and I think it is due to the number of joins.

My query (at the bottom of the post) is trying to find the highest 3 scoring players per week. The score field is PPP.

So, in theory, I am looking at grouping by week, then by player (to get the sum of his scores), find the 3 players with the highest PPP per week and them sort them by PPP desc along with, ideally, giving them a rank number.

I know I would be able to do something that works with php but as I am sure doing it all in a query would be more server-friendly, I am determined to get this right.! The query below is not the only attempt I have had but it is the closest I have got to it working. The most often found solution LEFT OUTER JOIN table t2 ON (t1.id = t2.id AND t1.date < t2.date) did not come anywhere near working.

The parts of the output are correct are the grouping by YearWeek and User and the sum of that users PPP. It also does correctly select 3 users from each week (as long as there are 3 available) but not the highest scoring 3 and not in correct rank order.

So, in my head, I feel like I am close to getting it right but obviously may be a mile away! I have attached an SQL dump of the tables and data in case they will help.

Thanks in advance for any suggestions.
Steve

THE QUERY

Select
x.PLYR, x.PPP, x.WST, x.WND, x.rank, x.YWK
From
(Select
U.username PLYR,
Sum(Case When P.home = G.hgoals And P.away = G.agoals Then 3
When (P.home > P.away And G.hgoals > G.agoals) Or
(P.home < P.away And G.hgoals < G.agoals) Or
(P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
Else 0 End) PTS,
Sum(Case
When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
And G.agoals Is Not Null Then 1 Else 0 End) TOT,
Sum(Case When P.home = G.hgoals And P.away = G.agoals Then 3
When (P.home > P.away And G.hgoals > G.agoals) Or
(P.home < P.away And G.hgoals < G.agoals) Or
(P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
Else 0 End) / Sum(Case
When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
And G.agoals Is Not Null Then 1 Else 0 End) PPP,
Date_Add(G.date, Interval (0 - WeekDay(G.date)) Day) WST,
Date_Add(G.date, Interval (6 - WeekDay(G.date)) Day) WND,
Week(G.date) WK,
CASE
WHEN @currweek != YearWeek(G.date, 1) THEN @rank := 1
ELSE @rank := @rank + 1
END AS rank,
@currweek := YearWeek(G.date, 1) YWK
From
compuser CU Inner Join
comps C On C.compid = CU.comp Left Join
users U On U.userid = CU.user Inner Join
compgame CG On C.compid = CG.comp Inner Join
games G On G.gamesid = CG.game Left Join
predictions P On CU.user = P.user And CG.game = P.game
JOIN (SELECT @rank := NULL, @currweek := '') r
Where
G.date < Now()
Group By
YearWeek(G.date, 1), U.userid
Order By
YearWeek(G.date, 1), PPP Desc) x
Where
x.rank <= 3

[Biiim]

I'm having a look at it - looks like a challenge!

indexes were missing on these - dropped the query from 90 seconds to 6 seconds for me
CU.comp
CU.user
CG.comp
CG.game
P.user

[Biiim]

SELECT compuserid,`PTS`,`TOT`,`PPP`,WST,WND,WK,
        CASE
        WHEN @currweek != YWK THEN @rank := 1
        ELSE @rank := @rank + 1
        END AS rank,
        @currweek := YWK AS t,@currweek AS tt
    FROM (SELECT compuserid,SUM(PTS) AS `PTS`,SUM(TOT) AS `TOT`,SUM(PTS)/SUM(TOT) AS `PPP`,WST,WND,WK,YWK
        FROM (SELECT CU.compuserid,
                (CASE WHEN P.home = G.hgoals AND P.away = G.agoals THEN 3
                WHEN (P.home > P.away AND G.hgoals > G.agoals) OR
                (P.home < P.away AND G.hgoals < G.agoals) OR
                (P.home <> G.hgoals AND G.hgoals = G.agoals AND P.home = P.away) THEN 1
                ELSE 0 END) PTS,
                (CASE WHEN P.home IS NOT NULL AND P.away IS NOT NULL AND G.hgoals IS NOT NULL
                AND G.agoals IS NOT NULL THEN 1 ELSE 0 END) TOT,
                (CASE WHEN P.home = G.hgoals AND P.away = G.agoals THEN 3
                WHEN (P.home > P.away AND G.hgoals > G.agoals) OR
                (P.home < P.away AND G.hgoals < G.agoals) OR
                (P.home <> G.hgoals AND G.hgoals = G.agoals AND P.home = P.away) THEN 1
                ELSE 0 END) / (CASE
                WHEN P.home IS NOT NULL AND P.away IS NOT NULL AND G.hgoals IS NOT NULL
                AND G.agoals IS NOT NULL THEN 1 ELSE 0 END) PPP,
                DATE_ADD(G.date, INTERVAL (0 - WEEKDAY(G.date)) DAY) WST,
                DATE_ADD(G.date, INTERVAL (6 - WEEKDAY(G.date)) DAY) WND,
                WEEK(G.date) WK,
                YEARWEEK(G.date, 1) YWK
            FROM compuser CU
            LEFT JOIN comps C ON c.compid = CU.comp
            LEFT JOIN users U ON U.userid = CU.user
            LEFT JOIN compgame CG ON C.compid = CG.comp
            LEFT JOIN games G ON G.gamesid = CG.game
            LEFT JOIN predictions P ON CU.user = P.user AND CG.game = P.game
            WHERE 
            G.date < NOW()
            ORDER BY G.date ASC
            ) AS `datatable` 
    GROUP BY YWK, compuserid
    ORDER BY YWK ASC, PPP DESC) AS `groupedtable`
;

Can i have a medal please?

[MargateSteve]

You can't have the medal yet but it is certainly on order!!

It is very nearly there in so much as the grouping and sorting function is working spot on. The only bits missing are limiting each week to the top 3 members and the fact that the rank is showing as null (and a small typo in the table alias on line 28).

However, massive thanks for getting it this close. I have had a quick play to see if I can get the rank working but tired eyes (2.15am here) are not helping so will have another look in the morning.

Thanks again
Steve

[MargateSteve]

Close but frustratingly still not there. I have succesfully got the query to rank the members in the correct order and all that is left is to limit it to the top 3 per week.

I have come close in the code below but it is giving a very strange outcome. Quite simply it only returns odd numbered ranks, so anything ranked 1 or 3. I tried changing it to <=5 and that returned ranks 1,3,5 so only odd ones again.

Also, that final 'WHERE' looks like it could be shortened, but trying groupedtable.RNK returned a 'column not found' error.

Select
  groupedtable.compuserid,
  groupedtable.PTS,
  groupedtable.TOT,
  groupedtable.PPP,
  groupedtable.WST,
  groupedtable.WND,
  groupedtable.WK,
  Case When @currweek != groupedtable.YWK Then @rank := 1
    Else @rank := @rank + 1 End As RNK,
  @currweek := groupedtable.YWK As t,
  @currweek As tt
From
  (Select
    datatable.compuserid,
    Sum(datatable.PTS) As PTS,
    Sum(datatable.TOT) As TOT,
    Sum(datatable.PTS) / Sum(datatable.TOT) As PPP,
    datatable.WST,
    datatable.WND,
    datatable.WK,
    datatable.YWK,
    datatable.DTE
  From
    (Select
      CU.compuserid,
      (Case When P.home = G.hgoals And P.away = G.agoals Then 3
        When (P.home > P.away And G.hgoals > G.agoals) Or
        (P.home < P.away And G.hgoals < G.agoals) Or
        (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
        Else 0 End) PTS,
      (Case
        When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
        And G.agoals Is Not Null Then 1 Else 0 End) TOT,
      (Case When P.home = G.hgoals And P.away = G.agoals Then 3
        When (P.home > P.away And G.hgoals > G.agoals) Or
        (P.home < P.away And G.hgoals < G.agoals) Or
        (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
        Else 0 End) / (Case
        When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
        And G.agoals Is Not Null Then 1 Else 0 End) PPP,
      Date_Add(G.date, Interval (0 - WeekDay(G.date)) Day) WST,
      Date_Add(G.date, Interval (6 - WeekDay(G.date)) Day) WND,
      Week(G.date) WK,
      G.date DTE,
      YearWeek(G.date, 1) YWK
    From
      compuser CU Left Join
      comps C On C.compid = CU.comp Left Join
      users U On U.userid = CU.user Left Join
      compgame CG On C.compid = CG.comp Left Join
      games G On G.gamesid = CG.game Left Join
      predictions P On CU.user = P.user And CG.game = P.game
    Where
      G.date < Now()  

    Order By
      G.date,
      (Case When P.home = G.hgoals And P.away = G.agoals Then 3
        When (P.home > P.away And G.hgoals > G.agoals) Or
        (P.home < P.away And G.hgoals < G.agoals) Or
        (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
        Else 0 End) / (Case
        When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
        And G.agoals Is Not Null Then 1 Else 0 End) Desc) As datatable

    Group By
    datatable.compuserid, YearWeek(datatable.DTE, 1)
  Order By
    datatable.YWK,
    Sum(datatable.PTS) / Sum(datatable.TOT) Desc) As groupedtable
    WHERE
      Case When @currweek != groupedtable.YWK Then @rank := 1
    Else @rank := @rank + 1 End <= 3

[MargateSteve]

Still no joy, despite having played around with it all. Have replaced the last WHERE clause with 'HAVING' instead, but it still only returns odd numbered ransk (1,3,5).

Select
  groupedtable.compuserid,
  groupedtable.PTS,
  groupedtable.TOT,
  groupedtable.PPP,
  groupedtable.WST,
  groupedtable.WND,
  groupedtable.WK, 
  groupedtable.DTE,
  (Case When @currweek != YWK Then @rank := 1
    Else @rank := @rank + 1 End) RNK,
  @currweek := YWK As t,
  @currweek As tt
From
  (Select
    datatable.compuserid,
    Sum(datatable.PTS) As PTS,
    Sum(datatable.TOT) As TOT,
    Sum(datatable.PTS) / Sum(datatable.TOT) As PPP,
    datatable.WST,
    datatable.WND,
    datatable.WK,
    datatable.YWK,
    datatable.DTE
  From
    (Select
      CU.compuserid,
      (Case When P.home = G.hgoals And P.away = G.agoals Then 3
        When (P.home > P.away And G.hgoals > G.agoals) Or
        (P.home < P.away And G.hgoals < G.agoals) Or
        (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
        Else 0 End) PTS,
      (Case
        When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
        And G.agoals Is Not Null Then 1 Else 0 End) TOT,
      (Case When P.home = G.hgoals And P.away = G.agoals Then 3
        When (P.home > P.away And G.hgoals > G.agoals) Or
        (P.home < P.away And G.hgoals < G.agoals) Or
        (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
        Else 0 End) / (Case
        When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
        And G.agoals Is Not Null Then 1 Else 0 End) PPP,
      Date_Add(G.date, Interval (0 - WeekDay(G.date)) Day) WST,
      Date_Add(G.date, Interval (6 - WeekDay(G.date)) Day) WND,
      Week(G.date) WK,
      G.date DTE,
      YearWeek(G.date, 1) YWK
    From
      compuser CU Left Join
      comps C On C.compid = CU.comp Left Join
      users U On U.userid = CU.user Left Join
      compgame CG On C.compid = CG.comp Left Join
      games G On G.gamesid = CG.game Left Join
      predictions P On CU.user = P.user And CG.game = P.game
    Where
      G.date < Now()
    Order By
      G.date,
      (Case When P.home = G.hgoals And P.away = G.agoals Then 3
        When (P.home > P.away And G.hgoals > G.agoals) Or
        (P.home < P.away And G.hgoals < G.agoals) Or
        (P.home <> G.hgoals And G.hgoals = G.agoals And P.home = P.away) Then 1
        Else 0 End) / (Case
        When P.home Is Not Null And P.away Is Not Null And G.hgoals Is Not Null
        And G.agoals Is Not Null Then 1 Else 0 End) Desc) As datatable  
  Group By
    datatable.compuserid, YearWeek(datatable.DTE, 1)
  Order By
    datatable.YWK,
    Sum(datatable.PTS) / Sum(datatable.TOT) Desc) As groupedtable      
   HAVING RNK <=5