Power Users Active on Four or More Days a Month

Medium
411.

Power Users Active on Four or More Days a Month

MediumAggregationDates

MySQL

The product team calls a user a power user for a month when they were active on at least four different calendar days of that month. Several events on one day are one active day, and a streak across a month boundary counts separately in each month.

Return activity_month ('YYYY-MM'), user_id and active_days for every qualifying (month, user) pair, ordered by activity_month, then active_days descending, then user_id.

Tables

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

Primary key: event_id.

The product event log; every event is a user doing something.

Example

The rows must come back in the order shown.

events

event_iduser_idevent_nameoccurred_at
11login2024-06-03 09:00:00
21task_added2024-06-04 10:15:00
31comment_posted2024-06-04 18:40:00
41login2024-06-10 08:05:00
51report_viewed2024-06-21 13:30:00
62login2024-06-30 22:00:00
72login2024-07-01 09:00:00
82file_uploaded2024-07-02 11:20:00
92login2024-07-03 09:10:00
102task_added2024-07-05 17:45:00
4 more rows

Output

activity_monthuser_idactive_days
2024-0614
2024-0724
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.

Power Users Active on Four or More Days a Month

The product team calls a user a power user for a month when they were active on at least four different calendar days of that month.

  • Difficulty: Medium
  • Topics: Aggregation, Dates
  • Dialect: MySQL
  • Problem: #411

Problem statement

The product team calls a user a power user for a month when they were active on at least four different calendar days of that month. Several events on one day are one active day, and a streak across a month boundary counts separately in each month.

Return activity_month ('YYYY-MM'), user_id and active_days for every qualifying (month, user) pair, ordered by activity_month, then active_days descending, then user_id.

Tables

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

Primary key: event_id.

The product event log; every event is a user doing something.

Examples

Example 1

events

event_iduser_idevent_nameoccurred_at
11login2024-06-03 09:00:00
21task_added2024-06-04 10:15:00
31comment_posted2024-06-04 18:40:00
41login2024-06-10 08:05:00
51report_viewed2024-06-21 13:30:00
62login2024-06-30 22:00:00
72login2024-07-01 09:00:00
82file_uploaded2024-07-02 11:20:00
92login2024-07-03 09:10:00
102task_added2024-07-05 17:45:00
113login2024-06-29 10:00:00
123login2024-06-30 10:00:00
133login2024-07-01 10:00:00
143login2024-07-02 10:00:00

Output

activity_monthuser_idactive_days
2024-0614
2024-0724

How to solve Power Users Active on Four or More Days a Month

The unit is a (month, user) pair, so group by both: the month as DATE_FORMAT(occurred_at, '%Y-%m') and the user. Inside each group count distinct dates — COUNT(DISTINCT DATE(occurred_at)) — because an active day is a date, not an event; user 1's two events on 4 June are one day. Then HAVING … >= 4 keeps the power users.

Grouping by month splits a streak that crosses the boundary: user 3 was active 29 June – 2 July, four days in a row, but only two in each month, so they qualify in neither. That is the definition the statement asks for, and the one a monthly dashboard actually uses.

Deduplicating (user, date) pairs in a derived table first and then counting rows is the same computation in two steps, and is how you would write it on an engine without COUNT(DISTINCT expr). Counting distinct DAY() numbers inside a month is also safe, because the month is part of the group. One scan, one sort or hash.

Reference solution (MySQL)

SELECT DATE_FORMAT(occurred_at, '%Y-%m') AS activity_month,
       user_id,
       COUNT(DISTINCT DATE(occurred_at)) AS active_days
FROM events
GROUP BY DATE_FORMAT(occurred_at, '%Y-%m'), user_id
HAVING COUNT(DISTINCT DATE(occurred_at)) >= 4
ORDER BY activity_month, active_days DESC, user_id

Another way

SELECT LEFT(d, 7) AS activity_month, user_id, COUNT(*) AS active_days
FROM (SELECT DISTINCT user_id, LEFT(occurred_at, 10) AS d FROM events) days
GROUP BY LEFT(d, 7), user_id
HAVING COUNT(*) >= 4
ORDER BY activity_month, active_days DESC, user_id

Another way

SELECT activity_month, user_id, active_days FROM (
  SELECT YEAR(occurred_at) AS y, MONTH(occurred_at) AS m, DATE_FORMAT(occurred_at, '%Y-%m') AS activity_month, user_id, COUNT(DISTINCT DAY(occurred_at)) AS active_days
  FROM events GROUP BY YEAR(occurred_at), MONTH(occurred_at), DATE_FORMAT(occurred_at, '%Y-%m'), user_id
) t WHERE active_days >= 4
ORDER BY activity_month, active_days DESC, user_id

← Top Two Features in Every Pricing Plan · Signups and Upgrades by UTM Source →