Identify Shops with Low Weekly Visibility Rates

SHOP_VISIBILITY

+-----------+---------+------------+---------+
| shop_id   | user_id | view_date  | visible |
+-----------+---------+------------+---------+
| 101       | 555     | 2023-07-01 | true    |
| 102       | 556     | 2023-07-01 | false   |
| 101       | 557     | 2023-07-02 | true    |
| 103       | 560     | 2023-07-02 | true    |
| 102       | 561     | 2023-07-03 | false   |
+-----------+---------+------------+---------+

Scenario

Marketplace team wants to measure how visible each shop is to users over time.

Question

Write an SQL query that calculates each shop’s daily visibility rate (percentage of records where visible = true). Identify shops whose average visibility rate is below 50% during the past 7 days.

Hints

Use CASE expressions, GROUP BY, HAVING, and date filters.

Shop visibility rate over a fixed 7-day window

You are given a SHOP_VISIBILITY table that tracks which shops were shown to which users on specific dates, and whether the shop was actually visible to the user. Using the data for the 7-day period from 2025-05-26 through 2025-06-01 (inclusive), write an SQL query that:

1. Calculates each shop’s daily visibility rate (the fraction of records per shop per day where visible = true).
2. Computes, for each shop, the average of these daily visibility rates over that 7-day window.
3. Returns only the shops whose 7-day average visibility rate is below 0.5 (50%), with columns shop_id and avg_visibility_rate_7d (a number between 0 and 1).

Table SHOP_VISIBILITY

column	type	constraints
shop_id	INTEGER
user_id	INTEGER
view_date	DATE
visible	BOOLEAN

Sample data:

shop_id	user_id	view_date	visible
101	555	2025-05-27	true
102	556	2025-05-27	false
101	557	2025-05-28	true
103	560	2025-05-28	true
102	561	2025-05-29	false

Expected output

Return one row per shop whose 7-day average of its daily visibility rates (for dates between 2025-05-26 and 2025-06-01) is below 0.5. Include shop_id and the computed avg_visibility_rate_7d as a fraction between 0 and 1. Based on the sample data, shop 102 has a daily visibility rate of 0.0 on each day it appears and thus a 7-day average of 0.0, so it is the only shop in the result.

avg_visibility_rate_7d	shop_id
0	102

Hints

1. Filter rows to the fixed 7-day window: view_date between '2025-05-26' and '2025-06-01'.
2. Compute daily visibility per shop and date using AVG(CASE WHEN visible THEN 1.0 ELSE 0.0 END).
3. Aggregate those daily visibility rates again per shop to get a 7-day average.
4. Use HAVING to keep only shops with an average visibility rate below 0.5.