User Activity for the Past 30 Days I - Problem

Given the Activity table, write a solution to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively.

A user was active on someday if they made at least one activity on that day.

Activity Types: Any activity from ('open_session', 'end_session', 'scroll_down', 'send_message') will be considered valid activity for a user to be considered active on a day.

Return the result table in any order.

Table Schema

Activity
Column Name Type Description
user_id int User identifier
session_id int Session identifier
activity_date date Date when activity occurred
activity_type enum Type of activity: 'open_session', 'end_session', 'scroll_down', 'send_message'
Primary Key: None
Note: Table may have duplicate rows. Each session belongs to exactly one user.

Input & Output

Example 1 — Basic Activity Count
Input Table:
user_id session_id activity_date activity_type
1 1 2019-07-20 open_session
1 1 2019-07-20 scroll_down
1 1 2019-07-20 end_session
2 4 2019-07-20 open_session
2 4 2019-07-21 send_message
3 2 2019-07-21 open_session
3 2 2019-07-21 send_message
4 3 2019-06-25 open_session
4 3 2019-06-25 end_session
Output:
day active_users
2019-07-20 2
2019-07-21 2
💡 Note:

For 2019-07-20: Users 1 and 2 were active (user 1 had multiple activities but counts as 1 unique user). For 2019-07-21: Users 2 and 3 were active. The activity on 2019-06-25 is outside the 30-day range so it's excluded.
Example 2 — Edge Case with No Activities
Input Table:
user_id session_id activity_date activity_type
1 1 2019-06-01 open_session
2 2 2019-08-01 scroll_down
Output:
day active_users
💡 Note:

No activities fall within the 30-day period ending 2019-07-27 (range: 2019-06-28 to 2019-07-27), so the result is empty.

Constraints

  • 1 ≤ user_id ≤ 10^9
  • 1 ≤ session_id ≤ 10^9
  • activity_date is in valid date format
  • activity_type is one of ('open_session', 'end_session', 'scroll_down', 'send_message')

Visualization

Tap to expand
User Activity for the Past 30 Days I INPUT user_id session_id date type 1 1 2019-07-20 open 1 1 2019-07-20 scroll 1 1 2019-07-21 send 2 4 2019-07-21 open 3 2 2019-07-21 end ... more rows ... Date Range Filter 2019-06-28 to 2019-07-27 (30 days inclusive) Valid Activity Types: open_session end_session scroll_down send_message ALGORITHM STEPS 1 Filter by Date Range WHERE date BETWEEN '2019-06-28' AND '2019-07-27' 2 Group by Date GROUP BY activity_date Aggregate per day 3 Count Distinct Users COUNT(DISTINCT user_id) Unique users per day 4 Return Results SELECT day, active_users SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users FROM Activity WHERE ... GROUP BY ... FINAL RESULT day active_users 2019-07-20 2 2019-07-21 3 2019-07-22 1 2019-07-25 4 2019-07-27 2 ... more days ... Daily Active Users Days in Range OK - Complete Key Insight: The key is using COUNT(DISTINCT user_id) to count each user only ONCE per day, regardless of how many activities they performed. Combined with date filtering using DATEDIFF or BETWEEN to get the exact 30-day window ending on 2019-07-27, this gives us the daily active user (DAU) count efficiently. TutorialsPoint - User Activity for the Past 30 Days I | Optimal Solution

Related Problems

Asked in
Facebook 28 Amazon 15 Google 12
23.4K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen