Calculate annual percentages and YoY by cohorts

This exercise bundles three separate deliverables: two SQL queries and one pandas computation. Across all of them, be explicit about which rows you deduplicate and which set serves as the denominator of any ratio.

The SQL side uses four relations — orders(order_id, user_id, order_date), order_items(order_id, product_id, qty), products(product_id, name, category), and users(user_id, age, location). The pandas side receives three DataFrames holding the same rows: df_orders, df_products, and df_users. Their sample contents are shown below.

orders

order_items

products

users

Task A (SQL). Restrict to orders dated during calendar year 2024, from 2024-01-01 through 2024-12-31 inclusive. Determine what share of the distinct orders in that window included at least one product whose category equals 'Subscription'. An order is counted once only, however many subscription line items it happens to contain. The denominator is the number of distinct orders whose order_date lies inside 2024. Emit one row holding one column named pct_subscription_2024, written as a percentage rounded to two decimals. Common table expressions, derived tables, and CASE WHEN expressions are all allowed.

Task B (SQL). Split users.age into three buckets: '18-29' for ages 18 through 29, '30-44' for ages 30 through 44, and '45+' for ages 45 and above. For every (location, age_group) pair that occurs among users — including pairs whose members placed no orders at all — report distinct-order counts for 2023 and for 2024 together with the year-over-year ratio

$$yoy\_pct\_change = \frac{orders\_2024 - orders\_2023}{\mathrm{NULLIF}(orders\_2023,\ 0)}$$

An order takes on the location and age bucket of the user who placed it. Whenever orders_2023 is 0, the ratio must come back as NULL instead of raising an error. Return the columns location, age_group, orders_2023, orders_2024, yoy_pct_change. Window functions and conditional aggregation are both fair game.

Task C (pandas). Given df_orders(order_id, user_id, order_date), df_products(product_id, name, category), and df_users(user_id, age, location) as populated above, consider only year 2024. For each (location, age_group) bucket — using the same three age groups as Task B — count how many distinct users bought at least one product whose name contains the substring 'Pro', matching regardless of letter case. Produce a DataFrame with columns location, age_group, unique_users, ordered by unique_users descending and then by location ascending, with ties resolved deterministically. Your implementation should use merge, str.contains, groupby, and the nunique aggregation.

Example 1 (Task A):

Input: the tables listed above

Output: pct_subscription_2024 = 80.00

Explanation: five distinct orders fall in 2024, and four of them contain a Subscription product, giving $4/5 = 0.80$, reported as 80.00.

Example 2 (Task B):

Input: the tables listed above

Output:
 location | age_group | orders_2023 | orders_2024 | yoy_pct_change
 WA       | 18-29     | 1           | 3           | 2.0
 OR       | 30-44     | 1           | 1           | 0.0
 TX       | 45+       | 0           | 1           | NULL

Explanation: the WA/18-29 bucket moves from one order in 2023 to three in 2024 for a ratio of $(3-1)/1 = 2.0$, OR/30-44 is unchanged at $(1-1)/1 = 0.0$, and TX/45+ has no 2023 orders, so its ratio is NULL.

Example 3 (Task C):

Input: the tables listed above

Output:
 location | age_group | unique_users
 WA       | 18-29     | 2
 OR       | 30-44     | 1
 TX       | 45+       | 1

Explanation: in 2024 the subscription items named Nova Pro and RelayPRO were bought by users 203, 207 (both WA/18-29), 204 (TX/45+), and 205 (OR/30-44), and the two buckets tied at 1 are ordered alphabetically by location.

Constraints:

• Referential integrity holds throughout: every order_id in order_items exists in orders, every user_id in orders exists in users, and every product_id in order_items exists in products.
• order_date always holds a valid calendar date; 2023 means 2023-01-01 through 2023-12-31, and 2024 means 2024-01-01 through 2024-12-31.
• Task B must list every (location, age_group) combination that appears in users, even when a bucket has zero orders in one or both years.
• yoy_pct_change is NULL exactly when orders_2023 equals 0.
• Row order is unrestricted for Tasks A and B, but Task C must follow the stated ordering.

Schema

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  age INT,
  location TEXT
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name TEXT,
  category TEXT
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT REFERENCES users(user_id),
  order_date DATE
);

CREATE TABLE order_items (
  order_id INT REFERENCES orders(order_id),
  product_id INT REFERENCES products(product_id),
  qty INT
);