Write SQL for call analytics

Q: Write SQL for call analytics

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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:

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
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
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

Write SQL for call analytics

Quick Overview

Comments (0)