Calculate Shop Visibility Ranking in Search Results

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.

Rank Shops by 7-Day Average Visibility Score

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.

Table shop_impressions

column	type	constraints
date	DATE
shop_id	INTEGER
position	INTEGER
clicks	INTEGER

Sample data:

clicks	date	position	shop_id
45	2024-06-01	1	101
12	2024-06-01	3	102
30	2024-06-02	2	101
18	2024-06-02	1	103
55	2024-06-03	1	101

Expected output

Using the latest date in the data (2024-06-03), consider the 7-day period from 2024-05-28 to 2024-06-03 (inclusive). For each shop, compute avg(1.0/position) over all rows in that window, then rank shops by this avg_visibility in descending order. Based on the sample data, shop 103 has visibility 1.0, shop 101 has average visibility (1 + 0.5 + 1) / 3 = 0.833333, and shop 102 has average visibility 1/3 ≈ 0.333333.

avg_visibility	rank	shop_id
1	1	103
0.833333	2	101
0.333333	3	102

Hints

1. First identify the latest date in the table and build a 7-day window ending on that date.
2. 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.