Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2025Remote

Summary

Meta data scientist technical screen, one question, all SQL. The problem looked like a reporting query at first glance but had enough edge cases baked in that I ended up rewriting my approach halfway through.

Questions Asked (1)

Using PostgreSQL, write a single query that returns one row per UTC calendar date for the last 7 days (always exactly 7 rows), with columns for date, calls initiated, completion rate, drop rate, and median duration in seconds. Only include calls where neither the caller nor callee is an employee. A call's date is determined by when it started. Median is over completed calls only, using percentile_cont(0.5), and excludes durations of zero or less. Dates with no activity should still appear with zeros or nulls.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures

Author's notes

I started writing a straightforward group-by and got maybe 60% of the way there before realizing I'd completely forgotten to generate the date spine.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by generating a complete date series for the last 7 UTC days using generate_series, then left join aggregated call metrics to ensure all dates appear. Compute metrics with conditional aggregation: count calls for initiated, average of completed indicator for completion rate, average of dropped indicator for drop rate, and percentile_cont(0.5) within group for median duration, filtering out employee calls and zero/negative durations for median.

Pro tip: Mention that you'd verify the query's performance with EXPLAIN ANALYZE and consider indexing on (start_time, caller_is_employee, callee_is_employee) to avoid full scans, showing you think about production readiness.

1. Generate date spine

Use generate_series to create exactly 7 rows for the last 7 UTC calendar dates, ensuring no gaps.

2. Filter calls

Restrict calls to those where neither caller nor callee is an employee, and derive the call date from start_time in UTC.

3. Aggregate metrics per date

For each date, compute calls initiated (count), completion rate (avg of completed flag), drop rate (avg of dropped flag), and median duration using percentile_cont(0.5) over completed calls with duration > 0.

4. Left join and coalesce

Left join the date spine to the aggregated metrics, using COALESCE to replace nulls with zeros for counts and rates, and leave median as null if no completed calls.

5. Format and validate

Ensure the final query returns exactly 7 rows with correct column names and data types, and mentally test edge cases like days with no calls.

Key Points to Mention

Use of generate_series to guarantee exactly 7 rows including dates with no activity.
Filtering out employee calls by checking both caller and callee employee flags.
Using percentile_cont(0.5) WITHIN GROUP (ORDER BY duration) for median, and filtering duration > 0.
Conditional aggregation with FILTER (WHERE ...) for completion and drop rates.
Handling nulls with COALESCE to show zeros for counts and rates on empty days.
Ensuring UTC date extraction with start_time::date or date_trunc('day', start_time AT TIME ZONE 'UTC').

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