GPT-4's answer to the nested subtotals SQL question

nested_subtotals_gpt4.sql

-- Prompt:
-- 
-- I want help writing a SQL query. I have a database with the two tables:
--
-- CREATE TABLE order_items (
--   created_at timestamp,
--   sale_price decimal,
--   inventory_item_id integer
-- );
--
-- CREATE TABLE inventory_items (
--   id integer,
--   product_department varchar,
--   product_category varchar
-- );

-- The date range of data in the order_items table is from 1/1/2021 to 12/31/2022
-- I want to calculate year-over-year revenue and year-over-year revenue growth with these tables.
-- I also want to calculate for each product_department, year-over-year revenue and revenue growth, as well as the contribution % of that product_department to overall revenue growth.
-- I then want to drill in from product_department into product_category, and calculate year-over-year revenue and revenue growth, and the contribution of that product_category to the product_department's growth.

WITH department_revenue AS (
  SELECT 
    EXTRACT(YEAR FROM oi.created_at) AS year,
    ii.product_department,
    SUM(oi.sale_price) AS department_yearly_revenue
  FROM 
    read_parquet('order_items.parquet') AS oi
  JOIN 
    read_parquet('inventory_items.parquet') AS ii 
  ON 
    oi.inventory_item_id = ii.id
  GROUP BY 
    year, 
    ii.product_department
),
category_revenue AS (
  SELECT 
    EXTRACT(YEAR FROM oi.created_at) AS year,
    ii.product_department,
    ii.product_category,
    SUM(oi.sale_price) AS category_yearly_revenue
  FROM 
    read_parquet('order_items.parquet') AS oi
  JOIN 
    read_parquet('inventory_items.parquet') AS ii 
  ON 
    oi.inventory_item_id = ii.id
  GROUP BY 
    year, 
    ii.product_department,
    ii.product_category
),
overall_revenue AS (
  SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    SUM(sale_price) AS yearly_revenue
  FROM 
    read_parquet('order_items.parquet')
  GROUP BY 
    year
),
department_growth AS (
  SELECT 
    dr.year,
    dr.product_department,
    dr.department_yearly_revenue,
    dr.department_yearly_revenue - LAG(dr.department_yearly_revenue) OVER (PARTITION BY dr.product_department ORDER BY dr.year) AS department_yearly_growth
  FROM 
    department_revenue AS dr
),
category_growth AS (
  SELECT 
    cr.year,
    cr.product_department,
    cr.product_category,
    cr.category_yearly_revenue,
    cr.category_yearly_revenue - LAG(cr.category_yearly_revenue) OVER (PARTITION BY cr.product_department, cr.product_category ORDER BY cr.year) AS category_yearly_growth
  FROM 
    category_revenue AS cr
),
overall_growth AS (
  SELECT 
    year,
    yearly_revenue,
    yearly_revenue - LAG(yearly_revenue) OVER (ORDER BY year) AS yearly_growth
  FROM 
    overall_revenue
),
department_contribution AS (
  SELECT 
    dg.year,
    dg.product_department,
    dg.department_yearly_growth / og.yearly_growth * 100 AS department_growth_contribution_percentage
  FROM 
    department_growth AS dg
  JOIN 
    overall_growth AS og 
  ON 
    dg.year = og.year
),
category_contribution AS (
  SELECT 
    cg.year,
    cg.product_department,
    cg.product_category,
    cg.category_yearly_growth / dg.department_yearly_growth * 100 AS category_growth_contribution_percentage
  FROM 
    category_growth AS cg
  JOIN 
    department_growth AS dg 
  ON 
    cg.year = dg.year AND 
    cg.product_department = dg.product_department
)
SELECT 
  og.year, 
  og.yearly_revenue,
  og.yearly_growth,
  dg.product_department, 
  dg.department_yearly_revenue, 
  dg.department_yearly_growth, 
  dc.department_growth_contribution_percentage,
  cg.product_category,
  cg.category_yearly_revenue,
  cg.category_yearly_growth,
  cc.category_growth_contribution_percentage
FROM 
  overall_growth AS og
LEFT JOIN 
  department_growth AS dg 
ON 
  og.year = dg.year
LEFT JOIN 
  department_contribution AS dc 
ON 
  dg.year = dc.year AND 
  dg.product_department = dc.product_department
LEFT JOIN
  category_growth AS cg
ON
  dg.year = cg.year AND
  dg.product_department = cg.product_department
LEFT JOIN
  category_contribution AS cc
ON
  cg.year = cc.year AND
  cg.product_department = cc.product_department AND
  cg.product_category = cc.product_category
ORDER BY 
  og.year, 
  dg.product_department,
  cg.product_category;