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