Analyze Recent Calling Behavior in France Using SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
CALLS
+---------+---------+---------------------+-------------------+----------+
| call_id | user_id | call_start_time | participant_cnt | is_video |
+---------+---------+---------------------+-------------------+----------+
| 201 | 1 | 2023-09-14 10:00:00 | 4 | 1 |
| 202 | 2 | 2023-09-14 11:30:00 | 2 | 1 |
| 203 | 3 | 2023-09-13 09:45:00 | 5 | 0 |
| 204 | 1 | 2023-09-12 20:10:00 | 6 | 1 |
| 205 | 4 | 2023-09-12 08:00:00 | 3 | 1 |
USERS
+---------+--------------+
| user_id | country_code |
+---------+--------------+
| 1 | fr |
| 2 | fr |
| 3 | us |
| 4 | fr |
| 5 | fr |
DAILY_ACTIVITY
+--------------+---------+
| activity_date| user_id |
+--------------+---------+
| 2023-09-14 | 1 |
| 2023-09-14 | 2 |
| 2023-09-14 | 3 |
| 2023-09-14 | 4 |
| 2023-09-14 | 5 |
##### Scenario
WhatsApp analytics team wants to understand recent calling behavior and engagement in France.
##### Question
Write SQL to return the number of unique users who initiated a call with more than 3 participants in the last 7 days.
Write SQL to calculate the percentage of yesterday's daily active users whose country_code = 'fr' that were on a video call.
Explain whether COUNT(DISTINCT user_id) is still needed after grouping by user_id.
Compare UNION versus UNION ALL and when to use each.
##### Hints
Think about event-level tables, country filtering, GROUP BY-then-COUNT logic, and deduplication/performance trade-offs for UNION.
Overview: This question evaluates SQL data-manipulation competencies such as time-window filtering, joins between event and user tables, aggregation and deduplication logic, set operations (UNION vs UNION ALL), and calculation of user-level percentages from event-level data.
French Callers With More Than 3 Participants
Count the number of distinct French users (`USERS.country_code = 'fr'`) who initiated at least one call with more than 3 participants from 2025-05-25 through 2025-05-31 inclusive. Return a single column named `count_users`.
Tables
USERS(user_id INTEGER, country_code VARCHAR(2))
CALLS(call_id INTEGER, user_id INTEGER, call_start_time TIMESTAMP, participant_cnt INTEGER, is_video INTEGER)
DAILY_ACTIVITY(activity_date DATE, user_id INTEGER)
Hints
- Join `CALLS` to `USERS` on `user_id`.
- Use `participant_cnt > 3`.
French DAU Video Rate on 2025-05-31
Using `DAILY_ACTIVITY`, treat 2025-05-31 as the activity date of interest. Among French daily active users (`country_code = 'fr'`) on that date, calculate the percentage who had at least one video call (`is_video = 1`) on 2025-05-31. Return one column named `video_rate_pct`, rounded to 2 decimal places.
Tables
USERS(user_id INTEGER, country_code VARCHAR(2))
CALLS(call_id INTEGER, user_id INTEGER, call_start_time TIMESTAMP, participant_cnt INTEGER, is_video INTEGER)
DAILY_ACTIVITY(activity_date DATE, user_id INTEGER)
Hints
- Build French DAU from `DAILY_ACTIVITY` joined to `USERS`.
- Use DISTINCT users from same-day video calls for the numerator.
COUNT(DISTINCT) vs grouped count on French DAU
Using the French daily active users on 2025-05-31 (the latest activity_date in DAILY_ACTIVITY), demonstrate that COUNT(DISTINCT user_id) is redundant after grouping by user_id by returning both counts and showing they are equal.
Tables
USERS(user_id INTEGER, country_code VARCHAR(2))
DAILY_ACTIVITY(activity_date DATE, user_id INTEGER)
Hints
- First build the set of French DAU for 2025-05-31, then group that result by user_id and count the rows.
- Compare that grouped-row count to COUNT(DISTINCT user_id) on the same fr_dau set.
UNION vs UNION ALL on French DAU
Using the list of French daily active users on 2025-05-31 (the latest activity_date in DAILY_ACTIVITY), combine that list with itself and report how many rows are returned using UNION versus UNION ALL.
Tables
USERS(user_id INTEGER, country_code VARCHAR(2))
DAILY_ACTIVITY(activity_date DATE, user_id INTEGER)
Hints
- Build the French DAU list for 2025-05-31 from DAILY_ACTIVITY joined to USERS.
- Use UNION to de-duplicate and UNION ALL to keep duplicates, then compare the row counts.