Write Call Analytics SQL Queries
Company: Meta
Role: Product Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Assume you are given two tables.
Table: `calls`
- `call_id` BIGINT
- `sender_id` BIGINT
- `receiver_id` BIGINT
- `call_time` TIMESTAMP
- `pickup` VARCHAR -- values: 'Y' or 'N'
- `call_type` VARCHAR -- values: 'video' or 'voice'
Table: `users`
- `user_id` BIGINT
- `country` VARCHAR
Assume:
- `calls.sender_id` and `calls.receiver_id` both reference `users.user_id`.
- All timestamps are stored in UTC.
- Unless otherwise stated, use the last 30 full days of data ending at `CURRENT_DATE` in UTC.
- A 'caller' means `sender_id`.
Write SQL for the following tasks:
1. Return daily call volume and pickup rate by call type for the last 30 days.
Required output columns:
- `call_date`
- `call_type`
- `total_calls`
- `picked_up_calls`
- `pickup_rate`
2. For a specified country (for example, 'US'), compute the percentage of calls initiated by callers in that country that were picked up during the same 30-day window.
Required output columns:
- `country`
- `total_calls`
- `picked_up_calls`
- `pickup_rate`
3. Compute the percentage of unique callers who made at least one voice call and at least one video call during the same 30-day window.
Required output columns:
- `total_unique_callers`
- `callers_with_both_types`
- `pct_callers_with_both_types`
Quick Answer: This question evaluates SQL data manipulation and analytical competencies, including aggregation, joins between user and event tables, time-window filtering, deduplication for unique users, and computation of counts and pickup rates.