Meta·Data Scientist·Technical Phone Screen
- Write a SQL query using CTEs that computes a daily Shop Visibility Score for each shop over a 7-day window. The score is defined as: among sessions with at least one home_feed impression on a given date, what fraction of distinct sessions saw at least one impression from that shop ranked 5 or below on home_feed that same date. Return one row per date and shop with the score, numerator, and denominator.
- From the SVS query results, identify the top 3 shops by 7-day average SVS. If there are ties, break them by total home_feed impressions in the same window.
- The rank column can have gaps, meaning a session might have ranks 2, 4, 6 with no rank 1. Does your query still correctly capture top-5 visibility, or does it need adjustment?
- Identify two ways the SVS metric could be gamed or produce biased results given only the columns in this schema, and propose a SQL-only fix for each.
“This took me longer than I wanted to admit.”