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