Analyze New Shops' Activity Compared to Existing Ones

shops

+---------+------------+---------------+
| shop_id | created_at | category      |
+---------+------------+---------------+
| 1       | 2024-01-03 | fashion       |
| 2       | 2023-11-20 | home          |
| 3       | 2024-02-10 | electronics   |
| 4       | 2023-09-15 | fashion       |
| 5       | 2024-03-01 | toys          |
+---------+------------+---------------+

page_views

+----------+---------+---------------------+
| shop_id  | user_id | viewed_at           |
+----------+---------+---------------------+
| 1        | 501     | 2024-03-10 09:01:15 |
| 2        | 877     | 2024-03-10 09:05:11 |
| 1        | 612     | 2024-03-10 09:06:55 |
| 3        | 501     | 2024-03-10 09:07:15 |
| 4        | 933     | 2024-03-10 09:09:05 |
+----------+---------+---------------------+

Scenario

Marketplace wants to understand whether newly onboarded shops are more active and visible than existing ones.

Question

Given the tables below, write SQL to compute daily shop visibility (number of page_views per shop). Then propose and calculate a metric that demonstrates whether shops created in the last 30 days are more active than older shops.

Hints

Join page_views with shops, group by shop_id and date, compare average views of new-shop cohort (<30 days) versus older cohort; consider median to limit outlier impact.

Daily New-vs-Old Shop Visibility Metrics

A marketplace wants to understand whether newly onboarded shops are more active and visible than existing shops.

Use shops and page_views to compute cohort metrics for each day that appears in page_views:

1. Treat a shop as new on a day if created_at is within the 30 days ending on that day, inclusive. Otherwise treat it as old.
2. For each active day, include every shop that had already been created by that day, even if it had zero page views that day.
3. For each (day, cohort), return:
   • day
   • cohort
   • shops_in_cohort
   • total_views
   • avg_views_per_shop, rounded to 3 decimals
   • median_views_per_shop, rounded to 3 decimals
   • new_to_old_avg_ratio: the new cohort average divided by the old cohort average for that same day, rounded to 3 decimals

Order by day, then cohort.

Table shops

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

Sample data:

category	created_at	shop_id
fashion	2024-01-03	1
home	2023-11-20	2
electronics	2024-02-10	3
fashion	2023-09-15	4
toys	2024-03-01	5

Table page_views

column	type	constraints
shop_id	INTEGER	NOT NULL, FOREIGN KEY -> shops.shop_id
user_id	INTEGER	NOT NULL
viewed_at	TIMESTAMP	NOT NULL

Sample data:

shop_id	user_id	viewed_at
1	501	2024-03-10 09:01:15
2	877	2024-03-10 09:05:11
1	612	2024-03-10 09:06:55
3	501	2024-03-10 09:07:15
4	933	2024-03-10 09:09:05

Expected output

Daily cohort-level visibility metrics for new and old shops, including zero-view shops and a new-to-old average-view ratio.

avg_views_per_shop	cohort	day	median_views_per_shop	new_to_old_avg_ratio	shops_in_cohort	total_views
0.5	new	2024-03-10	0.5	0.375	2	1
1.333	old	2024-03-10	1	0.375	3	4

Hints

1. Generate the active day set from page_views first.
2. Cross join active days to eligible shops so zero-view shops are included.
3. Cast percentile_cont(...) to numeric before using ROUND(value, scale) in PostgreSQL.