Calculate annual percentages and YoY by cohorts

Answer both SQL and Python parts. Be precise about deduping and denominator choices.

SQL schema (sample rows):

orders
order_id | user_id | order_date
1 | 101 | 2023-01-10
2 | 102 | 2023-05-03
3 | 101 | 2024-02-12
4 | 103 | 2024-11-20
5 | 104 | 2024-12-28

order_items
order_id | product_id | qty
1 | 10 | 1
1 | 11 | 2
2 | 11 | 1
3 | 12 | 1
4 | 10 | 1
5 | 13 | 1

products
product_id | name | category
10 | Widget Pro | Subscription
11 | Widget | Standard
12 | Gadget Pro | Subscription
13 | Service | Standard

users
user_id | age | location
101 | 27 | NY
102 | 42 | CA
103 | 35 | NY
104 | 23 | TX

A) Three-table percentage (CTE/subquery/case-when allowed): For calendar year 2024, compute the percentage of distinct orders that contained at least one product with category = 'Subscription'. Count each order at most once even if it has multiple subscription items. Output a single row with pct_subscription_2024 rounded to two decimals.

B) YoY change by location and age group: Define age_group buckets as [18–29], [30–44], [45+]. For each (location, age_group) present in users, compute distinct-order counts in 2023 and 2024 and the YoY percent change = (orders_2024 - orders_2023) / NULLIF(orders_2023, 0). Return columns: location, age_group, orders_2023, orders_2024, yoy_pct_change. If orders_2023 = 0, return NULL for yoy_pct_change (avoid divide-by-zero). Assume an order belongs to the age/location of its user at order time. You may use window functions or conditional aggregation.

Python part (use pandas): You are given two DataFrames with the same data as above: df_orders(order_id, user_id, order_date), df_products(product_id, name, category), df_users(user_id, age, location). For year Y = 2024, compute the number of unique users who purchased any product whose name contains the substring 'Pro' (case-insensitive). Return a DataFrame with columns [location, age_group, unique_users] where age_group uses the same bins as in part B, sorted by unique_users descending, then location ascending. You must use merge, str.contains, groupby, and an aggregation (nunique), and ensure stable sorting for ties.

Percentage of 2024 orders containing a Subscription product

Using the tables below, for calendar year 2024 (2024-01-01 through 2024-12-31 inclusive), compute the percentage of distinct orders that contained at least one product with category = 'Subscription'.

Rules:

• Count each order at most once, even if it has multiple subscription items.
• The denominator is the total number of distinct orders in 2024.
• Output a single row with a single column pct_subscription_2024, rounded to two decimals.

You may use CTEs/subqueries and CASE WHEN logic.

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

order_date	order_id	user_id
2023-01-10	1	101
2023-05-03	2	102
2024-02-12	3	101
2024-11-20	4	103
2024-12-28	5	104

Table order_items

column	type	constraints
order_id	INT	NOT NULL
product_id	INT	NOT NULL
qty	INT	NOT NULL

Sample data:

order_id	product_id	qty
1	10	1
1	11	2
2	11	1
3	12	1
4	10	1
5	13	1

Table products

column	type	constraints
product_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	name	product_id
Subscription	Widget Pro	10
Standard	Widget	11
Subscription	Gadget Pro	12
Standard	Service	13

Expected output

Single-row output with the percent of distinct 2024 orders that include at least one Subscription product, rounded to 2 decimals.

pct_subscription_2024
66.67

Hints

1. First reduce to one row per order with a 0/1 flag for whether it contains any Subscription product.
2. Compute percent = 100 * (subscription_orders / total_orders) and round to 2 decimals.

YoY order count change by location and age group

Using the tables below, define age_group buckets based on users.age:

• '18-29' for ages 18 through 29
• '30-44' for ages 30 through 44
• '45+' for ages 45 and above

For each (location, age_group) combination present in the users table, compute:

• distinct-order count in calendar year 2023 (2023-01-01 through 2023-12-31)
• distinct-order count in calendar year 2024 (2024-01-01 through 2024-12-31)
• yoy_pct_change = (orders_2024 - orders_2023) / NULLIF(orders_2023, 0)

Rules:

• An order belongs to the age/location of its user.
• Return NULL for yoy_pct_change when orders_2023 = 0 (avoid divide-by-zero).

Return columns: location, age_group, orders_2023, orders_2024, yoy_pct_change.

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_date	DATE	NOT NULL

Sample data:

order_date	order_id	user_id
2023-01-10	1	101
2023-05-03	2	102
2024-02-12	3	101
2024-11-20	4	103
2024-12-28	5	104

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
age	INT	NOT NULL
location	VARCHAR(2)	NOT NULL

Sample data:

age	location	user_id
27	NY	101
42	CA	102
35	NY	103
23	TX	104

Expected output

One row per (location, age_group) present in users, showing distinct order counts for 2023 and 2024 and the YoY percent change (NULL when 2023 count is 0).

age_group	location	orders_2023	orders_2024	yoy_pct_change
30-44	CA	1	0	-1
18-29	NY	1	1	0
30-44	NY	0	1	NULL
18-29	TX	0	1	NULL

Hints

1. Start from users to ensure you return every (location, age_group) present, even if a group has zero orders in a year.
2. Use conditional COUNT(DISTINCT ...) for 2023 and 2024, then compute YoY with NULLIF to avoid divide-by-zero.