Calculate Daily Visibility Score for Each Shop
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
shop_events
| user_id | event_time | event_type | product_id | shop_id |
|---------|------------|------------|------------|---------|
| 101 | 2023-07-01 | impression | 5501 | 12 |
| 101 | 2023-07-01 | view | 5501 | 12 |
| 102 | 2023-07-01 | impression | 6602 | 12 |
| 103 | 2023-07-01 | view | 7703 | 15 |
| 104 | 2023-07-02 | impression | 5501 | 12 |
##### Scenario
The analytics team wants a daily visibility score for each shop, defined as views divided by impressions.
##### Question
Write a SQL query that, for every shop and calendar day, returns impressions, views, and visibility_ratio = views/impressions, ordered by event_date and shop_id.
##### Hints
Use CASE or FILTER clauses for conditional aggregation.
Quick Answer: This question evaluates a candidate's ability to perform conditional aggregation and time-based grouping to compute derived metrics such as impressions, views, and a visibility ratio per shop and calendar day.