Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Sep 2025Remote

Summary

Meta DS interview with a SQL question that looks straightforward until you actually sit down and write it. One question, pretty focused, left me second-guessing my join direction the whole time.

Questions Asked (1)

Given a user activity table and a composer events table, calculate the average number of posts per Daily Active User broken down by country for a specific date. DAUs are users with an active flag set on that date, posts are only the 'post' events from the composer table on that same date, and users with zero posts still count in the denominator.

Product Analytics & MetricsData Modeling

Author's notes

The zero-post users thing is what gets you.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions: DAU is a user with active flag = 1 on the given date, and posts are 'post' events from the composer table on that date. Then compute the numerator as the total count of post events per country, and the denominator as the count of distinct DAUs per country, ensuring users with zero posts are included. Finally, divide the numerator by the denominator for each country and present the average posts per DAU.

Pro tip: Explicitly state that you will use a LEFT JOIN from the DAU table to the post events table to preserve users with zero posts, and mention that you will handle potential duplicate post events by counting distinct event IDs or using a pre-aggregated subquery.

1. Clarify definitions and assumptions

Confirm that DAU is defined by the active flag on the given date, and that only 'post' events from the composer table count. Also clarify whether a user can have multiple posts and how to handle duplicates.

2. Aggregate posts per country

Filter the composer events table for the specific date and event_type = 'post', then group by country and count the number of post events (or distinct post IDs) to get the numerator.

3. Aggregate DAUs per country

Filter the user activity table for the specific date and active = 1, then group by country and count distinct user IDs to get the denominator.

4. Combine and compute the ratio

Join the two aggregated results on country, ensuring that countries with DAUs but zero posts are included (e.g., using a LEFT JOIN from DAU counts to post counts). Compute the average as total posts divided by total DAUs per country.

5. Validate and present results

Check for anomalies such as countries with zero DAUs, and consider rounding or formatting. Present the final metric clearly, noting any assumptions made.

Key Points to Mention

DAU definition: users with active flag = 1 on the given date, counted distinctly.
Post definition: only 'post' events from the composer table on the same date.
Inclusion of users with zero posts in the denominator (LEFT JOIN or equivalent).
Handling of duplicate post events (e.g., counting distinct event IDs).
Grouping by country and ensuring all countries with DAUs are represented.
Potential edge cases: users with no country, multiple posts per user, and date filtering.

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