New Users Daily Count - Problem

Given a Traffic table that tracks user activities, write a SQL solution to report the number of users that logged in for the first time on each date within at most 90 days from today (2019-06-30).

The table contains columns:

  • user_id (int): User identifier
  • activity (enum): Activity type ('login', 'logout', 'jobs', 'groups', 'homepage')
  • activity_date (date): Date of the activity

Key Requirements:

  • Only consider dates within 90 days from 2019-06-30 (i.e., from 2019-04-01 to 2019-06-30)
  • Find users who logged in for the first time on each date
  • Count distinct users per date
  • The table may contain duplicate rows

Table Schema

Traffic
Column Name Type Description
user_id int User identifier
activity enum Activity type: 'login', 'logout', 'jobs', 'groups', 'homepage'
activity_date date Date when the activity occurred
Primary Key: None
Note: Table may contain duplicate rows. Need to find first login date for each user.

Input & Output

Example 1 — Multiple Users First Login
Input Table:
user_id activity activity_date
1 login 2019-05-01
1 homepage 2019-05-01
1 logout 2019-05-01
2 login 2019-06-21
2 logout 2019-06-21
3 login 2019-05-01
3 logout 2019-05-01
3 login 2019-06-21
4 login 2019-06-21
4 login 2019-05-01
Output:
login_date user_count
2019-05-01 3
2019-06-21 1
💡 Note:

User 1's first login: 2019-05-01, User 2's first login: 2019-06-21, User 3's first login: 2019-05-01 (not 2019-06-21), User 4's first login: 2019-05-01 (not 2019-06-21). So on 2019-05-01, we have 3 new users (1,3,4), and on 2019-06-21, we have 1 new user (2).
Example 2 — Outside Date Range
Input Table:
user_id activity activity_date
1 login 2019-03-01
1 login 2019-05-01
2 login 2019-06-30
Output:
login_date user_count
2019-06-30 1
💡 Note:

User 1's first login was 2019-03-01, which is outside the 90-day window (before 2019-04-01), so user 1 is not counted. User 2's first login 2019-06-30 is within range, so we count 1 new user on that date.
Example 3 — No Login Activities
Input Table:
user_id activity activity_date
1 homepage 2019-05-01
2 logout 2019-06-21
Output:
login_date user_count
💡 Note:

No login activities found in the data, so the result is empty. Only 'homepage' and 'logout' activities are present, which don't count as first-time logins.

Constraints

  • 1 ≤ user_id ≤ 100000
  • activity is one of ('login', 'logout', 'jobs', 'groups', 'homepage')
  • activity_date is between 2019-01-01 and 2019-06-30
  • Consider only dates within 90 days from 2019-06-30

Visualization

Tap to expand
New Users Daily Count: Problem OverviewInput: Traffic Tableuser_idactivityactivity_date1login2019-05-012login2019-06-213login2019-05-01Find FirstLogin Per UserProcess: First Login Detectionuser_idfirst_login_date12019-05-0122019-06-2132019-05-01COUNT BYDATEOutput: Daily Countlogin_dateuser_count2019-05-0122019-06-211Key Concept: Only count first-time logins within 90 days from 2019-06-30Date Range: 2019-04-01 to 2019-06-30 (90 days)Method: Find MIN(activity_date) per user WHERE activity = "login"Result: COUNT(DISTINCT user_id) GROUP BY first_login_date
Understanding the Visualization
1
Input
Traffic table with user activities
2
Filter
Find first login per user
3
Count
Group by date and count new users
Key Takeaway
🎯 Key Insight: Use window functions or aggregation to identify each user's first login, then count new users by date within the specified time window

Related Problems

Asked in
Facebook 28 Amazon 15 Google 12
23.4K Views
Medium 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