Design SQL Query for Shop Visibility and User Activity Metrics

SHOP_VISIBILITY

+----------+---------+------------+------------+-------------+--------------+
| user_id  | shop_id | event_date | is_visible | signup_date | action_type  |
+----------+---------+------------+------------+-------------+--------------+
| 101      | 12      | 2023-06-01 | TRUE       | 2023-05-20  | view         |
| 102      | 18      | 2023-06-01 | FALSE      | 2021-11-02  | view         |
| 103      | 44      | 2023-06-02 | TRUE       | 2023-06-01  | click        |
| 104      | 12      | 2023-06-02 | TRUE       | 2020-01-15  | purchase     |
| 105      | 18      | 2023-06-02 | FALSE      | 2023-05-30  | view         |
+----------+---------+------------+------------+-------------+--------------+

Scenario

E-commerce ‘shop visibility’ dataset used to test SQL skills and metric design.

Question

Write a query that for each shop returns total visible days, ordered descending (GROUP BY, HAVING, ORDER BY). Extend it to include shop category with a JOIN and apply CASE logic for visibility buckets. Using window functions, rank shops by average daily visibility within their category. Define and implement a metric—using today’s snapshot—to compare activity of new versus old users by grouping users into tenure buckets and reporting activity rates.

Hints

Compute tenure as DATEDIFF(today, signup_date); activity = active_events / users.

Per-shop visible days

Compute the number of distinct days each shop was visible (is_visible = TRUE). Exclude shops with zero visible days and order results by visible_days descending, then shop_id ascending.

Table SHOP_VISIBILITY

column	type	constraints
user_id	INTEGER	NOT NULL
shop_id	INTEGER	NOT NULL
event_date	DATE	NOT NULL
is_visible	BOOLEAN	NOT NULL
signup_date	DATE	NOT NULL
action_type	VARCHAR(20)	NOT NULL

Sample data:

action_type	event_date	is_visible	shop_id	signup_date	user_id
view	2023-06-01	true	12	2023-05-20	101
view	2023-06-01	false	18	2021-11-02	102
click	2023-06-02	true	44	2023-06-01	103
purchase	2023-06-02	true	12	2020-01-15	104
view	2023-06-02	false	18	2023-05-30	105

Expected output

Per-shop count of distinct dates where the shop was visible, excluding shops with zero visible days, ordered by visible_days descending then shop_id ascending.

shop_id	visible_days
12	2
44	1

Hints

1. Use COUNT(DISTINCT ...) on event_date conditioned by is_visible
2. Filter out zero counts with HAVING
3. Order by visible_days desc

Visibility by category rank

For each shop, join its category, compute visible_days across all dates, derive avg_daily_visibility = visible_days / total_distinct_event_days, bucket visibility (None/Low/High), and rank shops within their category by avg_daily_visibility (ties get the same rank).

Table SHOP_VISIBILITY

column	type	constraints
user_id	INTEGER	NOT NULL
shop_id	INTEGER	NOT NULL
event_date	DATE	NOT NULL
is_visible	BOOLEAN	NOT NULL
signup_date	DATE	NOT NULL
action_type	VARCHAR(20)	NOT NULL

Sample data:

action_type	event_date	is_visible	shop_id	signup_date	user_id
view	2023-06-01	true	12	2023-05-20	101
view	2023-06-01	false	18	2021-11-02	102
click	2023-06-02	true	44	2023-06-01	103
purchase	2023-06-02	true	12	2020-01-15	104
view	2023-06-02	false	18	2023-05-30	105

Table SHOP_DIM

column	type	constraints
shop_id	INTEGER	PRIMARY KEY, NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	shop_id	name
Electronics	12	NULL
Fashion	18	shop_id
Electronics	44	NULL

Expected output

Per-shop visibility enriched with category, average daily visibility over all dates, visibility bucket, and rank within the category by average daily visibility.

avg_daily_visibility	category	category_rank	shop_id	visibility_bucket	visible_days
1	Electronics	1	12	High	2
0.5	Electronics	2	44	Low	1
0	Fashion	1	18	None	0

Hints

1. Aggregate to shop-day first to avoid double counting
2. Use total distinct event days as the denominator
3. Rank within category with DENSE_RANK

Snapshot activity by tenure

Snapshot activity by user tenure

Using the SHOP_VISIBILITY table and a fixed snapshot date of 2023-06-02, summarize that day's activity broken down by each user's tenure bucket.

Consider only the events that occurred on the snapshot date (event_date = DATE '2023-06-02'). For each such event, compute the user's tenure in days as snapshot_date - signup_date and assign a tenure bucket:

• New — tenure < 30 days
• Established — tenure 30 to 179 days (i.e. >= 30 and < 180)
• Old — tenure >= 180 days

Return one row per tenure bucket that has at least one event on the snapshot date, with the following columns:

• snapshot_date — the constant date 2023-06-02
• tenure_bucket — 'New', 'Established', or 'Old'
• active_users — count of distinct user_id values active in that bucket on the snapshot date
• active_events — total number of events (rows) in that bucket on the snapshot date
• activity_rate — active_events / active_users (events per active user), rounded to 4 decimal places

Order the result by tenure_bucket ascending (alphabetical).

Table SHOP_VISIBILITY

column	type	constraints
user_id	INTEGER	NOT NULL
shop_id	INTEGER	NOT NULL
event_date	DATE	NOT NULL
is_visible	BOOLEAN	NOT NULL
signup_date	DATE	NOT NULL
action_type	VARCHAR(20)	NOT NULL

Sample data:

action_type	event_date	is_visible	shop_id	signup_date	user_id
view	2023-06-01	true	12	2023-05-20	101
view	2023-06-01	false	18	2021-11-02	102
click	2023-06-02	true	44	2023-06-01	103
purchase	2023-06-02	true	12	2020-01-15	104
view	2023-06-02	false	18	2023-05-30	105
view	2023-06-02	true	44	2023-04-10	106
click	2023-06-02	true	12	2023-04-10	106

Table SHOP_DIM

column	type	constraints
shop_id	INTEGER	PRIMARY KEY, NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	shop_id
Electronics	12
Fashion	18
Electronics	44

Expected output

On 2023-06-02, one row per active tenure bucket with distinct active users, total events, and activity_rate (events per user), ordered by bucket.

active_events	active_users	activity_rate	snapshot_date	tenure_bucket
2	1	2.0000	2023-06-02	Established
2	2	1.0000	2023-06-02	New
1	1	1.0000	2023-06-02	Old

Hints

1. Pin the snapshot date in a CTE and join on event_date to keep only that day's events.
2. In PostgreSQL, subtracting two DATEs (snapshot_date - signup_date) yields an integer day count - no DATEDIFF needed.
3. Use COUNT(DISTINCT user_id) for active_users vs COUNT(*) for events, and cast to numeric before dividing.