Bard's answer to the nested subtotals SQL question

nested_subtotals_bard.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,
--   created_at timestamp
-- );
--
-- 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 order_items AS (
  SELECT
    created_at,
    sale_price,
    inventory_item_id
  FROM read_parquet('order_items.parquet')
),
inventory_items AS (
  SELECT
    id,
    product_department,
    product_category,
    created_at AS inventory_items_created_at
  FROM read_parquet('inventory_items.parquet')
)
SELECT
  year,
  product_department,
  product_category,
  revenue,
  revenue_growth,
  contribution_percent
FROM (
  SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    inventory_items.product_department,
    inventory_items.product_category,
    SUM(sale_price) AS revenue,
    (SUM(sale_price) - LAG(SUM(sale_price)) OVER (ORDER BY year, product_department, product_category)) / LAG(SUM(sale_price)) OVER (ORDER BY year, product_department, product_category) * 100 AS revenue_growth,
    SUM(sale_price) / SUM(revenue) OVER (PARTITION BY year) AS contribution_percent
  FROM order_items
  LEFT JOIN inventory_items ON inventory_items.id = order_items.inventory_item_id
  GROUP BY
    year,
    product_department,
    product_category
) AS year_over_year_revenue
ORDER BY
  year,
  product_department,
  product_category