Write SQL for call metrics
Company: Meta
Role: Product Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You have two tables.
calls (
call_id BIGINT,
sender_id BIGINT,
receiver_id BIGINT,
call_ts TIMESTAMP,
picked_up BOOLEAN,
call_type VARCHAR -- values are 'video' or 'voice'
)
users (
user_id BIGINT,
country VARCHAR
)
Assumptions:
- call_ts is stored in UTC.
- sender_id and receiver_id both reference users.user_id.
- A picked-up call means picked_up = TRUE.
- The analysis window is the full history in calls.
- A caller is identified by sender_id.
- Return percentage metrics from 0 to 100 rounded to 2 decimals.
Write SQL for the following tasks:
1. For each call_type, return total_calls, picked_up_calls, and pickup_rate_pct.
Output columns: call_type, total_calls, picked_up_calls, pickup_rate_pct.
2. For calls initiated by users in the United States, return total_calls, picked_up_calls, and pickup_rate_pct.
Output columns: country, total_calls, picked_up_calls, pickup_rate_pct.
3. Return the percentage of distinct callers who made at least one video call and at least one voice call.
Output columns: callers_with_both_pct.
Quick Answer: This question evaluates a candidate's competency in SQL-based data manipulation and analytics, specifically aggregations, JOINs across relational tables, grouping, distinct counting, and percentage calculations with rounding.