← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Apr 2026

Summary

Meta data scientist SQL round, two tasks in one prompt. The schema setup was clean but the edge cases around missing hashtag IDs and deduplication were where things got interesting fast.

Questions Asked (2)

Q1

Given a table of hashtag follow events and a hashtag reference table, deduplicate exact duplicate rows and then rank each traffic source by total follows today. Return the source name, follow count, and rank, breaking ties alphabetically.

Product Analytics & MetricsData Modeling
Author's notes

The dedup part is what gets you.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by deduplicating the follow events table using DISTINCT or GROUP BY to remove exact duplicate rows. Then filter for today's events, join with the hashtag reference table to get source names, aggregate follow counts per source, and finally rank sources using a window function like RANK() or DENSE_RANK() with an ORDER BY count DESC, source ASC to break ties alphabetically.

Pro tip: Clarify whether 'today' refers to the current date or the latest date in the data, and confirm the definition of 'exact duplicate' (all columns identical). Also, consider if deduplication should happen before or after filtering by date, as it can affect counts.

1. Understand the data and requirements

Identify the relevant tables, columns, and the definition of 'today' and 'exact duplicate'. Confirm whether deduplication should be applied to the entire table or only today's events.

2. Deduplicate the follow events

Use SELECT DISTINCT or GROUP BY on all columns of the follow events table to remove exact duplicate rows. If the table has a unique event ID, deduplication might be unnecessary, but assume no ID.

3. Filter for today's events and join reference table

Apply a date filter to keep only events from today. Then join with the hashtag reference table on hashtag ID to retrieve the source name for each hashtag.

4. Aggregate follows by source

Group by source name and count the number of follows. This gives the total follows per source for today.

5. Rank sources with tie-breaking

Use a window function like RANK() or DENSE_RANK() over (ORDER BY follow_count DESC, source_name ASC) to assign ranks. Ensure ties are broken alphabetically by source name.

Key Points to Mention

  • Deduplication method: DISTINCT vs GROUP BY, and handling of NULLs
  • Date filtering: using CURRENT_DATE or a specific date, and timezone considerations
  • Join type: INNER JOIN to ensure only hashtags with reference data are included
  • Aggregation: COUNT(*) vs COUNT(DISTINCT user_id) depending on definition of 'follow'
  • Ranking function: RANK() vs DENSE_RANK() vs ROW_NUMBER() and their differences
  • Tie-breaking: ORDER BY count DESC, source ASC within the window function

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

Q2

Using the same deduplicated data, calculate what percentage of today's follows from the hashtag page source are on hashtags flagged as violating. Return a single value rounded to two decimal places.

Product Analytics & MetricsData Modeling
Author's notes

This one required an inner join to the hashtag table, which means rows with no matching hashtag get dropped.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the definitions of 'today's follows', 'hashtag page source', and 'flagged as violating'. Then, using the deduplicated dataset, filter to follows that occurred today and originated from the hashtag page, and compute the percentage of those follows that are on violating hashtags, rounding to two decimal places.

Pro tip: Always confirm the time zone and the exact definition of 'today' (e.g., UTC vs. local) and whether 'flagged as violating' refers to the hashtag's current status or its status at the time of the follow. This avoids off-by-one errors and ensures alignment with business expectations.

1. Clarify definitions and assumptions

Confirm what 'today' means (date range and time zone), what constitutes a 'follow' from the 'hashtag page source', and how a hashtag is 'flagged as violating' (e.g., current flag vs. historical).

2. Filter to relevant follows

From the deduplicated dataset, select only follows that occurred today and have source = 'hashtag page'.

3. Identify violating hashtags

Determine which hashtags are flagged as violating, either by joining with a hashtag metadata table or using a flag column in the dataset.

4. Compute the percentage

Count the number of follows from step 2 that are on violating hashtags, divide by the total number of follows from step 2, and multiply by 100.

5. Round and validate

Round the result to two decimal places and sanity-check the number (e.g., ensure it's between 0 and 100, and consider edge cases like zero total follows).

Key Points to Mention

  • Importance of deduplication to avoid double-counting follows
  • Definition of 'today' and time zone considerations
  • Source filtering: only follows from the hashtag page
  • Definition of 'flagged as violating' (current vs. historical status)
  • Handling of edge cases (e.g., no follows, missing data)
  • Rounding to two decimal places and presenting the result clearly

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