Coding questions · Meta

Write SQL for shop visibility and activity metric

This question evaluates SQL-based data manipulation and analytical skills, including aggregations, window functions, cohort comparisons, ranking, median calculation with analytic functions, and careful handling of NULLs versus zeros when constructing time-windowed metrics like visibility_rate and activity_score.

Assume 'today' is 2025-09-01. Schema and tiny samples: 1) shops(shop_id INT, created_at DATE) Sample:

 shop_id | created_at
 1 | 2025-08-25
 2 | 2025-07-15
 3 | 2025-08-30
 4 | 2025-06-01
 5 | 2025-08-05

listings(listing_id INT, shop_id INT, created_at DATE, is_visible TINYINT) -- is_visible is a 2025-09-01 snapshot Sample:

 listing_id | shop_id | created_at | is_visible
 101 | 1 | 2025-08-26 | 1
 102 | 1 | 2025-08-27 | 0
 103 | 1 | 2025-08-29 | 1
 104 | 2 | 2025-07-20 | 1
 105 | 2 | 2025-07-25 | 1
 106 | 3 | 2025-08-31 | 0
 107 | 3 | 2025-08-31 | 0
 108 | 4 | 2025-06-05 | 1
 109 | 5 | 2025-08-10 | 1

shop_events(shop_id INT, event_date DATE, event_type VARCHAR, cnt INT) -- event_type in ('product_created','listing_updated','message_replied','order_placed') Sample:

 shop_id | event_date | event_type | cnt
 1 | 2025-08-26 | product_created | 3
 1 | 2025-08-29 | message_replied | 5
 2 | 2025-08-27 | order_placed | 2
 3 | 2025-08-31 | listing_updated | 4
 3 | 2025-08-30 | order_placed | 1
 4 | 2025-08-28 | product_created | 1
 5 | 2025-08-21 | product_created | 2
 5 | 2025-08-25 | message_replied | 1

Tasks: A) Write SQL to compute per-shop visibility_rate on 2025-09-01 = AVG(is_visible) over that shop’s listings; return only shops with at least 5 listings and rank by visibility_rate DESC, breaking ties by total listings DESC. B) Define a 'new shop' as created_at >= 2025-08-02 (last 30 days). Define activity_score over the window [2025-08-19, 2025-09-01] as 3order_placed + 1product_created + 0.5listing_updated + 0.2message_replied using counts from shop_events; shops with no events have score 0. Write SQL to output one row per shop with: shop_id, is_new, visibility_rate (from A; default NULL if <1 listing), activity_score. C) Using that output, write SQL to produce a 3-row summary: metric_name, new_shops_value, existing_shops_value, pct_diff for (i) mean activity_score, (ii) median activity_score (use an analytic function), and (iii) share_of_active_shops (fraction with activity_score > 0). D) Finally, list the top 5 new shops by activity_score whose visibility_rate < 0.5 to identify active-but-underexposed shops. Explain any assumptions you make about NULL vs 0 and how you’d prevent survivorship bias.

Shop visibility rate on snapshot date

You work with three tables: shops, listings, and shop_events. On the fixed snapshot date 2025-06-01, the listings table records whether each listing is visible via the is_visible column (1 = visible, 0 = hidden).

Define a shop's visibility_rate on 2025-06-01 as the average of is_visible across all of that shop's listings (i.e., AVG(is_visible) for that shop).

Write a SQL query to:

Compute visibility_rate per shop.
Return only shops that have at least 5 listings.
Output columns: shop_id, visibility_rate, total_listings.
Sort the result in descending order of visibility_rate, breaking ties by total_listings in descending order (and optionally by shop_id ascending for deterministic output).

Table `shops`

column	type	constraints
shop_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	shop_id
2025-05-25	1
2025-04-15	2
2025-05-30	3
2025-03-20	4
2025-05-05	5
2025-04-01	6

Table `listings`

column	type	constraints
listing_id	INT	PRIMARY KEY, NOT NULL
shop_id	INT	NOT NULL
created_at	DATE	NOT NULL
is_visible	TINYINT	NOT NULL

Sample data:

created_at	is_visible	listing_id	shop_id
2025-05-26	1	101	1
2025-05-27	0	102	1
2025-05-28	0	103	1
2025-05-29	0	104	1
2025-04-20	1	201	2
2025-04-21	1	202	2
2025-04-22	1	203	2
2025-04-23	0	204	2
2025-04-24	0	205	2
2025-05-31	0	301	3
2025-05-31	0	302	3
2025-05-31	0	303	3
2025-05-31	0	304	3
2025-05-31	0	305	3
2025-03-25	1	401	4
2025-03-26	1	402	4
2025-05-06	1	501	5

