Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Meta DS interview with a pretty gnarly SQL question about visibility share ranking. Single question but it had multiple parts and a bunch of edge cases to track. The kind of problem where you think you understand it and then realize you missed something halfway through writing the query.

Questions Asked (1)

Write a SQL query to find the top 3 shops by average daily visibility share over a 7-day window for US non-bot traffic, applying session-level deduplication, handling days with zero eligible sessions, and using CTR as a tiebreaker on deduplicated impressions.

Product Analytics & MetricsData ModelingAlgorithms & Data Structures

Author's notes

This one has layers.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into stages: first filter and deduplicate sessions, then compute daily visibility share per shop, then average over the 7-day window handling zero-session days, and finally rank with CTR as tiebreaker. Use CTEs to modularize the query and ensure clarity. Explicitly state assumptions about visibility share definition and bot detection.

Pro tip: Clarify with the interviewer whether 'visibility share' is per session or per impression, and confirm how to handle days with zero eligible sessions—whether to treat as 0 or exclude from average. This shows attention to metric definition and edge cases.

1. Filter and deduplicate sessions

Filter to US non-bot traffic and deduplicate sessions by taking the first or last event per session ID. Ensure only eligible sessions are considered.

2. Compute daily visibility share per shop

For each shop and each day in the 7-day window, calculate visibility share as the sum of visibility-weighted impressions divided by total impressions, using deduplicated sessions.

3. Handle days with zero eligible sessions

Left join a date spine to shops to include all days. For days with no sessions, set visibility share to 0 (or NULL) and decide whether to include in the average.

4. Average over 7-day window and rank

Compute the average daily visibility share per shop over the 7 days. Rank shops by this average descending, using CTR (on deduplicated impressions) as tiebreaker, and select top 3.

Key Points to Mention

Session-level deduplication: define session boundaries and deduplication logic (e.g., row_number over session_id).
Bot filtering: use a bot flag or user agent pattern to exclude non-human traffic.
Visibility share definition: clarify if it's based on impressions, time in view, or other metric; ensure consistent aggregation.
Zero-session days: use a date spine and left join to avoid dropping shops; decide whether to treat missing as 0 or exclude from average.
CTR tiebreaker: compute CTR on deduplicated impressions, not raw events, to avoid inflation.
Window functions: use ROW_NUMBER or RANK with ORDER BY avg_visibility_share DESC, ctr DESC to get top 3.

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