Katkat
Back

Write SQL for call pickup and usage metrics

MetaProduct Analyst · Onsite
EasyProduct AnalystOnsiteData Manipulation (SQL/Python)

Last updated

This question tests the ability to join event logs, filter by session timing, and calculate per-user metrics using window functions or group-by operations.

Scenario

You are working with two tables from a voice calling platform.

Table calls contains metadata about initiation:

  • call_id (int)
  • user_id (int)
  • timestamp (datetime)

Table events contains lifecycle updates for those calls:

  • call_id (int)
  • event_type (string): Either 'pickup' or 'missed'
  • event_time (datetime)

Task

Write a SQL query to return a list of users who have made at least one call in the last 30 days. For these users, output their user_id and the pickup_rate, defined as the number of 'pickup' events divided by the total number of calls initiated by that user within the same 30-day window. If a user has made no calls, they should not appear in the results.

Example

Input:

  • calls: (1, 101, '2023-10-01'), (2, 101, '2023-10-05')
  • events: (1, 'pickup', '2023-10-01'), (2, 'missed', '2023-10-05')

Output:

  • 101, 0.5

Constraints

  • Consider that a call_id might appear multiple times in the events table due to logging errors; treat the first occurrence of a status as the truth.
  • Use standard SQL syntax (PostgreSQL preferred).
  • Assume the current date is '2023-10-31'.

Hints

Hint 1

Use a LEFT JOIN between the calls table and a subquery that identifies unique successful pickups.

Hint 2

Apply a filter on the calls table timestamp to ensure only events from the last 30 days are processed.

Hint 3

Handle potential division by zero by using a CASE statement or NULLIF function when calculating the rate.

Follow-up questions

  • How would you modify the query if you wanted to calculate the 7-day rolling average pickup rate for each user?
  • What steps would you take to optimize the query if the events table contained billions of rows?
  • If 'pickup' events could be delayed by up to 10 minutes, how would you adjust the join logic to ensure accuracy?