Write SQL for call pickup and usage metrics
Company: Meta
Role: Product Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Onsite
You have two tables about 1:1 calls.
## Table 1: `calls`
Each row is a call attempt.
- `sender_id` (BIGINT) — user who initiated the call
- `receiver_id` (BIGINT) — user who received the call
- `call_time` (TIMESTAMP) — when the call was initiated (assume UTC)
- `picked_up` (BOOLEAN) — whether the receiver picked up
- `call_type` (VARCHAR) — `'video'` or `'voice'`
## Table 2: `users`
- `user_id` (BIGINT, PRIMARY KEY)
- `country` (VARCHAR)
Assume `calls.sender_id` and `calls.receiver_id` both reference `users.user_id`.
### Q1) Basic aggregation
Return daily call volume and pickup rate by call type.
- Output columns: `call_date`, `call_type`, `call_attempts`, `picked_up_calls`, `pickup_rate`
### Q2) Pickup percentage for a specific country
Compute the percentage of calls that are picked up **for users in a given country** (parameter `:country`).
- Define the metric as: among calls where the **receiver** is in `:country`, the fraction with `picked_up = true`.
- Output columns: `country`, `call_attempts`, `picked_up_calls`, `pickup_rate`
### Q3) Callers who used both video and voice
Compute the percentage of distinct callers (`sender_id`) who made **at least one video call and at least one voice call** in a given time window `[ :start_time, :end_time )`.
- Output columns: `total_callers`, `callers_both_types`, `pct_callers_both_types`
Notes:
- Handle division-by-zero safely.
- You may assume standard SQL (Postgres-like syntax is fine).
Quick Answer: This question evaluates proficiency in SQL data manipulation—specifically aggregation, joins, filtering, and safe handling of edge cases—to compute call pickup and usage metrics from event tables.