Quick Overview

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.

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`

Overview: 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.

Compute call analytics: (1) total and picked-up calls by call_type; (2) pickup rate percentage for calls placed by senders in a given country; (3) percentage of distinct callers who have made at least one video and at least one voice call.

Tables

calls(call_id BIGINT, sender_id BIGINT, receiver_id BIGINT, call_ts TIMESTAMP, pickup CHAR(1), call_type VARCHAR)

users(user_id BIGINT, country VARCHAR)

Hints

  1. Use conditional aggregation with SUM(CASE WHEN pickup='Y' THEN 1 ELSE 0 END) for picked-up counts.
  2. Join calls to users on calls.sender_id = users.user_id when filtering by sender country.

Loading coding console...