Coding questions · Meta
Identify Unique Callers and French Customer Call Percentage
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.
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.
Unique callers and French customer engagement metrics
Using the video_calls table, write a query that returns a single row with two metrics:
-
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').
-
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.
Table video_calls
| column | type | constraints |
|---|---|---|
| call_id | INTEGER | PRIMARY KEY, NOT NULL |
| caller_id | INTEGER | NOT NULL |
| recipient_id | INTEGER | NOT NULL |
| start_ts | TIMESTAMP | NOT NULL |
| caller_country | VARCHAR(2) | NOT NULL |
Sample data:
| call_id | caller_country | caller_id | recipient_id | start_ts |
|---|---|---|---|---|
| 1 | US | 101 | 201 | 2025-05-25 10:00:00 |
| 2 | US | 101 | 202 | 2025-05-26 11:15:10 |
| 3 | US | 101 | 203 | 2025-05-27 12:00:00 |
| 4 | US | 101 | 204 | 2025-05-28 09:55:00 |
| 5 | FR | 202 | 101 | 2025-05-29 14:00:00 |
| 6 | FR | 202 | 303 | 2025-05-31 16:30:00 |
| 7 | FR | 303 | 101 | 2025-05-30 08:45:00 |
| 8 | FR | 303 | 101 | 2025-06-01 09:00:00 |
Expected output
A single-row result with two columns: (1) unique_callers_gt3_last_7d – the count of callers who contacted more than three distinct recipients 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 call on 2025-05-31. For the sample data, caller 101 has four distinct recipients in that 7-day window, so unique_callers_gt3_last_7d = 1. There are two distinct French callers overall (202 and 303), and only caller 202 called on 2025-05-31, so the percentage is 50.00.
| pct_fr_customers_called_yesterday | unique_callers_gt3_last_7d |
|---|---|
| 50 | 1 |
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.
- To compute the French customer percentage, count distinct French callers overall and distinct French callers on 2025-05-31, then return (yesterday / total) * 100 with safe division (avoid division by zero).