Calculate Daily Visibility Score for Each Shop

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.

Daily Shop Visibility Ratio

Given a table of shop events, write a SQL query that, for every shop and calendar day, returns the number of impressions, the number of views, and a visibility_ratio defined as views divided by impressions. If impressions is 0, visibility_ratio should be NULL. Order the result by event_date and shop_id.

Table shop_events

column	type	constraints
user_id	INTEGER
event_time	DATE
event_type	VARCHAR
product_id	INTEGER
shop_id	INTEGER

Sample data:

event_time	event_type	product_id	shop_id	user_id
2023-07-01	impression	5501	12	101
2023-07-01	view	5501	12	101
2023-07-01	impression	6602	12	102
2023-07-01	view	7703	15	103
2023-07-02	impression	5501	12	104

Expected output

For each shop and date, return counts of impressions and views and the visibility_ratio (views divided by impressions). If impressions is 0, visibility_ratio is NULL. Results are ordered by event_date then shop_id.

event_date	impressions	shop_id	views	visibility_ratio
2023-07-01	2	12	1	0.5
2023-07-01	0	15	1	NULL
2023-07-02	1	12	0	0

Hints

1. Use CASE expressions for conditional aggregation (or FILTER if your SQL dialect supports it).
2. Cast counts to a decimal type and use NULLIF(impressions, 0) in the denominator to avoid division by zero.