Daily Active Players From the Login Log

Easy
178.

Daily Active Players From the Login Log

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

ColumnTypeValues
event_idint
player_idint
login_atdatetime
platformenumandroid, ios, pc

Primary key: event_id.

One row per successful sign-in. Times are server time (IST).

Example

The rows must come back in the order shown.

LoginEvent

event_idplayer_idlogin_atplatform
172025-04-14 09:12:00android
272025-04-14 21:40:10android
392025-04-14 23:59:59pc
492025-04-15 00:00:05pc
532025-04-15 18:30:00ios
672025-04-15 19:02:44ios
732025-04-17 07:45:00android

Output

login_dayactive_players
2025-04-142
2025-04-153
2025-04-171
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
event_idint
player_idint
login_atdatetime
platformenum(android, ios, pc)

Primary key: event_id.

One row per successful sign-in. Times are server time (IST).

Examples

Example 1

LoginEvent

event_idplayer_idlogin_atplatform
172025-04-14 09:12:00android
272025-04-14 21:40:10android
392025-04-14 23:59:59pc
492025-04-15 00:00:05pc
532025-04-15 18:30:00ios
672025-04-15 19:02:44ios
732025-04-17 07:45:00android

Output

login_dayactive_players
2025-04-142
2025-04-153
2025-04-171

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_day

Another 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_day

Another 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 →