Daily Active Users per App Excluding US Activity and Fraudulent Users
Company: ByteDance
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
A company that runs several video apps wants today's daily active users (DAU) for each of its apps, excluding activity in the United States and counting only users who are not considered fraudulent today. A user is non-fraudulent on a date when their `risk_score` for that date is at most 3.
The report asks for today's numbers. So that the result does not depend on when the query runs, this version fixes today as 2025-06-30, and it defines which users count in the Output Contract.
### Input Table(s)
**`daily_log`**: one row per app event. The table has no primary key; rows that are identical in every column are separate events.
| Column | Type | Meaning |
| --- | --- | --- |
| `date` | DATE | Calendar date of the event |
| `user_id` | INTEGER | User who generated the event |
| `app_id` | INTEGER | App in which the event happened |
| `country` | TEXT | Two-letter uppercase code of the country where the event happened; `'US'` is the United States |
| `event_type` | TEXT | Kind of event, such as `'video_watch'`, `'like'`, `'share'` or `'login'` |
**`user_risk`**: one row per user per date; primary key (`date`, `user_id`).
| Column | Type | Meaning |
| --- | --- | --- |
| `date` | DATE | Date the row describes |
| `user_id` | INTEGER | User |
| `country` | TEXT | Two-letter uppercase code of the user's country on that date |
| `risk_score` | INTEGER | The user's fraud risk score for that date; a score above 3 means the user is treated as fraudulent that day |
| `num_sessions` | INTEGER | Number of sessions the user had on that date (0 or more) |
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per app, with these columns in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `app_id` | integer | App |
| `dau` | integer | Number of distinct users counted for the app on 2025-06-30 |
A user counts toward an app's `dau` when both of these hold:
1. `daily_log` has at least one row with `date = '2025-06-30'`, that `user_id`, that `app_id` and `country <> 'US'`. Any `event_type` counts as activity.
2. `user_risk` has a row with `date = '2025-06-30'` and that `user_id` whose `risk_score` is at most 3.
Each user counts at most once per app but can count toward several apps. Apps with no counted user are omitted. Order the rows by `app_id` ascending.
### Example
`daily_log`:
| date | user_id | app_id | country | event_type |
| --- | --- | --- | --- | --- |
| 2025-06-30 | 1 | 10 | GB | video_watch |
| 2025-06-30 | 1 | 10 | GB | like |
| 2025-06-30 | 1 | 20 | GB | share |
| 2025-06-30 | 2 | 10 | US | video_watch |
| 2025-06-30 | 3 | 10 | FR | video_watch |
| 2025-06-30 | 4 | 20 | DE | login |
| 2025-06-30 | 5 | 20 | BR | video_watch |
| 2025-06-29 | 6 | 10 | GB | video_watch |
| 2025-06-30 | 7 | 10 | US | like |
| 2025-06-30 | 7 | 10 | CA | video_watch |
| 2025-06-30 | 8 | 30 | US | video_watch |
| 2025-06-30 | 9 | 20 | IN | video_watch |
`user_risk`:
| date | user_id | country | risk_score | num_sessions |
| --- | --- | --- | --- | --- |
| 2025-06-30 | 1 | GB | 2 | 4 |
| 2025-06-30 | 2 | US | 0 | 3 |
| 2025-06-30 | 3 | FR | 5 | 9 |
| 2025-06-30 | 4 | DE | 3 | 1 |
| 2025-06-29 | 5 | BR | 1 | 2 |
| 2025-06-29 | 6 | GB | 0 | 2 |
| 2025-06-30 | 6 | GB | 0 | 1 |
| 2025-06-30 | 7 | US | 1 | 2 |
| 2025-06-30 | 8 | US | 0 | 1 |
| 2025-06-30 | 9 | IN | 0 | 5 |
Expected result:
| app_id | dau |
| --- | --- |
| 10 | 2 |
| 20 | 3 |
- App 10: user 1 has two events but counts once. User 7 counts through the CA event, even though their other event is in the US and their `user_risk` row says `'US'`. User 2's only event is in the US, user 3's risk score of 5 marks them as fraudulent, and user 6's event is from 2025-06-29.
- App 20: users 1, 4 and 9 count; user 4 has a risk score of exactly 3, which is non-fraudulent. User 5 has no `user_risk` row for 2025-06-30, so they are not counted.
- App 30: its only event today is in the US, so the app is omitted.
### Constraints and Clarifications
- No column contains NULL.
- The US exclusion applies to the events' `daily_log.country`; `user_risk.country` plays no role in this query.
- A user who is active today but has no `user_risk` row for 2025-06-30 is not counted, because they cannot be confirmed as non-fraudulent.
- Rows for dates other than 2025-06-30 in either table are ignored.
- If no app has a counted user, the result is empty.
```hint Count people, not events
A user with several events in one app today is still one active user of that app.
```
Overview: SQL practice computing daily active users per app from an event log and a daily user risk table, excluding United States activity and users whose risk score marks them as fraudulent that day. Tests distinct counting, joins on user and date, and careful handling of users with no risk row.
Read the full ByteDance Data Scientist interview experience this question came from