Compute Shop Visibility Rate Using SQL and Python
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
shop_events
| event_id | shop_id | user_id | event_type | event_time |
| 1 | 101 | 1001 | view | 2023-07-01 10:05:00 |
| 2 | 102 | 1002 | view | 2023-07-01 10:06:00 |
| 3 | 101 | 1003 | purchase | 2023-07-01 10:07:00 |
| 4 | 103 | 1004 | view | 2023-07-01 10:08:00 |
| 5 | 101 | 1001 | view | 2023-07-01 10:10:00 |
##### Scenario
E-commerce marketplace wants to quantify each shop’s visibility to users.
##### Question
Using the data below, write SQL to compute each shop’s daily visibility rate (total views divided by unique visiting users).
2) Propose and clearly define a visibility metric and confirm it with the interviewer before coding.
##### Hints
Explain assumptions, handle time zones, deduplicate users, ignore non-view events unless justified.
Overview: This question evaluates proficiency in data manipulation and metric design using SQL and Python, focusing on event-level aggregation, user deduplication, and definition of a business visibility metric within the Data Manipulation (SQL/Python) domain.
Using the shop_events table, compute each shop’s daily visibility rate, defined as the total number of 'view' events divided by the number of unique users who viewed that shop on that calendar day. Return one row per shop per day with total_views, unique_visitors, and visibility_rate. Assume event_time is in UTC and non-view events should be excluded from this metric.
Tables
shop_events(event_id INTEGER, shop_id INTEGER, user_id INTEGER, event_type VARCHAR(20), event_time TIMESTAMP)
Hints
- Define visibility as average number of view events per unique visiting user per shop per day: total_views / COUNT(DISTINCT user_id).
- Truncate event_time to a calendar date (assume UTC) using CAST(event_time AS DATE) or an equivalent function.