Coding questions · Meta
Analyze Top Call Initiators and Active French Video Callers
This question evaluates proficiency in SQL-based data manipulation and analytics—specifically aggregation, grouping, time-window filtering, and percentage calculations—by asking for the top call initiators over the last seven days and the proportion of active users in France on video calls yesterday, and is categorized under Data Manipulation (SQL/Python) for a Data Scientist role with a focus on practical application. It is commonly asked in technical interviews to assess a data scientist's ability to extract actionable engagement metrics from event logs, reason about temporal windows and population denominators, and produce accurate, explainable reporting from production-style datasets.
calls
+---------+-----------+-------------+---------------------+---------+-----------+
| call_id | caller_id | receiver_id | call_start_time | country | call_type |
+---------+-----------+-------------+---------------------+---------+-----------+
| 1 | 101 | 202 | 2023-10-10 09:05:00 | France | video |
| 2 | 103 | 204 | 2023-10-10 10:15:00 | India | voice |
| 3 | 101 | 105 | 2023-10-09 22:30:00 | France | video |
| 4 | 106 | 107 | 2023-10-03 05:45:00 | Brazil | video |
| 5 | 108 | 109 | 2023-10-10 13:20:00 | France | voice |
+---------+-----------+-------------+---------------------+---------+-----------+
Scenario
A social media platform wants SQL queries to analyze its call feature usage.
Question
Write a SQL query to return the top 10 users who initiated the highest number of calls in the last 7 days. Write a SQL query to calculate the percentage of active users in France who were on a video call yesterday.
Hints
Filter by date, group by caller, count calls, use sub-query or CTE for total active users and video callers, and calculate the percentage.
Top 10 Call Initiators (2025-05-26 to 2025-06-01)
Return the top 10 users (by caller_id) who initiated the highest number of calls between 2025-05-26 and 2025-06-01 (both dates inclusive).
Table calls
| column | type | constraints |
|---|---|---|
| call_id | INTEGER | PRIMARY KEY |
| caller_id | INTEGER | NOT NULL |
| receiver_id | INTEGER | NOT NULL |
| call_start_time | TIMESTAMP | NOT NULL |
| country | VARCHAR(100) | NOT NULL |
| call_type | VARCHAR(20) | NOT NULL |
Sample data:
| call_id | call_start_time | call_type | caller_id | country | receiver_id |
|---|---|---|---|---|---|
| 1 | 2025-06-01 09:05:00 | video | 101 | France | 202 |
| 2 | 2025-06-01 10:15:00 | voice | 103 | India | 204 |
| 3 | 2025-05-31 22:30:00 | video | 101 | France | 105 |
| 4 | 2025-05-25 05:45:00 | video | 106 | Brazil | 107 |
| 5 | 2025-06-01 13:20:00 | voice | 108 | France | 109 |
Expected output
Up to 10 rows listing caller_id and the number of calls they initiated where call_start_time is between 2025-05-26 and 2025-06-01 inclusive (i.e., call_start_time >= '2025-05-26' and < '2025-06-02'). Based on the sample data, the call on 2025-05-25 is excluded.
| call_count | caller_id |
|---|---|
| 2 | 101 |
| 1 | 103 |
| 1 | 108 |
Hints
- Filter calls using a fixed date window: call_start_time >= DATE '2025-05-26' AND call_start_time < DATE '2025-06-02'.
- GROUP BY caller_id, ORDER BY call_count DESC, and LIMIT 10.
France Video Active Users Percentage on 2025-05-31
Calculate the percentage of active users in France who were on a video call on 2025-05-31. Return a single row with columns: date, active_users, video_users, and percentage_video.
Table calls
| column | type | constraints |
|---|---|---|
| call_id | INTEGER | PRIMARY KEY |
| caller_id | INTEGER | NOT NULL |
| receiver_id | INTEGER | NOT NULL |
| call_start_time | TIMESTAMP | NOT NULL |
| country | VARCHAR(100) | NOT NULL |
| call_type | VARCHAR(20) | NOT NULL |
Sample data:
| call_id | call_start_time | call_type | caller_id | country | receiver_id |
|---|---|---|---|---|---|
| 1 | 2025-06-01 09:05:00 | video | 101 | France | 202 |
| 2 | 2025-06-01 10:15:00 | voice | 103 | India | 204 |
| 3 | 2025-05-31 22:30:00 | video | 101 | France | 105 |
| 4 | 2025-05-25 05:45:00 | video | 106 | Brazil | 107 |
| 5 | 2025-06-01 13:20:00 | voice | 108 | France | 109 |
Expected output
One row for the date 2025-05-31 with the count of distinct active users in France (distinct users from caller_id and receiver_id on that date), the count of those users who participated in at least one video call, and the percentage (video_users / active_users * 100). Based on the sample, only call_id = 3 matches (France, 2025-05-31, video), so users 101 and 105 are active and both are video users.
| active_users | date | percentage_video | video_users |
|---|---|---|---|
| 2 | 2025-05-31 | 100 | 2 |
Hints
- Include both caller_id and receiver_id by UNION ALL to define the set of active users on 2025-05-31.
- Filter by country = 'France' and the fixed window call_start_time >= DATE '2025-05-31' AND call_start_time < DATE '2025-06-01'.
- COUNT DISTINCT user_id for active_users and for video_users; then compute video_users * 100.0 / active_users with rounding.