Question 63638 in PhpMyAdmin Question posted in
The official documentation can be found here.

Query database table – Phpmyadmin

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

enter image description here

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search