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.
Quick Answer: 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.