Identify Shops with Low Weekly Visibility Rates
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates a candidate's competence in SQL data manipulation and analytical metrics, including aggregating boolean fields, computing daily and average visibility rates, and applying date-based filters and conditional logic.
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).
Tables
SHOP_VISIBILITY(shop_id INTEGER, user_id INTEGER, view_date DATE, visible BOOLEAN)
Hints
- Filter rows to the fixed 7-day window: view_date between '2025-05-26' and '2025-06-01'.
- Compute daily visibility per shop and date using AVG(CASE WHEN visible THEN 1.0 ELSE 0.0 END).