Table `shop_events`

column	type	constraints
shop_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(32)	NOT NULL
cnt	INT	NOT NULL

Sample data:

cnt	event_date	event_type	shop_id
3	2025-05-26	product_created	1
5	2025-05-29	message_replied	1
10	2025-05-10	product_created	1
2	2025-05-20	order_placed	2
2	2025-05-22	listing_updated	2
4	2025-05-18	product_created	2
4	2025-05-31	listing_updated	3
1	2025-05-30	order_placed	3
1	2025-05-21	product_created	4
1	2025-05-25	message_replied	5
2	2025-05-17	product_created	5

Expected output

One row per shop that has at least 5 listings, with the average visibility (fraction of listings where is_visible = 1) and the total number of listings, sorted by visibility_rate descending and then total_listings descending. With the sample data, only shops 2 and 3 have at least 5 listings.

shop_id	total_listings	visibility_rate
2	5	0.6
3	5	0

Hints

Aggregate over listings by shop_id and use AVG on the is_visible flag.
Filter after aggregation with HAVING and sort with ORDER BY using multiple keys.

Per-shop new/existing flag, visibility, and activity score

Using the same tables, define metrics relative to the fixed reference date 2025-06-01.

Definitions:

A 'new shop' is a shop with created_at >= '2025-05-02' (shops opened in the last 30 days, from 2025-05-02 to 2025-06-01 inclusive).
The visibility_rate for a shop is the average of is_visible across all its listings on the 2025-06-01 snapshot. If a shop has no listings, its visibility_rate should be NULL.
The activity_score for a shop over the window from '2025-05-19' to '2025-06-01' (inclusive) is defined as: activity_score = 3 * order_placed + 1 * product_created + 0.5 * listing_updated + 0.2 * message_replied where order_placed, product_created, listing_updated, and message_replied are the summed cnt values from shop_events of each event_type over that date range.
Shops with no events in that window should have activity_score = 0.

Write a SQL query that outputs one row per shop with the columns:

shop_id
is_new (1 for new shops, 0 for existing shops)
visibility_rate (NULL if the shop has no listings)
activity_score (0 if the shop has no events in the window)

Order the result by shop_id ascending.

Table `shops`

column	type	constraints
shop_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	shop_id
2025-05-25	1
2025-04-15	2
2025-05-30	3
2025-03-20	4
2025-05-05	5
2025-04-01	6

Table `listings`

column	type	constraints
listing_id	INT	PRIMARY KEY, NOT NULL
shop_id	INT	NOT NULL
created_at	DATE	NOT NULL
is_visible	TINYINT	NOT NULL

Sample data:

created_at	is_visible	listing_id	shop_id
2025-05-26	1	101	1
2025-05-27	0	102	1
2025-05-28	0	103	1
2025-05-29	0	104	1
2025-04-20	1	201	2
2025-04-21	1	202	2
2025-04-22	1	203	2
2025-04-23	0	204	2
2025-04-24	0	205	2
2025-05-31	0	301	3
2025-05-31	0	302	3
2025-05-31	0	303	3
2025-05-31	0	304	3
2025-05-31	0	305	3
2025-03-25	1	401	4
2025-03-26	1	402	4
2025-05-06	1	501	5

Table `shop_events`

column	type	constraints
shop_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(32)	NOT NULL
cnt	INT	NOT NULL

Sample data:

cnt	event_date	event_type	shop_id
3	2025-05-26	product_created	1
5	2025-05-29	message_replied	1
10	2025-05-10	product_created	1
2	2025-05-20	order_placed	2
2	2025-05-22	listing_updated	2
4	2025-05-18	product_created	2
4	2025-05-31	listing_updated	3
1	2025-05-30	order_placed	3
1	2025-05-21	product_created	4
1	2025-05-25	message_replied	5
2	2025-05-17	product_created	5

Expected output

One row per shop, with a 0/1 is_new flag, the average visibility across its listings (or NULL if it has no listings), and the weighted activity_score over the window 2025-05-19 to 2025-06-01 (0 if the shop has no events in that window). Using the sample data:

New shops are 1, 3, and 5.
Visibility rates: shop1=0.25, shop2=0.6, shop3=0.0, shop4=1.0, shop5=1.0, shop6=NULL.
Activity scores: shop1=4.0, shop2=7.0, shop3=5.0, shop4=1.0, shop5=0.2, shop6=0.0.

activity_score	is_new	shop_id	visibility_rate
4	1	1	0.25
7	0	2	0.6
5	1	3	0
1	0	4	1
0.2	1	5	1
0	0	6	NULL

Hints

Pre-aggregate listings by shop_id to get visibility_rate, then left join to shops.
Pre-aggregate shop_events by shop_id over the given date range and COALESCE NULL scores to 0.

