Calculate Video Call Usage Metrics by Country and Date
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
video_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 | FR | Android | 1 | 2019-01-01 |
| 789 | 18-24 | US | Android | 0 | 2019-01-01 |
+---------+------------+---------+-------------+----------+------------+
##### Scenario
Video-calling platform wants usage KPIs by country and date.
##### Question
What percentage of users located in France made at least one video call yesterday? What is the average time spent on calls per daily active user (DAU) in the United States today?
##### Hints
Join call and user tables, filter by ds for yesterday/today, count distinct users, sum durations, divide for ratios or averages.
Overview: This question evaluates data manipulation and product-analytics skills, focusing on joining user and call datasets, aggregations for distinct-user percentages, and per-DAU duration calculations.
A video-calling platform wants usage KPIs by country and date.
Using the ds column as the date partition, compute both of the following metrics:
1) For 2025-05-31, the percentage of users located in France who initiated at least one video call on that date.
2) For 2025-06-01, the average total time spent on video calls per daily active user (DAU) in the United States on that date. DAUs are users with dau_flag = 1, and call time should include calls they placed or received.
Return one row per metric with the columns: metric, ds, country, value.
- For the France metric, use metric = 'pct_fr_users_made_call_yesterday' and country = 'FR'.
- For the US metric, use metric = 'avg_call_time_per_dau_us_today' and country = 'US'.
- Round value to 1 decimal place.
Tables
video_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
- Use users.ds snapshots for 2025-05-31 (France) and 2025-06-01 (US) when filtering.
- For the France metric, the denominator is distinct FR users on 2025-05-31; the numerator is distinct FR callers on that date.