Q01: Monthly Product Review Ratings (easy) Topic: Date Functions & Aggregations Amazon retail and catalog teams track customer review scores across calendar months to detect manufacturing defects, supplier shifts, and emerging quality trends early in a product's lifecycle. Problem Statement Calculate the average star rating for each product by calendar month. Return the month number (1–12), product_id, and avg_stars rounded to two decimal places. Sort by month ascending, then product_id descending. Input Schemas & Sample Fixtures Table: reviews review_iduser_idsubmit_dateproduct_idstars 61711232025-06-08 00:00:0050001478022652025-06-10 00:00:0069852452933622025-06-18 00:00:0050001363521922025-07-26 00:00:0069852345179812025-07-05 00:00:00698522 Expected Output monthproduct_idavg_stars 6698524.006500013.507698522.50 Solution Walkthrough Extract Calendar Month: Extract the month component from submit_date using EXTRACT(MONTH FROM submit_date) and cast the result to an integer to ensure standard numeric formatting.Compute Mean Star Rating: Calculate average product feedback with AVG(stars) and round to two decimal places using ROUND(..., 2).Aggregate and Sort: Group records by extracted month and product_id. Sort the results chronologically by month ascending, breaking ties by product_id descending. Verified SQL Solution SELECT CAST(EXTRACT(MONTH FROM submit_date) AS INT) AS month, product_id, ROUND(AVG(stars), 2) AS avg_stars FROM reviews WHERE submit_date >= '2025-01-01' AND submit_date < '2026-01-01' GROUP BY EXTRACT(MONTH FROM submit_date), product_id ORDER BY month ASC, product_id DESC; Practice Monthly Product Review Ratings in In-Browser Sandbox →
Q02: Department Headcount & NULL Handling (easy) Topic: Grouping & COALESCE Enterprise human resource rosters regularly contain records with unassigned department keys, such as contractors or newly onboarded personnel. Headcount queries must account for all active staff without silently dropping unassigned individuals during aggregation. Problem Statement Count total employees per department. Return department and the headcount as count. When an employee lacks a department (NULL), label the group 'Unassigned'. Sort by count descending, then department alphabetically. Input Schemas & Sample Fixtures Table: employees employee_idnamedepartmentsalary 101Liam ChenEngineering125000102Sophia PatelEngineering110000103Marcus VanceSales95000104Elena BrooksNULL88000105Noah KimSales92000 Expected Output departmentcount Engineering2Sales2Unassigned1 Solution Walkthrough Standardize Missing Values with COALESCE: Wrap the department attribute in COALESCE(department, 'Unassigned') in both SELECT and GROUP BY clauses to consolidate unassigned personnel into a clear audit category.Compute Departmental Headcounts: Count total employee records within each normalized department group using COUNT(*).Order by Headcount: Sort results using ORDER BY count DESC, department ASC so the largest divisions appear first, resolving headcount ties in alphabetical order. Verified SQL Solution SELECT COALESCE(department, 'Unassigned') AS department, COUNT(*) AS count FROM employees GROUP BY COALESCE(department, 'Unassigned') ORDER BY count DESC, department ASC; Practice Department Headcount & NULL Handling in In-Browser Sandbox →
Q03: Top Product Categories by Delivery Region (medium) Topic: Window Functions & DENSE_RANK Amazon supply chain leadership monitors regional demand patterns by ranking merchandise categories by net fulfilled revenue across distinct delivery territories. Problem Statement Calculate total fulfilled revenue (quantity * unit_price) per product category and delivery region during Q1 2025 (Jan 1 through Mar 31). Return region, category, total_revenue, and category_rank for the top two categories in each region, retaining ties without skipping rank numbers. Input Schemas & Sample Fixtures Table: orders order_idregioncategoryquantityunit_pricestatusorder_date 1North AmericaElectronics2450.00Completed2025-01-152North AmericaElectronics1300.00Completed2025-02-103North AmericaHome Goods560.00Completed2025-03-014North AmericaApparel240.00Completed2025-01-205EuropeElectronics3400.00Completed2025-02-14 Expected Output regioncategorytotal_revenuecategory_rank North AmericaElectronics1200.001North AmericaHome Goods300.002EuropeElectronics1200.001 Solution Walkthrough Aggregate Regional Q1 Revenue: Filter order records to completed transactions within Q1 2025. Group by region and product category, computing total fulfilled revenue as SUM(quantity * unit_price).Rank Categories per Region with DENSE_RANK: Assign category rankings within each region using DENSE_RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC). Using DENSE_RANK ensures revenue ties receive identical rank numbers without creating gaps in the numbering sequence.Filter Top Tiers and Sort: In the outer query, filter with WHERE category_rank <= 2 to isolate the top two product categories per territory. Sort by region DESC, category_rank DESC, category DESC. Verified SQL Solution WITH category_revenue AS ( SELECT region, category, SUM(quantity * unit_price) AS total_revenue FROM orders WHERE status = 'Completed' AND order_date >= DATE '2025-01-01' AND order_date < DATE '2025-04-01' GROUP BY region, category ), ranked_categories AS ( SELECT region, category, total_revenue, DENSE_RANK() OVER ( PARTITION BY region ORDER BY total_revenue DESC ) AS category_rank FROM category_revenue ) SELECT region, category, total_revenue, category_rank FROM ranked_categories WHERE category_rank <= 2 ORDER BY region DESC, category_rank DESC, category DESC; Practice Top Product Categories by Delivery Region in In-Browser Sandbox →
Q04: Month-Over-Month Subscriber Growth (medium) Topic: Window LAG & Growth Tracking Subscription analytics teams across Prime and AWS measure member retention by tracking monthly active subscriber counts, expansion velocity, and net contraction. Problem Statement Determine total active subscribers per calendar month for 2025, then calculate the percentage change from the preceding month. Return month, active_subscribers, and mom_churn_pct rounded to two decimal places, sorted chronologically. Input Schemas & Sample Fixtures Table: subscriptions user_idsubscription_monthstatus 1012025-01-01Active1022025-01-01Active1032025-01-01Active1012025-02-01Active1022025-02-01Active Expected Output monthactive_subscribersmom_churn_pct 13NULL22-33.33 Solution Walkthrough Tally Unique Monthly Subscribers: Filter for active subscriptions within 2025, group records by calendar month, and count distinct active subscribers using COUNT(DISTINCT user_id).Access Prior Month Totals with LAG: Retrieve the preceding month's subscriber count within the current row using the window function LAG(active_subscribers) OVER (ORDER BY month).Calculate Rate of Change: Compute relative growth using the formula ((current - prior) * 100.0) / prior, rounded to two decimal places. The initial cohort month naturally evaluates to NULL because no prior baseline exists. Verified SQL Solution WITH monthly_subscribers AS ( SELECT EXTRACT(MONTH FROM subscription_month) AS month, COUNT(DISTINCT user_id) AS active_subscribers FROM subscriptions WHERE status = 'Active' AND subscription_month >= '2025-01-01' AND subscription_month < '2026-01-01' GROUP BY EXTRACT(MONTH FROM subscription_month) ) SELECT month, active_subscribers, ROUND( ((active_subscribers - LAG(active_subscribers) OVER (ORDER BY month)) * 100.0) / LAG(active_subscribers) OVER (ORDER BY month), 2 ) AS mom_churn_pct FROM monthly_subscribers ORDER BY month ASC; Practice Month-Over-Month Subscriber Growth in In-Browser Sandbox →
Q05: Fulfillment Center Late Delivery Penalties (hard) Topic: Update Deduplication & Penalty Analytics Logistics fulfillment networks append real-time status updates as packages navigate sorting hubs. Assessing delivery service level agreements (SLAs) requires identifying each package's terminal status and calculating vendor penalties on late deliveries. Problem Statement Determine the final delivery status for every completed order using its latest timestamp. For orders delivered past the promised date in January 2025, apply a 10% penalty to order_value by warehouse and category. Return warehouse_name, category, late_order_count, late_penalty, and category_rank for the highest-penalty category per warehouse, filtering for categories with at least two delayed shipments. Input Schemas & Sample Fixtures Table: shipment_updates update_idorder_idshipment_statusupdated_atdelivered_date 111Shipped2025-01-08 09:00:00NULL121Delivered2025-01-12 10:00:002025-01-12212Shipped2025-01-11 09:00:00NULL222Delivered2025-01-15 10:00:002025-01-15 Table: orders order_idproduct_idwarehouse_idpromised_dateorder_valueorder_status 110112025-01-10100.00Completed210112025-01-13200.00Completed Table: products product_idcategory 101Electronics102Furniture Table: warehouses warehouse_idwarehouse_name 1North Fulfillment Center2South Fulfillment Center Expected Output warehouse_namecategorylate_order_countlate_penaltycategory_rank North Fulfillment CenterElectronics230.00001 Solution Walkthrough Isolate Terminal Shipment Status with ROW_NUMBER: Partition tracking updates by order_id and order by updated_at DESC, update_id DESC. Assigning ROW_NUMBER() guarantees that the most recent status record receives update_rank = 1.Identify Breached Delivery SLAs: Join orders, products, and warehouses against the terminal tracking event where update_rank = 1. Filter for completed orders delivered during January 2025 where delivered_date strictly exceeds promised_date.Aggregate Penalties by Warehouse and Category: Group delayed orders by warehouse and product category. Compute late_order_count with COUNT(*) and calculate total fees using CAST(SUM(order_value * 0.10) AS DECIMAL(10,4)), keeping groups with at least two delayed orders via HAVING COUNT(*) >= 2.Rank Categories by Financial Impact: Rank categories within each facility using DENSE_RANK() OVER (PARTITION BY warehouse_id ORDER BY late_penalty DESC). Filter for category_rank = 1 to output the highest-penalty merchandise category per warehouse. Verified SQL Solution WITH ranked_updates AS ( SELECT su.*, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY updated_at DESC, update_id DESC ) AS update_rank FROM shipment_updates AS su ), late_orders AS ( SELECT o.warehouse_id, p.category, o.order_value FROM orders AS o JOIN products AS p ON p.product_id = o.product_id JOIN ranked_updates AS ru ON ru.order_id = o.order_id AND ru.update_rank = 1 WHERE o.order_status = 'Completed' AND o.order_value IS NOT NULL AND ru.shipment_status = 'Delivered' AND ru.delivered_date >= DATE '2025-01-01' AND ru.delivered_date < DATE '2025-02-01' AND ru.delivered_date > o.promised_date ), category_impact AS ( SELECT warehouse_id, category, COUNT(*) AS late_order_count, CAST(SUM(order_value * 0.10) AS DECIMAL(10,4)) AS late_penalty FROM late_orders GROUP BY warehouse_id, category HAVING COUNT(*) >= 2 ), ranked_categories AS ( SELECT ci.warehouse_id, w.warehouse_name, ci.category, ci.late_order_count, ci.late_penalty, DENSE_RANK() OVER ( PARTITION BY ci.warehouse_id ORDER BY ci.late_penalty DESC ) AS category_rank FROM category_impact AS ci JOIN warehouses AS w ON w.warehouse_id = ci.warehouse_id ) SELECT warehouse_name, category, late_order_count, late_penalty, category_rank FROM ranked_categories WHERE category_rank = 1 ORDER BY warehouse_name DESC, category DESC; Practice Fulfillment Center Late Delivery Penalties in In-Browser Sandbox →