06_revenue_trends.sql

/* ============================================================
   Regional and year-over-year trends (Q6, Q19)

   Two questions that rank within groups. Q6 ranks categories
   inside each state, Q19 compares each product against itself
   across two years.

   Both use gross ordered value across all statuses, since both
   measure demand rather than realised revenue. Years are taken
   from the order date, and the data runs from 2020 to July 2024.
   ============================================================ */

-- ============================================================
-- Q6 | Best selling category in each state
-- Business question: which product category generates the most
--   order value in each state?
-- Approach: total value by state and category, rank within each
--   state, and keep the top category.
-- Note: electronics wins all 39 states. That follows from it
--   being 89.7% of all order value, so it reflects category mix
--   rather than regional preference.
-- ============================================================

WITH state_category AS (
    SELECT
        c.state,
        cat.category_name,
        ROUND(SUM(oi.total_sale), 2) AS gross_order_value,
        DENSE_RANK() OVER (
            PARTITION BY c.state
            ORDER BY ROUND(SUM(oi.total_sale), 2) DESC
        ) AS category_rank
    FROM orders AS o
    INNER JOIN customers AS c
        ON c.customer_id = o.customer_id
    INNER JOIN order_items AS oi
        ON oi.order_id = o.order_id
    INNER JOIN products AS p
        ON p.product_id = oi.product_id
    INNER JOIN category AS cat
        ON cat.category_id = p.category_id
    GROUP BY 1, 2
)

SELECT
    state,
    category_name,
    gross_order_value
FROM state_category
WHERE category_rank = 1
ORDER BY gross_order_value DESC;

/* 
   Why five tables for one ranking: the answer needs state, value
   and category, and those sit in three different places.
   order_items holds the value. customers holds the state, reached
   through orders. category holds the name, reached through products.

   orders and products contribute no column to the output. They are
   the only path to customers and category, so dropping them does
   not simplify the query, it breaks it. The test is not whether a 
   table gives you a column, it is whether removing it makes something 
   unreachable. 
*/

-- ============================================================
-- Q19 | Steepest revenue decline, 2022 to 2023
-- Business question: which products lost the largest share of
--   their order value between 2022 and 2023?
-- Approach: total each product's value in both years, then sort by 
-- percentage change ascending, so the steepest declines appear first.
-- Note: covers only products sold in both years. 282 of those
--   680 declined. A further 29 sold in 2022 and not in 2023,
--   a total loss, and are excluded by that framing.
-- ============================================================

WITH product_year AS (
    SELECT
        p.product_id,
        p.product_name,
        cat.category_name,
        ROUND(SUM(oi.total_sale)
              FILTER (WHERE EXTRACT(YEAR FROM o.order_date) = 2022), 2) AS gross_value_2022,
        ROUND(SUM(oi.total_sale)
              FILTER (WHERE EXTRACT(YEAR FROM o.order_date) = 2023), 2) AS gross_value_2023
    FROM orders AS o
    INNER JOIN order_items AS oi
        ON oi.order_id = o.order_id
    INNER JOIN products AS p
        ON p.product_id = oi.product_id
    INNER JOIN category AS cat
        ON cat.category_id = p.category_id
    WHERE o.order_date >= DATE '2022-01-01'
      AND o.order_date <  DATE '2024-01-01'
    GROUP BY 1, 2, 3
)

SELECT
    product_id,
    product_name,
    category_name,
    gross_value_2022,
    gross_value_2023,
    ROUND(gross_value_2023 - gross_value_2022, 2) AS gross_value_change,
    ROUND((gross_value_2023 - gross_value_2022) * 100.0 / gross_value_2022, 2) AS gross_value_change_pct
FROM product_year
WHERE gross_value_2022 > gross_value_2023
ORDER BY gross_value_change_pct ASC, product_id ASC
LIMIT 10; 


/* 
   Why the group by lists all three columns and not just product_id:
   every non aggregated column must have one provable value per
   group. Postgres infers that from a primary key, so grouping on
   product_id is enough to carry product_name, but category_name
   lives in another table whose key is not grouped, and Postgres
   will not reason across the join to prove it.

   Listing all three is safe because product_id is already in the
   group, so each group is one product with exactly one name and
   one category. The extra columns split nothing and merge nothing.
   This is the same principle behind the customer rule in Q3 and
   Q16: group on the id and carry the name, never group on the
   name alone. 
*/