SQL Select only players that have played x amount of games in recent y days?

Question

Q: How do I make MySQL only SELECT all users that have played => x amount of games within the y most recent days?

This is all used for a gaming ladder. I use two tables: weblPlayers has only the user info (username, email, password etc), and weblGames has the result of every reported game along with the date and time, properly formatted, of when it was reported.

Here are the screenshots of them:

weblGames

WeblPlayers has the fields: player_id | name | email | etc | etc. The names found of a winner or loser in weblGames when a game is reported correspond to same names found in WeblPlayers.

Answer 1

SELECT userid, count(*) as numgames
from weblGames g
where g.dateplayed >= now() - y
group by userid
having count(*) > x
;

You can always join it back to weblPlayers to get the rest of the info.

[Edited]

Sorry - hadn't noticed the winner and loser thing. Try:

SELECT userid, count(*) as cnt 
FROM
(
    SELECT winner as userid
    from weblGames g
    where g.reported_on >= now() - y
    UNION ALL
    SELECT loser as userid
    from weblGames g
    where g.reported_on >= now() - y
) t
GROUP BY userid
HAVING COUNT(*) > x;

Answer 2

This is in MSSQL syntax, but gives you the idea. The only non-portable stuff should be the DATEADD|DIFF to get today's date.

SELECT Name 
FROM (
    SELECT
        winner as Name,
        reported_on
    FROM webIGames
    UNION ALL
    SELECT
        loser as Name,
        reported_on
    FROM webIGames
) as AllPlayers
WHERE
     reported_on >= DATEADD(dd, 0 + @y, DATEDIFF(dd, 0, GETDATE()))
GROUP BY
     Name
HAVING
     COUNT(*) > @x

The trick is to a) get both Winners and Losers (the UNION ALL), and use HAVING to restrict the results after the GROUP BY.