Compute Shop Visibility Rate Using SQL and Python

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.

Compute Daily Shop Visibility Rate

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.

Table shop_events

column	type	constraints
event_id	INTEGER	PRIMARY KEY, NOT NULL
shop_id	INTEGER	NOT NULL
user_id	INTEGER	NOT NULL
event_type	VARCHAR(20)	NOT NULL
event_time	TIMESTAMP	NOT NULL

Sample data:

event_id	event_time	event_type	shop_id	user_id
1	2023-07-01 10:05:00	view	101	1001
2	2023-07-01 10:06:00	view	102	1002
3	2023-07-01 10:07:00	purchase	101	1003
4	2023-07-01 10:08:00	view	103	1004
5	2023-07-01 10:10:00	view	101	1001

Expected output

One row per shop per calendar day (UTC). For each shop and day, include total_views (count of 'view' events), unique_visitors (distinct users with at least one 'view'), and visibility_rate = total_views / unique_visitors (average views per visiting user). Non-view events are excluded.

event_date	shop_id	total_views	unique_visitors	visibility_rate
2023-07-01	101	2	1	2
2023-07-01	102	1	1	1
2023-07-01	103	1	1	1

Hints

1. Define visibility as average number of view events per unique visiting user per shop per day: total_views / COUNT(DISTINCT user_id).
2. Truncate event_time to a calendar date (assume UTC) using CAST(event_time AS DATE) or an equivalent function.
3. Filter to event_type = 'view' so that purchases and other non-view events do not affect the metric.
4. Use COUNT(DISTINCT user_id) to deduplicate users in the denominator.
5. Protect against division by zero with NULLIF in the denominator, and decide on rounding precision if needed.