OneNoughtOne
0/308
Loading content...
You are given a user event table named Traffic.
Each row has:
The table can contain duplicate rows.
Business definition:
Reference date is fixed to 2019-06-30.
Task:
Return only dates with non-zero counts.
Output requirements:
Supported submission environments:
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 |[
{"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.
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 |[
{"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.
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 |[]Explanation
No user has a first login date in the target 90-day window, so the result is empty.
Constraints