← Meta Interview Insights

Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

SQL round at Meta for a Data Scientist role. One question, pretty focused on window functions and ranking logic applied to a search visibility use case. Not a brutal interview but the problem had enough layers to trip you up if you rushed.

Questions Asked (1)

Q1

Given a table of daily shop impressions with columns for date, shop_id, position, and clicks, write a SQL query that computes each shop's average visibility score (defined as 1 divided by position) over the past 7 days, then ranks shops from highest to lowest average visibility and returns shop_id, avg_visibility, and rank.

Product Analytics & MetricsData Modeling
Author's notes

I jumped straight to AVG(1.0/position) and almost forgot to filter to the trailing 7 days.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schema and the definition of 'past 7 days' (e.g., relative to current date or max date in table). Then write a SQL query that filters rows to the last 7 days, computes visibility as 1.0/position, averages per shop, and ranks using a window function. Finally, discuss edge cases like missing days and ties in ranking.

Pro tip: Mention that you would use a window function like RANK() or DENSE_RANK() and explain the difference, and note that visibility should be computed as a float to avoid integer division. Also, consider whether to include shops with no impressions in the last 7 days.

1. Clarify requirements and schema

Confirm the table name, columns, and how to define the 7-day window (e.g., using CURRENT_DATE or the max date in the table). Ask about tie-breaking for ranks and whether to include all shops or only those with impressions.

2. Filter to last 7 days

Use a WHERE clause to select rows where date is within the last 7 days relative to the chosen reference date. Be explicit about inclusive/exclusive bounds.

3. Compute average visibility per shop

Calculate visibility as 1.0/position for each row, then group by shop_id and compute AVG(visibility). Ensure the division uses floating-point arithmetic.

4. Rank shops by average visibility

Use a window function like RANK() or DENSE_RANK() over the average visibility in descending order to assign ranks. Decide on tie-handling based on requirements.

5. Select and order final output

Return shop_id, avg_visibility, and rank, ordered by rank ascending (or avg_visibility descending). Consider rounding avg_visibility for readability.

Key Points to Mention

  • Definition of visibility as 1/position and the need for float division (e.g., 1.0/position).
  • How to define the 7-day window: using CURRENT_DATE, max(date), or a parameter, and handling time zones if applicable.
  • Use of window functions (RANK vs DENSE_RANK vs ROW_NUMBER) and their differences for ranking.
  • Handling ties in average visibility and the impact on ranking.
  • Edge cases: shops with no impressions in the last 7 days, missing dates, and position=0 (if possible).
  • Performance considerations: indexing on date and shop_id, and filtering before aggregation.

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