Calculate French User Engagement and U.S. Call Duration
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
call_logs
+---------+-----------+------------+---------+----------+
| caller | recipient | ds | call_id | duration |
+---------+-----------+------------+---------+----------+
| 123 | 456 | 2019-01-01 | 4325 | 864.4 |
| 032 | 789 | 2019-01-01 | 9395 | 263.7 |
| 456 | 032 | 2019-01-01 | 0879 | 22.0 |
+---------+-----------+------------+---------+----------+
user_profile
+---------+-----------+---------+------------+----------+------------+
| user_id | age_bucket| country | primary_os | dau_flag | ds |
+---------+-----------+---------+------------+----------+------------+
| 123 | 25-34 | US | iOS | 1 | 2019-01-01 |
| 456 | 35-44 | FR | Android | 1 | 2019-01-01 |
| 789 | 18-24 | FR | iOS | 0 | 2019-01-01 |
+---------+-----------+---------+------------+----------+------------+
##### Scenario
A video-calling app wants to monitor user engagement. Analysts must compute
(
1) what proportion of French users placed at least one video call yesterday, and
(
2) today’s average total call duration per U.S. daily-active user.
##### Question
Write an SQL query to return the percentage of users whose country = 'France' that made or received at least one call yesterday.
Write an SQL query to return total call-time divided by count of U.S. DAU for today.
##### Hints
Join call_logs to user_profile twice (caller, recipient). Filter by ds with DATE_SUB/CURRENT_DATE. Use DISTINCT users to avoid double-counting, then aggregate.
Overview: This question evaluates proficiency in SQL-based data manipulation and analytics, testing skills such as joining event and profile tables, aggregating metrics, and computing user-level engagement proportions and average durations.
Using the tables call_logs and user_profile, compute both of the following metrics for 2019-01-01:
1) pct_fr_users_engaged: the percentage (0–100) of users with country = 'FR' who made or received at least one call on 2019-01-01. Use all users from user_profile where ds = '2019-01-01' and country = 'FR' as the denominator, and consider a user engaged if they appear as either caller or recipient in call_logs on that date.
2) avg_call_duration_per_us_dau: the average total call duration per U.S. daily-active user (DAU) on 2019-01-01. Consider users in user_profile where ds = '2019-01-01', country = 'US', and dau_flag = 1 as the U.S. DAU set. For each such user, sum the durations of all calls they participated in that day (as caller or recipient), treating users with zero calls as having total duration 0. Then take the average of these per-user totals.
Return a single row with two columns: pct_fr_users_engaged and avg_call_duration_per_us_dau.
Tables
call_logs(caller VARCHAR(20), recipient VARCHAR(20), ds DATE, call_id VARCHAR(20), duration DECIMAL(10,1))
user_profile(user_id VARCHAR(20), age_bucket VARCHAR(10), country VARCHAR(2), primary_os VARCHAR(20), dau_flag INTEGER, ds DATE)
Hints
- Start from user_profile snapshots on 2019-01-01 and identify the FR users and the U.S. DAUs separately.
- For French engagement, find which FR users appear in call_logs that day as either caller or recipient, and avoid double-counting users with multiple calls.