Meta·Data Scientist·Technical Phone Screen·Intermediate

IntermediatePrefer not to say

Jul 2026Remote

Summary

Meta Data Scientist SQL round, one question but it had enough moving parts to keep me busy for a while. The date spine thing tripped me up more than I expected.

Questions Asked (1)

Given a table with user composer events (enter, post, cancel), calculate the daily post success rate, defined as posts divided by enters, for each date in a fixed 7-day window ending on 2025-09-01. Every date in the window must appear in the output even with no activity, and the rate should be rounded to 2 decimal places with no integer division.

Product Analytics & MetricsData Modeling

Author's notes

The ratio part was fine, NULLIF on the enter count to dodge division by zero, cast one side to float, round to 2.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, generate a complete date series for the 7-day window ending 2025-09-01 to ensure all dates appear. Then, aggregate the event counts per date, pivot to get enters and posts, and compute the success rate as posts divided by enters, handling division by zero and rounding to 2 decimal places. Finally, join the date series with the aggregated data to include dates with no activity.

Pro tip: Explicitly handle division by zero by using NULLIF or CASE to avoid errors, and ensure you cast to float before division to prevent integer division. Also, consider using a calendar table or recursive CTE for the date series to demonstrate scalability.

1. Generate Date Series

Create a list of all dates in the 7-day window ending 2025-09-01, ensuring no gaps. Use a recursive CTE or a date dimension table.

2. Aggregate Event Counts

Group the events table by date and event type, counting occurrences. Pivot or use conditional aggregation to get enters and posts per date.

3. Compute Success Rate

Calculate posts divided by enters, using NULLIF or CASE to handle zero enters, and cast to float to avoid integer division. Round to 2 decimal places.

4. Join and Fill Missing Dates

Left join the date series with the aggregated data, replacing NULLs with 0 for counts and computing the rate (or NULL if no enters).

Key Points to Mention

Use of a date series (e.g., recursive CTE or calendar table) to ensure all dates appear.
Conditional aggregation (SUM(CASE WHEN event='enter' THEN 1 ELSE 0 END)) to count enters and posts.
Handling division by zero with NULLIF or CASE to avoid errors.
Casting to float or decimal before division to prevent integer division.
Rounding the final rate to 2 decimal places using ROUND().
Left join to include dates with no activity and COALESCE to replace NULL counts with 0.

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