Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2025Remote

Summary

Meta data scientist SQL round, one question, pretty focused on filtering logic and aggregation. The problem looked straightforward at first but there were enough edge cases baked in that you had to actually think through the constraints carefully.

Questions Asked (1)

Write a single SQL query to return the count of unique callers who initiated calls to more than 3 distinct other users within a specific one-week UTC window. Only the caller's perspective counts (no inbound), repeated calls to the same person collapse to one, self-calls are excluded, and calls outside the date range are dropped. Return a single integer column named caller_cnt.

Product Analytics & MetricsData Modeling

Author's notes

There's a lot of conditions stacked on top of each other here and I almost forgot about the self-call exclusion until I re-read the schema.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Filter the calls table to the one-week UTC window and exclude self-calls, then deduplicate caller-receiver pairs using DISTINCT. Group by caller, count distinct receivers, and filter to those with more than 3 receivers, finally counting the resulting callers.

Pro tip: Explicitly state your assumptions about the schema (e.g., table name, column names, timestamp type) and clarify that 'distinct other users' means unique receivers per caller, not total unique users across all calls.

1. Filter and clean the data

Apply the date range filter on the call timestamp (UTC) and exclude rows where caller_id equals receiver_id to remove self-calls.

2. Deduplicate caller-receiver pairs

Use SELECT DISTINCT caller_id, receiver_id to collapse repeated calls between the same caller and receiver into a single pair.

3. Count distinct receivers per caller

Group by caller_id and count the number of distinct receiver_id values for each caller.

4. Filter callers with >3 receivers

Apply a HAVING clause to keep only callers whose distinct receiver count exceeds 3.

5. Count qualifying callers

Wrap the previous result in a subquery or use COUNT(*) OVER () to return the total number of such callers as a single integer column named caller_cnt.

Key Points to Mention

Use of DISTINCT or GROUP BY to deduplicate caller-receiver pairs
Correct handling of UTC date range with inclusive/exclusive boundaries
Exclusion of self-calls via caller_id != receiver_id
Filtering with HAVING COUNT(DISTINCT receiver_id) > 3
Returning a single integer column named caller_cnt
Assumptions about table schema and timestamp format

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.