Identify Unique Callers and French Customer Call Percentage
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
video_calls
+---------+-----------+--------------+---------------------+---------------+
| call_id | caller_id | recipient_id | start_ts | caller_country|
+---------+-----------+--------------+---------------------+---------------+
| 1 | 101 | 202 | 2023-05-01 10:00:00 | US |
| 2 | 101 | 303 | 2023-05-02 11:15:10 | US |
| 3 | 202 | 101 | 2023-05-02 12:00:00 | FR |
| 4 | 202 | 404 | 2023-05-03 09:55:00 | FR |
| 5 | 303 | 101 | 2023-05-04 14:00:00 | GB |
+---------+-----------+--------------+---------------------+---------------+
##### Scenario
Querying the video_calls table to answer recent usage questions.
##### Question
How many unique callers have called more than three distinct people in the last seven days? What percentage of customers from France made at least one video call yesterday?
##### Hints
Use COUNT DISTINCT, GROUP BY caller_id, HAVING clauses, date filters, and ratio calculations with CASE.
Overview: This question evaluates proficiency in data manipulation, specifically aggregation and deduplication of user activity, time-based filtering of recent events, and computation of proportions using SQL or Python.
Using the video_calls table, write a query that returns a single row with two metrics:
1) unique_callers_gt3_last_7d: the number of distinct callers who called more than three distinct recipients between 2025-05-25 00:00:00 (inclusive) and 2025-05-31 23:59:59 (i.e., start_ts between '2025-05-25' and '2025-05-31').
2) pct_fr_customers_called_yesterday: the percentage of distinct French callers (caller_country = 'FR') who made at least one video call on 2025-05-31 (i.e., start_ts between '2025-05-31' 00:00:00 and '2025-05-31' 23:59:59).
Return both metrics in a single-row result set.
Tables
video_calls(call_id INTEGER, caller_id INTEGER, recipient_id INTEGER, start_ts TIMESTAMP, caller_country VARCHAR(2))
Hints
- First, aggregate by caller_id over the given 7-day period and count distinct recipient_id values.
- Use a HAVING clause on the aggregated results to keep only callers with more than 3 distinct recipients, then count those callers.