Compare new vs existing shops using mean, median, and activity share

You are analyzing seller shops on a marketplace. You have three tables:

shops — one row per shop.

column	type	notes
`shop_id`	INT	primary key
`created_at`	DATE	date the shop was created

listings — one row per product listing (provided for context; not required for this question).

column	type	notes
`listing_id`	INT	primary key
`shop_id`	INT	the shop the listing belongs to
`created_at`	DATE	date the listing was created
`is_visible`	SMALLINT	1 if the listing is publicly visible, else 0

shop_events — one row per (shop, day, event type), with an aggregated count.

column	type	notes
`shop_id`	INT	the shop
`event_date`	DATE	day the events occurred
`event_type`	VARCHAR(32)	one of `order_placed`, `product_created`, `listing_updated`, `message_replied`
`cnt`	INT	number of events of that type on that day

Per-shop definitions (carried over from the previous question):

A shop is new (is_new = 1) if created_at >= '2025-05-02', otherwise it is existing (is_new = 0).
A shop's activity_score is computed over the window event_date from '2025-05-19' to '2025-06-01' (inclusive on both ends) as a weighted sum of its events:
- order_placed → 3.0 per event
- product_created → 1.0 per event
- listing_updated → 0.5 per event
- message_replied → 0.2 per event
A shop with no events in that window has activity_score = 0. (Every shop in shops must appear, scored 0 if it has no qualifying events.)

Task: Produce a summary that compares new vs existing shops across three metrics. Return exactly three rows with these four columns:

metric_name — the metric label (text).
new_shops_value — the metric's value for new shops.
existing_shops_value — the metric's value for existing shops.
pct_diff — defined as 100 * (new_shops_value - existing_shops_value) / existing_shops_value; return NULL if existing_shops_value is 0.

The three metrics (one row each) are:

mean_activity_score — the average activity_score across all shops in the group.
median_activity_score — the median activity_score across all shops in the group, computed with an analytic/ordered-set function (PERCENTILE_CONT(0.5)).
share_of_active_shops — the fraction of shops in the group whose activity_score > 0.

Round every numeric value (new_shops_value, existing_shops_value, pct_diff) to 4 decimal places. Order the result rows alphabetically by metric_name.

Table `shops`

column	type	constraints
shop_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	shop_id
2025-05-25	1
2025-04-15	2
2025-05-30	3
2025-03-20	4
2025-05-05	5
2025-04-01	6

Table `listings`

column	type	constraints
listing_id	INT	PRIMARY KEY, NOT NULL
shop_id	INT	NOT NULL
created_at	DATE	NOT NULL
is_visible	SMALLINT	NOT NULL

Sample data:

created_at	is_visible	listing_id	shop_id
2025-05-26	1	101	1
2025-05-27	0	102	1
2025-05-28	0	103	1
2025-05-29	0	104	1
2025-04-20	1	201	2
2025-04-21	1	202	2
2025-04-22	1	203	2
2025-04-23	0	204	2
2025-04-24	0	205	2
2025-05-31	0	301	3
2025-05-31	0	302	3
2025-05-31	0	303	3
2025-05-31	0	304	3
2025-05-31	0	305	3
2025-03-25	1	401	4
2025-03-26	1	402	4
2025-05-06	1	501	5

Table `shop_events`

column	type	constraints
shop_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(32)	NOT NULL
cnt	INT	NOT NULL

Sample data:

cnt	event_date	event_type	shop_id
3	2025-05-26	product_created	1
5	2025-05-29	message_replied	1
10	2025-05-10	product_created	1
2	2025-05-20	order_placed	2
2	2025-05-22	listing_updated	2
4	2025-05-18	product_created	2
4	2025-05-31	listing_updated	3
1	2025-05-30	order_placed	3
1	2025-05-21	product_created	4
1	2025-05-25	message_replied	5
2	2025-05-17	product_created	5

Expected output

Three rows (one per metric), each comparing new vs existing shops with a pct_diff, ordered alphabetically by metric_name. New shops {1,3,5} have activity_scores {4.0, 5.0, 0.2}; existing shops {2,4,6} have {7.0, 1.0, 0.0}.

existing_shops_value	metric_name	new_shops_value	pct_diff
2.6667	mean_activity_score	3.0667	15.0000
1.0000	median_activity_score	4.0000	300.0000
0.6667	share_of_active_shops	1.0000	50.0000

Hints

