Write SQL for call analytics
Company: Meta
Role: Product Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given two tables.
**Table: `calls`**
- `call_id` BIGINT
- `sender_id` BIGINT
- `receiver_id` BIGINT
- `call_ts` TIMESTAMP — stored in UTC
- `pickup` CHAR(1) — `'Y'` if the call was picked up, `'N'` otherwise
- `call_type` VARCHAR — either `'video'` or `'voice'`
Assume each row represents one call attempt.
**Table: `users`**
- `user_id` BIGINT
- `country` VARCHAR
Assume `calls.sender_id = users.user_id` when you need the caller's country. Unless otherwise stated, compute results over the full available dataset.
Write SQL for the following:
1. Return the total number of calls and the number of picked-up calls for each `call_type`.
- Output columns: `call_type`, `total_calls`, `picked_up_calls`
2. For a given country parameter, compute the percentage of calls placed by senders in that country that were picked up.
- Output columns: `country`, `pickup_rate_pct`
- Treat `pickup_rate_pct` as `100.0 * picked_up_calls / total_calls`
3. Compute the percentage of distinct callers who have made at least one `video` call and at least one `voice` call.
- Denominator: all distinct `sender_id` values in `calls`
- Output column: `pct_callers_both_types`
Quick Answer: This question evaluates a candidate's ability to perform SQL-based data manipulation and analytics, testing skills such as aggregations, joins to user metadata, percentage/rate calculations, and distinct-count reasoning over call records.