Compute High-Call Usage Rates
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
You are given two tables for a voice-calling product:
`users`
- `user_id` BIGINT
- `country_code` STRING
`calls`
- `call_id` BIGINT
- `caller_id` BIGINT
- `recipient_id` BIGINT
- `started_at` TIMESTAMP
`calls.caller_id` and `calls.recipient_id` both reference `users.user_id`.
Assume:
- Every row in `calls` is one valid call event.
- All timestamps are stored in UTC.
- Any date-based metric should be computed in the `Europe/London` timezone.
- `report_date` is a calendar date in `Europe/London`.
Write SQL to compute the following metrics for each `report_date`:
1. **Percentage of callers with at least 20 calls in the last 7 days**
- Use the 7-day window ending on `report_date`, inclusive.
- The numerator is the number of distinct users who initiated at least 20 calls in that 7-day window.
- The denominator is the number of distinct users who initiated at least 1 call in that same 7-day window.
2. **Percentage of Great Britain DAU with at least 50 total calls**
- Restrict to users with `country_code = 'GB'`.
- Define daily active users as distinct GB users who either placed or received at least one call on `report_date`.
- For each such user, count total call participations on `report_date`, where participations include both outgoing calls as `caller_id` and incoming calls as `recipient_id`.
- The numerator is the number of GB DAU with at least 50 total call participations on `report_date`.
- The denominator is all GB DAU on `report_date`.
Return one row per `report_date` with these columns:
- `report_date`
- `pct_callers_20plus_last_7d`
- `pct_gb_dau_50plus_calls`
Quick Answer: This question evaluates a candidate's ability to perform advanced data manipulation and analytical SQL tasks, including joins, aggregations, timezone-aware date handling, rolling window metrics, distinct user counting, and counting user participations across roles.