Calculate annual percentages and YoY by cohorts
Problem Description
This interview assesses your proficiency in data manipulation using both SQL and Python (Pandas). You are required to provide solutions for both parts, paying close attention to precise deduplication logic and appropriate denominator choices for calculations.
Schema
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 |
Part A: SQL - Annual Subscription Order Percentage
For the calendar year 2024, compute the percentage of distinct orders that contained at least one product with category = 'Subscription'. Each order should be counted at most once, even if it includes multiple subscription items. The output should be a single row with the column pct_subscription_2024, rounded to two decimal places. You may use CTEs, subqueries, or CASE WHEN statements.
Part B: SQL - Year-over-Year Order Change by Cohort
Define age group buckets as [18–29], [30–44], and [45+]. For each unique combination of (location, age_group) present in the users table, calculate the distinct order counts for 2023 and 2024. Also, compute the Year-over-Year (YoY) percent change using the formula: (orders_2024 - orders_2023) / NULLIF(orders_2023, 0). If orders_2023 is zero, return NULL for yoy_pct_change to avoid division by zero errors. Assume an order belongs to the age and location of its user at the time the order was placed. The output should include columns: location, age_group, orders_2023, orders_2024, and yoy_pct_change. You may use window functions or conditional aggregation.
Part C: Python (Pandas) - Unique Users for 'Pro' Products
You are provided with three Pandas DataFrames: df_orders (columns: order_id, user_id, order_date), df_products (columns: product_id, name, category), and df_users (columns: user_id, age, location), mirroring the SQL schema above. For the year 2024, compute the number of unique users who purchased any product whose name contains the substring 'Pro' (case-insensitive). The output should be a DataFrame with columns [location, age_group, unique_users], where age_group uses the same bins as defined in Part B. The result DataFrame must be sorted by unique_users in descending order, then by location in ascending order, ensuring stable sorting for ties. You are required to use merge, str.contains, groupby, and an aggregation function (nunique).
AI interview prep
Core dilemma
Balancing precision in complex data aggregation (distinct counts, conditional logic, year-over-year calculations) with efficient query/code execution across both SQL and Pandas, while meticulously handling edge cases like division by zero and sparse data.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Calculate annual percentages and YoY by cohorts
Problem Description
This interview assesses your proficiency in data manipulation using both SQL and Python (Pandas). You are required to provide solutions for both parts, paying close attention to precise deduplication logic and appropriate denominator choices for calculations.
Schema
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 |
Part A: SQL - Annual Subscription Order Percentage
For the calendar year 2024, compute the percentage of distinct orders that contained at least one product with category = 'Subscription'. Each order should be counted at most once, even if it includes multiple subscription items. The output should be a single row with the column pct_subscription_2024, rounded to two decimal places. You may use CTEs, subqueries, or CASE WHEN statements.
Part B: SQL - Year-over-Year Order Change by Cohort
Define age group buckets as [18–29], [30–44], and [45+]. For each unique combination of (location, age_group) present in the users table, calculate the distinct order counts for 2023 and 2024. Also, compute the Year-over-Year (YoY) percent change using the formula: (orders_2024 - orders_2023) / NULLIF(orders_2023, 0). If orders_2023 is zero, return NULL for yoy_pct_change to avoid division by zero errors. Assume an order belongs to the age and location of its user at the time the order was placed. The output should include columns: location, age_group, orders_2023, orders_2024, and yoy_pct_change. You may use window functions or conditional aggregation.
Part C: Python (Pandas) - Unique Users for 'Pro' Products
You are provided with three Pandas DataFrames: df_orders (columns: order_id, user_id, order_date), df_products (columns: product_id, name, category), and df_users (columns: user_id, age, location), mirroring the SQL schema above. For the year 2024, compute the number of unique users who purchased any product whose name contains the substring 'Pro' (case-insensitive). The output should be a DataFrame with columns [location, age_group, unique_users], where age_group uses the same bins as defined in Part B. The result DataFrame must be sorted by unique_users in descending order, then by location in ascending order, ensuring stable sorting for ties. You are required to use merge, str.contains, groupby, and an aggregation function (nunique).