Katkat
Back

Compute Shop Visibility Rate Using SQL and Python

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.