Analyze User Engagement Metrics for Video-Calling App
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Calls
+--------+-----------+------------+---------+----------+
| 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 |
+--------+-----------+------------+---------+----------+
Users
+---------+-----------+---------+------------+----------+------------+
| user_id | age_bucket| country | primary_os | dau_flag | ds |
+---------+-----------+---------+------------+----------+------------+
| 123 | 25-34 | US | iOS | 1 | 2019-01-01 |
| 456 | 35-44 | France | Android | 1 | 2019-01-01 |
| 789 | 25-34 | US | iOS | 0 | 2019-01-01 |
+---------+-----------+---------+------------+----------+------------+
##### Scenario
You work for a video-calling app and need to report user engagement metrics for different geographies.
##### Question
What percentage of users whose country = 'France' were on at least one video call yesterday?
What is the total video-call duration divided by the number of daily active users (DAU) in the United States today?
##### Hints
Join the calls table with the user profile table; use DISTINCT user counts, date filters like DATE(ds)=CURRENT_DATE-1 or CURRENT_DATE, and aggregate durations in seconds/minutes before dividing.
Overview: This question evaluates competency in data manipulation and product-metric computation, focusing on combining user profile and call event data to produce user-level engagement measures like participation rates and average call duration.
Using the calls and users tables, compute two engagement metrics for specific dates:
1) For 2025-05-31, calculate the percentage of users whose country = 'France' that participated in at least one call on that date. Treat both caller and recipient as participants. Return this as a row with:
- metric_name = 'fr_on_call_pct_yesterday'
- metric_date = '2025-05-31'
- metric_value = percentage of French users on at least one call (rounded to 2 decimals).
2) For 2025-06-01, compute the total person-level call duration for users in the United States on that date divided by the number of US daily active users (dau_flag = 1) on that same date. Again, treat both caller and recipient as participants. Return this as a row with:
- metric_name = 'us_avg_call_duration_per_dau_today'
- metric_date = '2025-06-01'
- metric_value = total US person-level call duration / number of US DAUs (rounded to 2 decimals).
Your query should return exactly two rows (one for each metric) with columns: metric_name, metric_date, metric_value.
Tables
calls(caller VARCHAR, recipient VARCHAR, ds DATE, call_id VARCHAR, duration DECIMAL(10,1))
users(user_id VARCHAR, age_bucket VARCHAR, country VARCHAR, primary_os VARCHAR, dau_flag INTEGER, ds DATE)
Hints
- Treat both caller and recipient as participants by UNION ALL-ing them into a single call_participants table.
- Filter by explicit dates: use ds = DATE '2025-05-31' for the France percentage and ds = DATE '2025-06-01' for the US duration metric.