01_sales_and_revenue.sql

/* ============================================================
   Sales and revenue analysis (Q1–Q4)
 
   Demand-side questions, built on order_items joined to orders.
   The data covers 21,629 orders from January 2020 to 30 July
   2024, and every date range here is literal — a window relative
   to today would return nothing.
 
   Sales value is gross ordered value: every order placed,
   whatever became of it. That suits a demand question, which
   asks what customers chose to buy rather than what was
   delivered. It totals 12,642,070.56, and 82% of that sits on
   completed orders. Queries measuring realised revenue filter to
   completed and say so in the column name.
============================================================ */

-- ============================================================
-- Q1 | Top ten products by sales value
-- Business question: which ten products generate the highest
--   total sales value, and what unit volume underpins them?
-- Approach: total the value of every sale by product and return
--   the ten highest.
-- ============================================================

SELECT 
    p.product_id, 
    p.product_name,
    SUM(oi.quantity)             AS units_sold,
    ROUND(SUM(oi.total_sale), 2) AS gross_order_value
FROM order_items AS oi
INNER JOIN products AS p
    ON p.product_id = oi.product_id
GROUP BY 1, 2
ORDER BY gross_order_value DESC
LIMIT 10;


-- ============================================================
-- Q2 | Revenue concentration by category
-- Business question: how is total sales value distributed across
--   product categories, and how reliant is the business on any
--   single one?
-- Approach: attribute every sale to its product's category and
--   express each category as a share of the total.
-- Note: electronics accounts for 89.7% of total sales value.
--   This concentration shapes much of the analysis that follows.
-- ============================================================

SELECT
    c.category_id,
    c.category_name,
    ROUND(SUM(oi.total_sale), 2) AS gross_order_value,
    ROUND(SUM(oi.total_sale) * 100.0                                                            -- A separate query, so GROUP BY does not apply to it: this is the
             / (SELECT SUM(total_sale) FROM order_items), 2) AS pct_of_gross_order_value        -- total across all categories, while SUM above is one category only.
FROM order_items AS oi
INNER JOIN products AS p 
    ON p.product_id = oi.product_id
INNER JOIN category AS c
    ON c.category_id = p.category_id
GROUP BY 1, 2
ORDER BY gross_order_value DESC;


-- ============================================================
-- Q3 | Average order value among repeat customers
-- Business question: among customers who purchase regularly,
--   what is a single order worth on average?
-- Approach: restrict to customers with more than five orders and
--   divide total spend by order count.
-- Note: customers are identified by ID rather than name, as
--   several hundred share a name with another customer.
-- ============================================================

SELECT 
    c.customer_id, 
    c.first_name||' '||c.last_name AS customer_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    ROUND(SUM(oi.total_sale), 2) AS gross_order_value,
    ROUND(SUM(oi.total_sale) / COUNT(DISTINCT o.order_id), 2) AS gross_avg_order_value
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
GROUP BY 1, 2 
    HAVING COUNT(DISTINCT o.order_id) > 5
ORDER BY gross_avg_order_value DESC;


-- ============================================================
-- Q4 | Monthly sales trend with month-over-month growth
-- Business question: how has total sales value moved from month
--   to month over the past year?
-- Approach: total sales by calendar month and compare each month
--   with the one preceding it.
-- Note: covers the twelve complete months ending July 2024, the
--   final month in the dataset.
-- ============================================================

-- last 1 year data
-- each month -- there total sale and thier prev mionth sale
-- window lag

WITH monthly AS (
    SELECT EXTRACT(YEAR  FROM o.order_date) AS year,
           EXTRACT(MONTH FROM o.order_date) AS month,
           ROUND(SUM(oi.total_sale), 2)     AS gross_order_value
    FROM orders AS o
    INNER JOIN order_items AS oi
        ON oi.order_id = o.order_id
    WHERE o.order_date >= DATE '2023-08-01'
      AND o.order_date <  DATE '2024-08-01'
    GROUP BY 1, 2
),
with_prior AS (
    SELECT year, month, gross_order_value,
        LAG(gross_order_value) OVER (
            ORDER BY year, month
        ) AS prior_month_value
    FROM monthly
)
SELECT year, month, gross_order_value, prior_month_value,
       ROUND((gross_order_value - prior_month_value) * 100.0 / prior_month_value, 2) AS mom_growth_pct
FROM with_prior
ORDER BY year, month;