Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

Meta Data Scientist interview with a SQL-heavy question focused on marketplace analytics. Pretty straightforward setup but the specific framing around visibility rates and filtering by threshold tripped me up a bit.

Questions Asked (1)

Given a table of shop visibility records (shop ID, user ID, view date, and a boolean visible flag), write a SQL query to calculate each shop's daily visibility rate. Then identify shops whose average visibility rate across the past 7 days falls below 50%.

Product Analytics & MetricsData Modeling

Author's notes

I got the basic structure down pretty fast, CASE WHEN visible = true THEN 1 ELSE 0 END divided by count, grouped by shop and date.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, aggregate the raw visibility records to compute each shop's daily visibility rate by dividing the sum of visible flags by the total views per shop per day. Then, use a window function to calculate a 7-day rolling average of these daily rates and filter for shops where that average is below 50%.

Pro tip: Clarify whether the 7-day window should be based on calendar days or the last 7 days of data, and explicitly state your assumption about handling missing days (e.g., treating them as zero visibility or excluding them).

1. Aggregate daily visibility rate

Group the raw records by shop ID and view date, then compute the daily visibility rate as SUM(visible)/COUNT(*) or AVG(visible).

2. Compute 7-day rolling average

Use a window function (e.g., AVG() OVER (PARTITION BY shop_id ORDER BY view_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) to calculate the rolling average of daily rates over the past 7 days.

3. Filter shops below threshold

Select shops where the 7-day rolling average is less than 0.5, ensuring you consider the most recent date or all dates as needed.

4. Handle edge cases and validate

Address missing dates, partial data, and ensure the window includes only the last 7 days (not 7 rows if dates are missing). Validate results with a small sample.

Key Points to Mention

Definition of visibility rate: visible views divided by total views per shop per day.
Use of window functions for rolling averages (e.g., ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
Handling missing dates: whether to treat as zero visibility or exclude from the window.
Assumption about the 7-day window: calendar days vs. last 7 records.
Filtering condition: average visibility rate < 0.5 (or 50%).
Potential need to deduplicate or handle multiple records per user per day.

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