Write SQL for call pickup and usage metrics

Q: Write SQL for call pickup and usage metrics

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.

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.

Q: What difficulty level is this interview question?

This is a easy difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Product Analyst candidates at Meta during technical interviews.

Question

Loading...

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

Write SQL for call pickup and usage metrics

Quick Overview

Table 1: `calls`

Table 2: `users`

Q1) Basic aggregation

Q2) Pickup percentage for a specific country

Q3) Callers who used both video and voice

Comments (0)

Write SQL for call pickup and usage metrics

Quick Overview