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`
Overview: 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.
Compute, for each report_date, (1) the percentage of callers with at least 20 outgoing calls in the last 7 days (inclusive), and (2) the percentage of Great Britain daily active users (by country_code = 'GB') with at least 50 total call participations on that report_date, where participations include both outgoing and incoming calls. All date logic should use Europe/London calendar dates.
Tables
users(user_id BIGINT, country_code VARCHAR(2))
calls(call_id BIGINT, caller_id BIGINT, recipient_id BIGINT, started_at TIMESTAMP)
Hints
- Derive report_date as DATE(started_at). For January dates, Europe/London is aligned with UTC, so no offset is needed.
- Build the 7-day inclusive window as [report_date - 6 days, report_date] and aggregate outgoing calls per caller over that window.