Coding questions · Meta
Identify Users Interested in Group Video Calls
This question evaluates a data scientist's ability to perform SQL-based event-level data manipulation, aggregation, joins, and user-level feature derivation from call logs and user attribute tables.
video_calls
caller | recipient | ds | call_id | duration
u1 | u2 | 2023-09-01| c100 | 320
u3 | u4 | 2023-09-01| c101 | 0
u2 | u1 | 2023-09-02| c102 | 180
dim_all_users
user_id | age_bucket | country | primary_os | dau_flag | ds
u1 | 25-34 | US | iOS | 1 | 2023-09-01
u2 | 35-44 | IN | Android | 1 | 2023-09-01
u3 | 18-24 | US | Android | 0 | 2023-09-01
Scenario
Using call-level logs and user attributes, estimate user interest in group video calls.
Question
Write SQL to identify users who appear interested in group video calls using only the tables below.
Hints
Derive number of unique recipients per caller per day and filter callers exceeding a threshold.
Identify Users Interested in Group Video Calls
Using the call-level logs and user attributes, write SQL to identify users who appear interested in group video calls. Define an interested user as a caller who, on a given day (ds), contacted at least 2 unique recipients. Return one row per (user, ds) with the user's attributes and the number of unique recipients.
Table video_calls
| column | type | constraints |
|---|---|---|
| caller | VARCHAR(50) | NOT NULL |
| recipient | VARCHAR(50) | NOT NULL |
| ds | DATE | NOT NULL |
| call_id | VARCHAR(50) | PRIMARY KEY, NOT NULL |
| duration | INTEGER | NOT NULL |
Sample data:
| call_id | caller | ds | duration | recipient |
|---|---|---|---|---|
| c100 | u1 | 2023-09-01 | 320 | u2 |
| c101 | u3 | 2023-09-01 | 0 | u4 |
| c102 | u2 | 2023-09-02 | 180 | u1 |
Table dim_all_users
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(50) | NOT NULL |
| age_bucket | VARCHAR(10) | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| primary_os | VARCHAR(20) | NOT NULL |
| dau_flag | INTEGER | NOT NULL |
| ds | DATE | NOT NULL |
Sample data:
| age_bucket | country | dau_flag | ds | primary_os | user_id |
|---|---|---|---|---|---|
| 25-34 | US | 1 | 2023-09-01 | iOS | u1 |
| 35-44 | IN | 1 | 2023-09-01 | Android | u2 |
| 18-24 | US | 0 | 2023-09-01 | Android | u3 |
Expected output
Return the list of callers (with user attributes) who, on a given day (ds), contacted at least 2 unique recipients, indicating interest in group video calls. For the provided sample data, no caller contacted 2 or more unique recipients on the same day, so the result is empty.
Hints
- First aggregate the call logs by caller and ds to count distinct recipients.
- Use COUNT(DISTINCT recipient) grouped by caller and ds; filter where this count is at least 2.
- Join the filtered caller-day pairs to dim_all_users on user_id and ds to enrich with user attributes.