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 - SQL Solution INPUT: Traffic Table user_id activity date 1 login 2019-05-01 1 login 2019-06-01 2 login 2019-06-01 3 login 2019-06-05 4 login 2019-04-01 5 logout 2019-06-02 Constraints: Today = 2019-06-30 Window = 90 days back 2019-04-01 --> 2019-06-30 ALGORITHM STEPS 1 Filter Login Activity WHERE activity = 'login' 2 Find First Login MIN(activity_date) per user 3 Apply 90-Day Filter DATEDIFF <= 90 days 4 Count Per Day GROUP BY first_login_date SELECT first_date, COUNT(*) AS user_cnt FROM ( SELECT user_id, MIN(activity_date) first_date FROM Traffic WHERE activity='login' ) GROUP BY first_date FINAL RESULT login_date cnt 2019-05-01 1 2019-06-01 1 2019-06-05 1 Excluded: User 4: 2019-04-01 (Beyond 90 days) OK - Solution Complete 3 unique dates found 3 new users total within 90-day window Key Insight: The subquery finds each user's FIRST login using MIN(activity_date) with GROUP BY user_id. The outer query then filters these first logins to be within 90 days and counts users per date. This ensures each user is counted only ONCE on their actual first login date, not on subsequent logins. TutorialsPoint - New Users Daily Count | Optimal Solution

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