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
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_name | varchar |
| occurred_at | datetime |
Primary key: event_id.
The product event log; every event is a user doing something.
Examples
Example 1
events
| event_id | user_id | event_name | occurred_at |
|---|---|---|---|
| 1 | 1 | login | 2024-06-03 09:00:00 |
| 2 | 1 | task_added | 2024-06-04 10:15:00 |
| 3 | 1 | comment_posted | 2024-06-04 18:40:00 |
| 4 | 1 | login | 2024-06-10 08:05:00 |
| 5 | 1 | report_viewed | 2024-06-21 13:30:00 |
| 6 | 2 | login | 2024-06-30 22:00:00 |
| 7 | 2 | login | 2024-07-01 09:00:00 |
| 8 | 2 | file_uploaded | 2024-07-02 11:20:00 |
| 9 | 2 | login | 2024-07-03 09:10:00 |
| 10 | 2 | task_added | 2024-07-05 17:45:00 |
| 11 | 3 | login | 2024-06-29 10:00:00 |
| 12 | 3 | login | 2024-06-30 10:00:00 |
| 13 | 3 | login | 2024-07-01 10:00:00 |
| 14 | 3 | login | 2024-07-02 10:00:00 |
Output
| activity_month | user_id | active_days |
|---|---|---|
| 2024-06 | 1 | 4 |
| 2024-07 | 2 | 4 |
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_idAnother 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_idAnother 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 →