Coding questions · Meta
Identify 3-Person Call Cycles in Video-Calling App
This question evaluates SQL data-manipulation competency, including aggregation, distinct counting, date filtering and relational pattern detection (three-node cycles) to measure user engagement and group-call signals.
Calls
callerid | recipientid | ds | call_id | duration
1001 | 2001 | 2023-02-20| 555 | 180
2001 | 3001 | 2023-02-20| 556 | 60
3001 | 1001 | 2023-02-20| 557 | 240
1002 | 2002 | 2023-02-18| 558 | 300
1001 | 2003 | 2023-02-19| 559 | 120
Users
user_id | age_bucket | country | primary_os | dau_flag | ds
1001 | 25-34 | fr | ios | 1 | 2023-02-20
2001 | 25-34 | fr | android | 1 | 2023-02-20
3001 | 35-44 | us | ios | 0 | 2023-02-20
1002 | 18-24 | de | web | 1 | 2023-02-18
2002 | 25-34 | fr | ios | 0 | 2023-02-18
Scenario
A social-media video-calling app wants SQL insights from its Calls and Users tables.
Question
Write a SQL query that returns the number of distinct users who initiated calls involving more than three unique recipients during the past seven days. Calculate the percentage of yesterday’s daily active users whose country = 'fr' that joined at least one video call. Using only the Calls table, write SQL to detect 3-person call cycles (A→B, B→C, C→A) that suggest group-call demand.
Hints
Use GROUP BY/HAVING, DISTINCT counts, self-joins for cycles, date filters on ds.
Calling behavior, FR DAU participation, and 3-person call cycles
You are given two tables from a social-media video-calling app: Calls and Users. Return one combined result set with columns result_set, num_callers_gt_3_unique_recipients, yesterday, fr_dau_joined_pct, ds, a, b, and c:
- result_set = 'callers_gt_3_unique_recipients': count distinct users who initiated calls to more than three unique recipients in the inclusive 2025-05-26 through 2025-06-01 window.
- result_set = 'fr_dau_joined_pct': for 2025-05-31, compute the percentage of Users rows with country = 'fr' and dau_flag = 1 who participated in at least one call that day as caller or recipient.
- result_set = 'three_person_cycles': detect same-day 3-person cycles A->B, B->C, C->A and return one normalized ordered triple per distinct cycle.
Fields that do not apply to a row should be NULL.
Table Calls
| column | type | constraints |
|---|---|---|
| callerid | INTEGER | NOT NULL |
| recipientid | INTEGER | NOT NULL |
| ds | DATE | NOT NULL |
| call_id | INTEGER | PRIMARY KEY, NOT NULL |
| duration | INTEGER | NOT NULL |
Sample data:
| call_id | callerid | ds | duration | recipientid |
|---|---|---|---|---|
| 555 | 1001 | 2025-05-31 | 180 | 2001 |
| 556 | 2001 | 2025-05-31 | 60 | 3001 |
| 557 | 3001 | 2025-05-31 | 240 | 1001 |
| 558 | 1002 | 2025-05-29 | 300 | 2002 |
| 559 | 1001 | 2025-05-30 | 120 | 2003 |
Table Users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | NOT NULL |
| age_bucket | VARCHAR | NOT NULL |
| country | VARCHAR | NOT NULL |
| primary_os | VARCHAR | 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 | fr | 1 | 2025-05-31 | ios | 1001 |
| 25-34 | fr | 1 | 2025-05-31 | android | 2001 |
| 35-44 | us | 0 | 2025-05-31 | ios | 3001 |
| 18-24 | de | 1 | 2025-05-29 | web | 1002 |
| 25-34 | fr | 0 | 2025-05-29 | ios | 2002 |
Expected output
One combined result set for the caller threshold count, FR DAU call participation percentage, and normalized 3-person call cycles.
| a | b | c | ds | fr_dau_joined_pct | num_callers_gt_3_unique_recipients | result_set | yesterday |
|---|---|---|---|---|---|---|---|
| NULL | NULL | NULL | NULL | NULL | 0 | callers_gt_3_unique_recipients | NULL |
| NULL | NULL | NULL | NULL | 100 | NULL | fr_dau_joined_pct | 2025-05-31 |
| 1001 | 2001 | 3001 | 2025-05-31 | NULL | NULL | three_person_cycles | NULL |
Hints
- Build call participants from both callerid and recipientid.
- Detect cycles by joining Calls to itself three times on the same day.
- Normalize cycle triples so rotations of the same three users collapse to one row.