Compute High-Call Usage Rates

Q: Compute High-Call Usage Rates

This question evaluates a candidate's ability to perform advanced data manipulation and analytical SQL tasks, including joins, aggregations, timezone-aware date handling, rolling window metrics, distinct user counting, and counting user participations across roles.

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 hard difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Meta during technical interviews.

Question

You are given two tables for a voice-calling product:

users

user_id BIGINT
country_code STRING

calls

call_id BIGINT
caller_id BIGINT
recipient_id BIGINT
started_at TIMESTAMP

calls.caller_id and calls.recipient_id both reference users.user_id.

Assume:

Every row in calls is one valid call event.
All timestamps are stored in UTC.
Any date-based metric should be computed in the Europe/London timezone.
report_date is a calendar date in Europe/London .

Write SQL to compute the following metrics for each report_date:

Percentage of callers with at least 20 calls in the last 7 days
- Use the 7-day window ending on report_date , inclusive.
- The numerator is the number of distinct users who initiated at least 20 calls in that 7-day window.
- The denominator is the number of distinct users who initiated at least 1 call in that same 7-day window.
Percentage of Great Britain DAU with at least 50 total calls
- Restrict to users with country_code = 'GB' .
- Define daily active users as distinct GB users who either placed or received at least one call on report_date .
- For each such user, count total call participations on report_date , where participations include both outgoing calls as caller_id and incoming calls as recipient_id .
- The numerator is the number of GB DAU with at least 50 total call participations on report_date .
- The denominator is all GB DAU on report_date .

Return one row per report_date with these columns:

report_date
pct_callers_20plus_last_7d
pct_gb_dau_50plus_calls

Compute High-Call Usage Rates

Quick Overview

Comments (0)