Build a per-shop CTE first: LEFT JOIN shops to the windowed event scores and COALESCE missing scores to 0 so every shop is counted, then tag is_new.
Aggregate per is_new group with GROUP BY — use AVG for the mean, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY activity_score) for the median (do NOT add OVER; ordered-set aggregates can't be window functions in Postgres), and AVG of a 1/0 flag for the active share.
Pivot the two group rows into new-vs-existing columns with MAX(CASE WHEN is_new=...), then UNION ALL three SELECTs to get one row per metric; guard the division with NULLIF(existing,0).

Identify active but under-exposed new shops

Using the per-shop metrics from Question 2 (shop_id, is_new, visibility_rate, activity_score), find new shops that are active but have low visibility.

Definitions recap:

is_new = 1 if created_at >= '2025-05-02', else 0.
visibility_rate is the average is_visible across the shop's listings on 2025-06-01; shops with no listings have visibility_rate = NULL.
activity_score is computed over 2025-05-19 to 2025-06-01 as in Question 2.

Write a SQL query to:

Consider only new shops (is_new = 1).
Among them, filter to shops with visibility_rate < 0.5. (Assume that shops with visibility_rate = NULL are excluded from this filter.)
Return the top 5 such shops ordered by activity_score in descending order, breaking ties by shop_id ascending.
Output columns: shop_id, visibility_rate, activity_score.

In an interview setting, you should also be ready to explain:

Why visibility_rate is NULL (not 0) for shops with no listings, and why activity_score is 0 (not NULL) for shops with no events.
How you would prevent survivorship bias in a real analysis (e.g., by ensuring the denominator includes shops that churned before the snapshot date), but you do not need to express that explanation in SQL here.

Table `shops`

column	type	constraints
shop_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	shop_id
2025-05-25	1
2025-04-15	2
2025-05-30	3
2025-03-20	4
2025-05-05	5
2025-04-01	6

Table `listings`

column	type	constraints
listing_id	INT	PRIMARY KEY, NOT NULL
shop_id	INT	NOT NULL
created_at	DATE	NOT NULL
is_visible	TINYINT	NOT NULL

Sample data:

created_at	is_visible	listing_id	shop_id
2025-05-26	1	101	1
2025-05-27	0	102	1
2025-05-28	0	103	1
2025-05-29	0	104	1
2025-04-20	1	201	2
2025-04-21	1	202	2
2025-04-22	1	203	2
2025-04-23	0	204	2
2025-04-24	0	205	2
2025-05-31	0	301	3
2025-05-31	0	302	3
2025-05-31	0	303	3
2025-05-31	0	304	3
2025-05-31	0	305	3
2025-03-25	1	401	4
2025-03-26	1	402	4
2025-05-06	1	501	5

Table `shop_events`

column	type	constraints
shop_id	INT	NOT NULL
event_date	DATE	NOT NULL
event_type	VARCHAR(32)	NOT NULL
cnt	INT	NOT NULL

Sample data:

cnt	event_date	event_type	shop_id
3	2025-05-26	product_created	1
5	2025-05-29	message_replied	1
10	2025-05-10	product_created	1
2	2025-05-20	order_placed	2
2	2025-05-22	listing_updated	2
4	2025-05-18	product_created	2
4	2025-05-31	listing_updated	3
1	2025-05-30	order_placed	3
1	2025-05-21	product_created	4
1	2025-05-25	message_replied	5
2	2025-05-17	product_created	5

Expected output

Up to 5 rows for new shops (created_at >= '2025-05-02') whose visibility_rate is less than 0.5, ordered by activity_score descending. With the sample data and the metrics from Question 2:

New shops: 1, 3, 5.
Their visibility rates: shop1=0.25, shop3=0.0, shop5=1.0.
Their activity scores: shop1=4.0, shop3=5.0, shop5=0.2.

Filtering to visibility_rate < 0.5 gives shops 1 and 3. Ordering by activity_score DESC yields shop3 first, then shop1.

activity_score	shop_id	visibility_rate
5	3	0
4	1	0.25

Hints

Re-use (or rebuild) the per-shop metrics CTE from Question 2 to get is_new, visibility_rate, and activity_score in one place.
Filter on is_new = 1 and visibility_rate < 0.5, then order by activity_score DESC and limit to 5 rows.

Shop visibility rate on snapshot date

Table shops

Table listings

Table shop_events

Expected output

Hints

Per-shop new/existing flag, visibility, and activity score

Table shops

Table listings

Table shop_events

Expected output

Hints

Compare new vs existing shops using mean, median, and activity share

Table shops

Table listings

Table shop_events

Expected output

Hints

Identify active but under-exposed new shops

Table shops

Table listings

Table shop_events

Expected output

Hints

Table `shops`

Table `listings`

Table `shop_events`

Table `shops`

Table `listings`

Table `shop_events`

Table `shops`

Table `listings`

Table `shop_events`

Table `shops`

Table `listings`

Table `shop_events`