Daily First-Time Login User Count

MEDIUM20 pts

You are given a user event table named Traffic.

Each row has:

user_id: unique user identifier
activity: one of ('login', 'logout', 'jobs', 'groups', 'homepage')
activity_date: event date in YYYY-MM-DD format

The table can contain duplicate rows.

Business definition:

A user's first login date is the earliest activity_date among rows where activity = 'login'.

Reference date is fixed to 2019-06-30.

Task:

For each user, compute the first login date.
Keep users whose first login date falls inside the inclusive 90-day lookback window from the reference date: [2019-04-01, 2019-06-30]
Count how many users have each qualifying first login date.

Return only dates with non-zero counts.

Output requirements:

Return exactly two columns in this order:
- login_date
- user_count
Sort by login_date ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Traffic:
| 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-01-01    |
| 3       | jobs     | 2019-01-01    |
| 3       | logout   | 2019-01-01    |
| 4       | login    | 2019-06-21    |
| 4       | groups   | 2019-06-21    |
| 4       | logout   | 2019-06-21    |
| 5       | login    | 2019-03-01    |
| 5       | logout   | 2019-03-01    |
| 5       | login    | 2019-06-21    |
| 5       | logout   | 2019-06-21    |

Output

[
  {"login_date":"2019-05-01","user_count":1},
  {"login_date":"2019-06-21","user_count":2}
]

Explanation

Users 1, 2, and 4 have first logins inside the lookback window. User 5 is excluded because their first login is 2019-03-01, outside the window.

Example

Input

Traffic:
| user_id | activity | activity_date |
|---------|----------|---------------|
| 10      | login    | 2019-04-01    |
| 10      | logout   | 2019-04-01    |
| 11      | login    | 2019-03-31    |
| 11      | login    | 2019-06-10    |
| 12      | homepage | 2019-06-30    |
| 12      | login    | 2019-06-30    |
| 13      | login    | 2019-07-01    |

Output

[
  {"login_date":"2019-04-01","user_count":1},
  {"login_date":"2019-06-30","user_count":1}
]

Explanation

The inclusive window is 2019-04-01 to 2019-06-30. User 11 and user 13 are outside that window based on first login.

Example

Input

Traffic:
| user_id | activity | activity_date |
|---------|----------|---------------|
| 20      | login    | 2019-01-05    |
| 20      | logout   | 2019-01-05    |
| 21      | homepage | 2019-06-20    |
| 22      | login    | 2019-03-31    |
| 22      | jobs     | 2019-03-31    |

Output

[]

Explanation

No user has a first login date in the target 90-day window, so the result is empty.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Traffic <= 10,000
1 <= user_id <= 1,000,000,000
activity is one of: login, logout, jobs, groups, homepage
activity_date is a valid date in YYYY-MM-DD format
Duplicate rows may exist
Reference date is fixed to 2019-06-30
Lookback window is inclusive: [2019-04-01, 2019-06-30]
Only activity = 'login' rows participate in first-login computation
Return columns exactly as login_date, user_count
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: login_date, user_count

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

traffic =

[{"user_id":1,"activity":"login","activity_date":"2019-05-01"},{"user_id":1,"activity":"homepage","activity_date":"2019-05-01"},{"user_id":1,"activity":"logout","activity_date":"2019-05-01"},{"user_id":2,"activity":"login","activity_date":"2019-06-21"},{"user_id":2,"activity":"logout","activity_date":"2019-06-21"},{"user_id":3,"activity":"login","activity_date":"2019-01-01"},{"user_id":3,"activity":"jobs","activity_date":"2019-01-01"},{"user_id":3,"activity":"logout","activity_date":"2019-01-01"},{"user_id":4,"activity":"login","activity_date":"2019-06-21"},{"user_id":4,"activity":"groups","activity_date":"2019-06-21"},{"user_id":4,"activity":"logout","activity_date":"2019-06-21"},{"user_id":5,"activity":"login","activity_date":"2019-03-01"},{"user_id":5,"activity":"logout","activity_date":"2019-03-01"},{"user_id":5,"activity":"login","activity_date":"2019-06-21"},{"user_id":5,"activity":"logout","activity_date":"2019-06-21"}]

Daily First-Time Login User Count

Hints

Daily First-Time Login User Count

Hints