Katkat
Back

Compute High-Call Usage Rates

MetaData Scientist · Technical Screen
HardData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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:

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