Daily Active Players From the Login Log — SQL Dates Problem
The game client writes a LoginEvent every time a player signs in, and many players sign in several times a day.
- Difficulty: Easy
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #178
Problem statement
The game client writes a LoginEvent every time a player signs in, and many players sign in several times a day. The live-ops dashboard shows daily active players: the number of distinct players who logged in on each calendar day.
Return the columns login_day (the date part of login_at, as YYYY-MM-DD) and active_players, one row per day that has at least one login, ordered by login_day.
Tables
Table: LoginEvent
| Column | Type |
|---|---|
| event_id | int |
| player_id | int |
| login_at | datetime |
| platform | enum(android, ios, pc) |
Primary key: event_id.
One row per successful sign-in. Times are server time (IST).
Examples
Example 1
LoginEvent
| event_id | player_id | login_at | platform |
|---|---|---|---|
| 1 | 7 | 2025-04-14 09:12:00 | android |
| 2 | 7 | 2025-04-14 21:40:10 | android |
| 3 | 9 | 2025-04-14 23:59:59 | pc |
| 4 | 9 | 2025-04-15 00:00:05 | pc |
| 5 | 3 | 2025-04-15 18:30:00 | ios |
| 6 | 7 | 2025-04-15 19:02:44 | ios |
| 7 | 3 | 2025-04-17 07:45:00 | android |
Output
| login_day | active_players |
|---|---|
| 2025-04-14 | 2 |
| 2025-04-15 | 3 |
| 2025-04-17 | 1 |
How to solve Daily Active Players From the Login Log
Two ideas: bucket each sign-in by its calendar day, and count each player once per bucket. DATE(login_at) turns 2025-04-14 23:59:59 into 2025-04-14, so grouping by it puts every sign-in of a day together — and a sign-in five seconds after midnight lands on the next day, as it should.
Inside each group COUNT(DISTINCT player_id) counts players rather than events, so player 7 signing in twice on the 14th counts once. Days with no sign-ins have no rows and therefore no group; filling them with zeros would need a calendar to join against.
Because the format is fixed, LEFT(login_at, 10) or DATE_FORMAT(login_at, '%Y-%m-%d') produce the same key. Another way is to first take the DISTINCT (day, player) pairs and then count rows per day. All of them read the table once and sort the groups.
Reference solution (MySQL)
SELECT DATE(login_at) AS login_day, COUNT(DISTINCT player_id) AS active_players
FROM LoginEvent
GROUP BY DATE(login_at)
ORDER BY login_dayAnother way
SELECT LEFT(login_at, 10) AS login_day, COUNT(DISTINCT player_id) AS active_players FROM LoginEvent GROUP BY LEFT(login_at, 10) ORDER BY login_dayAnother way
SELECT login_day, COUNT(*) AS active_players FROM (SELECT DISTINCT DATE_FORMAT(login_at, '%Y-%m-%d') AS login_day, player_id FROM LoginEvent) d GROUP BY login_day ORDER BY login_day← Average Completed Match Length by Game Mode · Battle Pass Progress Labels for Season 12 →