Katkat
Back

Count heavy callers in 7 days

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

Last updated

This question tests the ability to handle temporal windowing and aggregation to identify user behavior patterns based on call activity frequency.

Problem Statement

You are given two tables: users and calls.

Table users:

  • user_id (int): Unique identifier for each user.
  • country (varchar): The country where the user is based.

Table calls:

  • caller_id (int): The ID of the person making the call.
  • receiver_id (int): The ID of the person receiving the call.
  • call_time (datetime): The timestamp of the call.

A "heavy caller" is defined as a user who has called at least 5 distinct receivers within any rolling 7-day window.

Write a query to identify the user_id of all heavy callers. Your result should contain unique user_ids.

Example

Input: calls table:

caller_idreceiver_idcall_time
1102023-01-01 10:00:00
1112023-01-02 10:00:00
1122023-01-03 10:00:00
1132023-01-04 10:00:00
1142023-01-05 10:00:00

Output:

user_id
1

Explanation: User 1 called 5 distinct people between 2023-01-01 and 2023-01-05, which falls within a 7-day span.

Constraints

  • call_time is in UTC.
  • Assume a large dataset; consider efficiency of your window function or self-join.
  • Focus on the logic to identify the 7-day rolling window.

Hints

Hint 1

Consider using a window function like `COUNT(DISTINCT receiver_id) OVER (...)` to look at a 7-day range.

Hint 2

Since standard SQL window functions often support `RANGE BETWEEN` on numeric types, you may need to convert timestamps to integers or use a self-join if your SQL dialect doesn't support interval-based window frames.

Hint 3

If using a self-join, join the table to itself on `caller_id` and ensure the `call_time` difference is between 0 and 6 days.

Follow-up questions

  • How would you optimize this query if the `calls` table contained billions of rows?
  • What changes would you make if we needed to define 'heavy callers' as those who spent more than 60 minutes in total calls within that same 7-day window?
  • How does the choice of database index (e.g., an index on `caller_id` vs `call_time`) affect the performance of this query?