Tricky SQL query

Question

Really tricky SQL statement I have here. Trying to build this query for about hour. Maybe You can help me.

We have a table with 3 columns: gamename | user | times_played

The query should select top three games (depending total times_played) and top three user who has played most times in this game => 9 rows.

The result is like:
CounterStrike | Smith 
CounterStrike | Jonny
Counterstrike | Hans
WoW           | George
WoW           | Bob
Wow           | Frank
Need For Speed| James
Need For Speed| Marion
Need For Speed| Scarlet

Would be very nice, if you could help me =) Thanks!

Answer 1

Update:

As @Steve Kass pointed out, I didn't notice that you only wanted the first three games.

Here's the updated version:

In SQL Server, Oracle and PostgreSQL 8.4:

SELECT  gamename, user
FROM    (
        SELECT  r.gamename, user,
                ROW_NUMBER() OVER (PARTITION BY game ORDER BY times_played DESC) rn,
        FROM    (
                SELECT  gamename, ROW_NUMBER() OVER (ORDER BY SUM(times_played) DESC) AS game_rn
                FROM    results
                GROUP BY
                        gamename
                ) g
        JOIN    results r
        ON      r.gamename = g.gamename
        WHERE   game_rn <= 3
        ) q
WHERE   rn <= 3
ORDER BY
        gamename, times_played DESC

In MySQL:

SELECT  ro.gamename, ro.user
FROM    (
        SELECT  gamename, SUM(times_played) AS rank
        FROM    results
        ORDER BY
                rank DESC
        LIMIT 3
        ) rd
JOIN    results ro
ON      ro.gamename >= rd.gamename
        AND ro.gamename <= rd.gamename
        AND
        (ro.times_played, ro.id) <=
        (
        SELECT  ri.times_played, ri.id
        FROM    results ri
        WHERE   ri.gamename = rd.gamename
        ORDER BY
                ri.times_played DESC, ri.id DESC
        LIMIT 2, 1
        )
ORDER BY
        gamename, times_played DESC

You will need a PRIMARY KEY for this query to work, assuming it is called id.

This is explained in more detail in this article in my blog:

• Advanced row sampling

In PostgreSQL 8.3 and below:

SELECT  gamename, ((ri)[s]).user
FROM    (
        SELECT  gamename, ri, generate_series(1, 3) AS s
        FROM    (
                SELECT  ro.gamename,
                        ARRAY
                        (
                        SELECT  ri
                        FROM    results ri
                        WHERE   ri.gamename = ro.gamename
                        ORDER BY
                                times_played DESC
                        LIMIT 3
                        ) AS ri
                FROM    (
                        SELECT  gamename, SUM(times_played) AS rank
                        FROM    results
                        ORDER BY
                                rank DESC
                        LIMIT 3
                        ) rd
                ) q
        ) q2
ORDER BY
        gamename, s

Answer 2

I don't think Quassnoi noticed that you asked for the top users only for the top three games (based on total times_played). Here's a query for that (not tested on real data, since no CREATE TABLE and INSERT statements were given). I also include ties, which Quassnoi didn't, just to show you that option.

with GamesPlays(gamename,totalPlays) as (
  select
    gamename, sum(times_played)
  from results
  group by gamename
), GamesRanked(gamename,gameRank) as (
  select
    gamename,
    rank() over (
      order by totalPlays desc
    )
  from GamesPlays
), ResultsRanked(gamename,user,userRank) as (
  select
    gamename,
    user,
    rank() over (
      partition by user
      order by times_played desc
    )
  from results;
)
  select
    G.gamename, R.user
  from ResultsRanked as R
  join GamesRanked as G
  on G.gamename = R.gamename
  where gameRank <= 3
  and userRank <= 3
  order by 
  gameRank,userRank;