Identify 3-Person Call Cycles in Video-Calling App
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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:
1. 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.
2. 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.
3. 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.
Tables
Calls(callerid INTEGER, recipientid INTEGER, ds DATE, call_id INTEGER, duration INTEGER)
Users(user_id INTEGER, age_bucket VARCHAR, country VARCHAR, primary_os VARCHAR, dau_flag INTEGER, ds DATE)
Hints
- Build call participants from both callerid and recipientid.
- Detect cycles by joining Calls to itself three times on the same day.