Query database table

Question

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 |  
+----+-----------+---------+----------+-------+---------------------+---------------------+

Please post data as text not images. – Serg Sep 04 '22 at 08:58 — Serg, Sep 04 '22 at 08:58

Tim Biegeleisen · Answer 1 · 2022-09-04T09:36:01.127

0

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.

edited Sep 04 '22 at 09:36

answered Sep 04 '22 at 08:59

Tim Biegeleisen

502,043
27
286
360

Probably "specific game" means `game_id` is of importance too. – Serg Sep 04 '22 at 09:03
I have updated my answer per your feedback. – Tim Biegeleisen Sep 04 '22 at 09:07
yes, it is per game ID this is right. I tried the query it return count 3. – Bebo Sep 04 '22 at 09:24
but when I added another where condition over date it returns 1. SELECT player_id, game_id, COUNT(*) AS counter FROM game_levels_plays t1 WHERE player_id = 267 AND date(t1.created_at) = date("2022-09-01") AND NOT EXISTS ( SELECT 1 FROM game_levels_plays 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; – Bebo Sep 04 '22 at 09:25

forpas · Answer 2 · 2022-09-04T09:45:36.843

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.

Query database table

2 Answers2