Calculate Shop Visibility Ranking in Search Results
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
shop_impressions
+------------+---------+----------+--------+
| date | shop_id | position | clicks |
+------------+---------+----------+--------+
| 2024-06-01 | 101 | 1 | 45 |
| 2024-06-01 | 102 | 3 | 12 |
| 2024-06-02 | 101 | 2 | 30 |
| 2024-06-02 | 103 | 1 | 18 |
| 2024-06-03 | 101 | 1 | 55 |
+------------+---------+----------+--------+
##### Scenario
An e-commerce marketplace wants to measure how prominently each shop is shown in search results so that marketing can reward high-visibility sellers.
##### Question
Given table shop_impressions(date, shop_id, position, clicks), write a SQL query that
Calculates each shop’s average daily visibility score defined as 1/position over the past 7 days.
Ranks shops by that average visibility (highest first) and returns shop_id, avg_visibility, rank.
##### Hints
Use window or aggregation over a 7-day rolling window; remember to filter by the latest date available.
Overview: This question evaluates SQL data-manipulation skills including time-windowed aggregations, computation of derived visibility metrics, and ranking operations over event-level impression data.
You are given a table shop_impressions(date, shop_id, position, clicks) that records, for each day, where a shop appeared in search results and how many clicks it received. Define a shop's daily visibility score as 1/position for that row.
Write a SQL query that:
1) Uses the latest date in the table as the end of a 7-day window.
2) For that 7-day period, calculates each shop's average daily visibility score as AVG(1.0/position) over all its rows in the window.
3) Ranks shops by this average visibility in descending order.
Return columns: shop_id, avg_visibility, rank.
Tables
shop_impressions(date DATE, shop_id INTEGER, position INTEGER, clicks INTEGER)
Hints
- First identify the latest date in the table and build a 7-day window ending on that date.
- Aggregate by shop_id to compute AVG(1.0/position), then use a window function like RANK() to rank shops by this average in descending order.