Video Watches by App and Country With a 30-Day Fraud-Session Country Ranking
Company: ByteDance
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
A company that runs several video apps wants a report for today that shows, for every app and every country other than the United States, how many video-watch events happened today. Each row must also show the country's average daily number of fraudulent sessions over the past 30 days, where a user's sessions on a day are fraudulent when their `risk_score` for that day is greater than 3, and must rank the countries by that average.
The report does not say which date is today, how the 30-day window is bounded, or which direction and tie rule the ranking uses. This version fixes today as 2025-06-30, uses the 30 dates from 2025-06-01 through 2025-06-30, ranks the highest average first, and gives tied countries the same rank, as specified below.
### 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, and each one counts.
| 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; `'video_watch'` marks a video-watch event, and other values such as `'like'` or `'share'` are other events |
**`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_id`, `country`) pair with at least one video-watch event on 2025-06-30 in a country other than `'US'`, with these columns in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `app_id` | integer | App |
| `country` | text | Country code from `daily_log` |
| `video_watch_events` | integer | Number of `daily_log` rows on 2025-06-30 with `event_type = 'video_watch'` for this app and country |
| `avg_daily_fraud_sessions` | numeric | The country's average daily fraudulent sessions from 2025-06-01 through 2025-06-30, rounded to two decimal places |
| `country_fraud_rank` | integer | The country's rank by `avg_daily_fraud_sessions`, 1 for the highest |
- A country's fraudulent sessions are the sum of `num_sessions` over the `user_risk` rows with that `country`, a `date` from 2025-06-01 through 2025-06-30, and `risk_score > 3`. The average divides that sum by 30, the number of days in the window, whether or not every day has rows. A country with no such rows has an average of 0.00, never NULL.
- Countries are ranked among the distinct countries that appear in the result, by their average in descending order. Countries with equal averages share a rank, and the next lower average gets the next integer (dense ranking: 1, 2, 2, 3). Every row of a country carries that country's rank.
Order the rows by `country_fraud_rank` ascending, then `country` ascending, then `app_id` ascending.
### Example
`daily_log`:
| date | user_id | app_id | country | event_type |
| --- | --- | --- | --- | --- |
| 2025-06-30 | 1 | 10 | BR | video_watch |
| 2025-06-30 | 2 | 10 | BR | video_watch |
| 2025-06-30 | 3 | 20 | BR | video_watch |
| 2025-06-30 | 4 | 10 | MX | video_watch |
| 2025-06-30 | 4 | 10 | MX | like |
| 2025-06-30 | 5 | 20 | IN | video_watch |
| 2025-06-30 | 6 | 10 | US | video_watch |
| 2025-06-29 | 7 | 20 | MX | video_watch |
| 2025-06-30 | 8 | 20 | DE | video_watch |
`user_risk`:
| date | user_id | country | risk_score | num_sessions |
| --- | --- | --- | --- | --- |
| 2025-06-10 | 11 | BR | 5 | 12 |
| 2025-06-20 | 12 | BR | 4 | 6 |
| 2025-06-20 | 13 | BR | 3 | 50 |
| 2025-06-01 | 14 | MX | 9 | 18 |
| 2025-05-31 | 15 | MX | 9 | 100 |
| 2025-06-30 | 5 | IN | 7 | 20 |
| 2025-06-15 | 8 | DE | 2 | 10 |
| 2025-06-15 | 6 | US | 9 | 300 |
Expected result:
| app_id | country | video_watch_events | avg_daily_fraud_sessions | country_fraud_rank |
| --- | --- | --- | --- | --- |
| 20 | IN | 1 | 0.67 | 1 |
| 10 | BR | 2 | 0.60 | 2 |
| 20 | BR | 1 | 0.60 | 2 |
| 10 | MX | 1 | 0.60 | 2 |
| 20 | DE | 1 | 0.00 | 3 |
- Event counts: app 10 in BR has video-watch events from users 1 and 2. App 10 in MX counts only user 4's video-watch event, not the `like`. The MX event on 2025-06-29 is not from today, and the US row is excluded.
- BR: 12 + 6 = 18 fraudulent sessions in the window, so 18 / 30 = 0.60. User 13's 50 sessions have a risk score of exactly 3 and are not fraudulent.
- MX: 18 fraudulent sessions on 2025-06-01, the first day of the window, so 0.60. The 100 sessions on 2025-05-31 fall outside the window.
- IN: 20 / 30 = 0.666..., shown as 0.67. User 5's own video-watch event still counts in `video_watch_events`, because event counts ignore risk scores.
- DE: no fraudulent sessions, so 0.00.
- IN ranks first; BR and MX tie at 0.60 and share rank 2; DE gets rank 3. The US's 300 fraudulent sessions do not matter because no US row is in the result.
### Constraints and Clarifications
- No column contains NULL.
- `video_watch_events` counts every user's video-watch events, whatever their risk score; only the fraud average uses `risk_score`.
- The event counts use `daily_log.country`, and the fraud average uses `user_risk.country`; the two tables are matched only through the country code.
- A risk score of exactly 3 is not fraudulent.
- Every average is a whole number of sessions divided by 30, so rounding to two decimals never merges or reorders distinct averages; ranking by the exact or the rounded value gives the same ranks.
- Countries that have fraudulent sessions but no non-US video-watch events today do not appear and take no rank. If there are no non-US video-watch events on 2025-06-30, the result is empty.
```hint Different grains
The event counts live at the app-and-country level for one day, while the fraud average and its rank live at the country level over 30 days; build each at its own level before combining them.
```
Overview: SQL practice combining today's video-watch events per app and country with each country's average daily fraudulent sessions over the past 30 days, then ranking countries by that average. Tests aggregating at different grains, bounded date windows, zero-filling missing values and dense ranking with ties.
Read the full ByteDance Data Scientist interview experience this question came from