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

predssql.txt

[fenway]

TLDR -- you'll need to simplify this example if you want some help -- I can't parse that in my head.

[MargateSteve]

I thought that might be the problem fenway, as none of the usual top n methods I know of will work with these joins. The trouble is, to simplify it would take something away that is vital, however, as I am now close to the solution (thanks to a bit of help elsewhere and some perseverance) the 'missing link' may seem straightforward.

 

The required outcome in a nutshell....

Members predict x amount of games per week. PPP is the average points scored.

What I want to do is group all results by week (YearWeek as it will run for more than a year) and show the top 3 users (highest PPP) per week.

 

Where I am now is completely different to the attempt above and the current query is below. Everything is working correctly except showing the top 3 members per week. All PPP's are grouped by week and by member, the highest scoring member is RNK 1, next is RNK 2 so everything works fine until the request to limit it to top 3 per week. The bizarre thing is that it is sort of working, but restricted to only odd number ranks (RNK). So HAVING RNK <=3 returns members with RNK 1 or 3. HAVING RNK <=7 returns members with RNK 1, 3, 5 or 7 and so on. My guess is that the 'HAVING' clause needs to go somewhere different or be replaced completely with another way of restricting the result.

 

Trouble is, I am all out of ideas having got to this point so if anyone can show me the obvious, I would be grateful.

 

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 <= 3

[MargateSteve]

I finally cracked this last night so thought I would share it in the unlikely event that someone else would find it useful. In the end, all I had to do was wrap the whole thing in one more query.

Select

  RNKING.WST,

  RNKING.WND,

  RNKING.RNK,

  RNKING.PLYR,

  RNKING.PPP,

  RNKING.TOT

From

  (Select

    SCORES.PLYR,

    SCORES.PPP,

    SCORES.WST,

    SCORES.WND,

    SCORES.YWK,

    Case When @currweek != SCORES.YWK Then @rank := 1 Else @rank := @rank + 1

    End As rank,

    @rank As RNK,

    @currweek := SCORES.YWK As t,

    @currweek As tt,

    SCORES.TOT

  From

    (Select

      U.username PLYR,

      YearWeek(G.date, 1) YWK,

      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,

      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,

      U.userid

    From

      users U Inner Join

      predictions P On U.userid = P.user Inner Join

      games G On G.gamesid = P.game Inner Join

      compgame CG On G.gamesid = CG.game

    Where

      CG.comp Not In (1, 3) and

G.date < Now()

    Group By

      YearWeek(G.date, 1), U.userid

    Order By

      YearWeek(G.date, 1),PPP Desc) SCORES) RNKING

Having

  RNK <= 3