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.
Quick Answer: 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.