Katkat
Back

Identify 3-Person Call Cycles in Video-Calling App

MetaData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.