Query database table-CodePudding

I have a table where I save plays for players over a specific game on it.
the game has different levels.
the player can play the same level multiple times.
I need to query that table to count the number of levels that a specific player played today for the first time over a specific date.
for example, in my picture, I need to know new levels for a player that has ID = 267 over the date "2022-09-01".
the query result should be 1 level as the player already played levels 1 and 2 before that specific date.

 ---- ----------- --------- ---------- ------- --------------------- ---------------------     
| id | player_id | game_id | level_id | score | created_at          | updated_at          |  
 ---- ----------- --------- ---------- ------- --------------------- ---------------------   
|  1 |       267 |      11 |        1 |   350 | 2022-08-28 00:28:52 | 2022-08-28 00:28:52 |  
|  2 |       267 |      11 |        2 |   150 | 2022-08-28 00:32:52 | 2022-08-28 00:32:52 |  
|  3 |       267 |      11 |        1 |   175 | 2022-09-01 00:28:52 | 2022-09-01 00:28:52 |  
|  4 |       267 |      11 |        2 |   125 | 2022-09-01 00:32:52 | 2022-09-01 00:32:52 |  
|  5 |       267 |      11 |        3 |   115 | 2022-09-01 00:35:52 | 2022-09-01 00:35:52 |  
 ---- ----------- --------- ---------- ------- --------------------- ---------------------

CodePudding user response：

Here is a raw MySQL query which should work:

SELECT game_id, COUNT(*) AS cnt
FROM yourTable t1
WHERE player_id = 267 AND
      DATE(t1.created_at) = '2022-09-01' AND
      NOT EXISTS (
          SELECT 1
          FROM yourTable t2
          WHERE t2.game_id = t1.game_id       AND
                t2.player_id = t1.player_id   AND
                t2.created_at < t1.created_at AND
                t2.level_id = t1.level_id
      )
GROUP BY game_id;

This query counts every player 267 record for which we cannot find an earlier record by the same player having that same level.

CodePudding user response：

Use conditional aggregation:

SELECT game_id,
       COUNT(DISTINCT CASE WHEN DATE(created_at) <= '2022-09-01' THEN level_id END) - 
       COUNT(DISTINCT CASE WHEN DATE(created_at) < '2022-09-01' THEN level_id END) count
FROM tablename
WHERE player_id = 267
GROUP BY game_id;

or:

SELECT game_id,
       COUNT(DISTINCT level_id) - 
       COUNT(DISTINCT CASE WHEN DATE(created_at) < '2022-09-01' THEN level_id END) count
FROM tablename
WHERE player_id = 267 AND DATE(created_at) <= '2022-09-01'
GROUP BY game_id;

See the demo.