Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL-heavy technical screen for a DS role at Meta. Two questions, both on the same schema, with the second one building on the first. Nothing conceptually wild but the filtering conditions and the multi-account definition tripped me up a bit.

Questions Asked (2)

Using only the accounts table, write a query that buckets users by how many accounts they own: exactly 2, exactly 3, or 4 or more. Return the bucket label and the count of users in each bucket.

Product Analytics & MetricsData Modeling

Author's notes

Straightforward once you see it as a two-step group-by.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, aggregate the accounts table to count accounts per user. Then, use a CASE statement to assign each user to a bucket based on their account count. Finally, group by the bucket label and count the number of users in each bucket.

Pro tip: Consider whether to include users with 0 or 1 account; the question specifies buckets for 2, 3, and 4+, so you may need to filter them out. Also, ensure you handle NULLs appropriately in the user ID column.

1. Count accounts per user

Write a subquery that groups the accounts table by user_id and counts the number of accounts for each user.

2. Assign buckets

In an outer query, use a CASE statement to categorize each user based on their account count into '2', '3', or '4+'.

3. Aggregate by bucket

Group the results by the bucket label and count the number of users in each bucket.

4. Filter and order

Optionally, filter out users with fewer than 2 accounts and order the results by bucket label for clarity.

Key Points to Mention

Use of GROUP BY and COUNT to aggregate accounts per user.
Use of CASE statement for bucketing.
Filtering out users with 0 or 1 account if not needed.
Handling of NULL user IDs.
Ensuring the final output has bucket label and user count.
Consideration of performance for large datasets.

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

Among users who have at least two accounts, what percentage have at least one unread notification across any of their accounts? Use a cutoff date of 2025-01-01 and treat a notification as unread if its read_at is NULL at that point.

Product Analytics & MetricsData Modeling

Author's notes

This one had more moving parts than it looked.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify users with at least two accounts by grouping the accounts table by user_id and filtering for counts >= 2. Then, for each such user, determine if any of their accounts has at least one unread notification (read_at IS NULL) as of the cutoff date 2025-01-01, and compute the percentage of users meeting this condition.

Pro tip: Clarify whether 'unread at that point' means the notification was created before the cutoff and still unread, or simply read_at is NULL regardless of creation date. In practice, you should consider the notification's creation timestamp to avoid counting future notifications.

1. Identify multi-account users

Query the accounts table to find user_ids that have at least two accounts. This gives the denominator population.

2. Filter unread notifications at cutoff

From the notifications table, select notifications where read_at IS NULL and (if applicable) created_at <= '2025-01-01' to represent unread status at the cutoff.

3. Map notifications to users

Join the filtered notifications to the accounts table to associate each notification with its user_id.

4. Determine users with any unread

For each multi-account user, check if they have at least one unread notification across any account. This yields the numerator.

5. Compute percentage

Divide the number of multi-account users with at least one unread notification by the total number of multi-account users, then multiply by 100 to get the percentage.

Key Points to Mention

Definition of 'unread': read_at IS NULL, and consider creation date relative to cutoff to avoid counting future notifications.
Denominator: users with >=2 accounts, not accounts themselves.
Numerator: users with >=1 unread notification across any of their accounts.
Use of LEFT JOIN or subqueries to ensure users with no unread notifications are counted in the denominator but not numerator.
Handling of edge cases: users with multiple accounts but no notifications, or notifications with NULL read_at but created after cutoff.
Efficiency: consider indexing on user_id and read_at for large datasets.

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