Define and query shop visibility
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given the following schema. Use only the columns provided; do not introduce new fields or labels.
Tables and columns:
- shops(shop_id INT, shop_name TEXT, country TEXT)
- product_impressions(session_id TEXT, ts TIMESTAMP, product_id INT, shop_id INT, page TEXT, rank INT)
Sample data (ASCII):
shops
+---------+-----------+---------+
| shop_id | shop_name | country |
+---------+-----------+---------+
| 1 | Alpha | US |
| 2 | Beta | US |
| 3 | Gamma | CA |
| 4 | Delta | GB |
+---------+-----------+---------+
product_impressions
+------------+---------------------+------------+---------+-----------+------+
| session_id | ts | product_id | shop_id | page | rank |
+------------+---------------------+------------+---------+-----------+------+
| s1 | 2025-08-31 10:00:00 | 101 | 1 | home_feed | 1 |
| s1 | 2025-08-31 10:00:05 | 102 | 2 | home_feed | 7 |
| s2 | 2025-08-31 11:20:00 | 103 | 1 | home_feed | 3 |
| s3 | 2025-08-30 09:00:00 | 104 | 2 | home_feed | 2 |
| s3 | 2025-08-30 09:00:03 | 105 | 3 | home_feed | 6 |
| s4 | 2025-08-25 15:00:00 | 106 | 3 | home_feed | 5 |
| s5 | 2025-08-29 12:00:00 | 107 | 1 | search | 1 |
| s6 | 2025-08-31 14:00:00 | 108 | 2 | home_feed | 4 |
+------------+---------------------+------------+---------+-----------+------+
Define the daily Shop Visibility Score (SVS) for a shop as: among sessions that had at least one home_feed impression on that calendar date (UTC), the fraction of distinct sessions that saw ≥1 impression from that shop with rank <= 5 on home_feed the same date. A session can contribute to multiple shops' numerators if it saw multiple shops in top-5; the denominator is the distinct session count with any home_feed impression that date.
Tasks:
1) Write a single SQL query (CTEs allowed) that returns, for 2025-08-25 to 2025-08-31 inclusive, one row per (date, shop_id, SVS, numerator_sessions, denominator_sessions). Use only product_impressions and shops.
2) From your result, return the top 3 shops by 7-day average SVS; break ties by higher total home_feed impressions (same window).
3) Suppose rank can have gaps (e.g., no rank=1 for a given session). Explain how your query still correctly counts top-5 visibility or adjust it without adding columns.
4) Identify two failure modes where this SVS could be gamed or biased using only the given schema (e.g., repeated impressions within a session), and propose an SQL-only mitigation for each.
Overview: This question evaluates a candidate's ability to design and compute a session‑level visibility metric using SQL and data manipulation techniques, covering joins, date/window handling, deduplication, aggregations, and metric validation.
Read the full Meta Data Scientist interview experience this question came from
Compute Daily Shop Visibility Score (SVS)
You are given two tables: shops and product_impressions.
Daily Shop Visibility Score (SVS) for a shop on a given UTC calendar date is defined as:
- Consider only sessions that had at least one home_feed impression on that date.
- The denominator_sessions is the number of distinct such sessions (regardless of shop or rank).
- The numerator_sessions for a shop is the number of distinct sessions that saw at least one home_feed impression from that shop with rank <= 5 on that date.
- A session can contribute to multiple shops' numerators if it saw multiple shops in the top-5; the denominator is shared across all shops for that date.
- SVS = numerator_sessions / denominator_sessions. If there are no home_feed sessions that day (denominator_sessions = 0), return SVS as NULL.
Write a single SQL query (CTEs allowed) that returns, for every date from 2025-08-25 to 2025-08-31 inclusive and every shop, one row with:
- impression_date (DATE)
- shop_id
- svs (the daily Shop Visibility Score)
- numerator_sessions
- denominator_sessions
Use only the shops and product_impressions tables and only their existing columns. Do not assume the existence of any other tables.
Tables
shops(shop_id INT, shop_name VARCHAR(50), country VARCHAR(2))
product_impressions(session_id VARCHAR(50), ts TIMESTAMP, product_id INT, shop_id INT, page VARCHAR(50), rank INT)
Hints
- First compute, per date, how many distinct sessions had any home_feed impression to form the denominator.
- Then compute, per date and shop_id, how many distinct sessions had a home_feed impression with rank <= 5, and join this to a calendar of dates and the shops table.
Top Shops by 7-Day SVS and Impressions
Using the same tables and the SVS definition from Question 1, consider the date range 2025-08-25 to 2025-08-31 inclusive.
Define each shop's 7-day SVS over this window as:
- total_numerator_sessions = sum of numerator_sessions over all days in the window
- total_denominator_sessions = sum of denominator_sessions over all days in the window (this is the same for all shops)
- svs_7d = total_numerator_sessions / total_denominator_sessions (NULL if total_denominator_sessions = 0)
Also define total_home_feed_impressions as the total count of rows in product_impressions for that shop where page = 'home_feed' and ts is in the same date range.
Write a single SQL query (CTEs allowed) that returns the top 3 shops ordered by:
1) svs_7d in descending order, and
2) if svs_7d is tied, by total_home_feed_impressions in descending order,
3) if still tied, by shop_id ascending.
The output should include:
- shop_id
- shop_name
- country
- svs_7d
- total_home_feed_impressions
Use only the shops and product_impressions tables.
Tables
shops(shop_id INT, shop_name VARCHAR(50), country VARCHAR(2))
product_impressions(session_id VARCHAR(50), ts TIMESTAMP, product_id INT, shop_id INT, page VARCHAR(50), rank INT)
Hints
- Reuse the per-day numerator and denominator logic from Question 1, then sum over days.
- Compute total denominator once (it is common to all shops), and join per-shop total numerators and total home_feed impressions to the shops table before ranking.
Handling Gaps in Rank for Top-5 Visibility
Assume that the rank column in product_impressions can have gaps within a session and date. For example, a session on a given date might only have rows with rank = 2 and rank = 6, with no rank = 1, 3, 4, or 5.
Without adding any new columns or tables, write or justify an SQL version of your SVS computation from Question 1 that still correctly treats home_feed impressions with rank <= 5 as "top-5" positions, even if some rank values are missing on a page.
Produce a query that computes the same daily SVS output as in Question 1 and explain (in comments or reasoning) why it is still correct when rank values are not contiguous.
Tables
shops(shop_id INT, shop_name VARCHAR(50), country VARCHAR(2))
product_impressions(session_id VARCHAR(50), ts TIMESTAMP, product_id INT, shop_id INT, page VARCHAR(50), rank INT)
Hints
- Think about what "top-5" means: it usually means any item with rank 1 through 5, regardless of whether all values are present.
- If you already use a simple filter like WHERE rank <= 5, you do not need to re-rank rows to handle gaps in rank values.
SVS Metric Biases and SQL Mitigations
The SVS definition based on sessions and ranks can be gamed or biased in several ways using only the existing schema (shops and product_impressions).
Identify two distinct failure modes where a shop could artificially inflate or bias its SVS or its tie-breaker metric (total_home_feed_impressions), using only behaviors that manifest in the given tables (for example, repeated impressions within a session, or sessions that only ever show one shop). For each failure mode, propose an SQL-only mitigation that uses only the existing columns.
Write a query that returns two rows, each describing:
- issue_id (1 or 2)
- issue (short text description of the failure mode)
- mitigation_sql_idea (short text describing how you would change the SVS or ranking query in SQL to mitigate that issue)
This query is explanatory: it can use constant strings in the SELECT to describe the issues and mitigations, but it must be valid SQL against the provided schema.
Tables
shops(shop_id INT, shop_name VARCHAR(50), country VARCHAR(2))
product_impressions(session_id VARCHAR(50), ts TIMESTAMP, product_id INT, shop_id INT, page VARCHAR(50), rank INT)
Hints
- Think about sessions that are too "pure" (only one shop) and repeated impressions that inflate counts without adding new exposure.
- Your query can simply return constant strings that describe each issue and an example SQL pattern that would mitigate it.