SQL Analysis — Sales Dashboard¶

Setup

Load superstore_clean.csv into a table called sales. Key columns: order_id, order_date, segment, region, category, sub_category, product_name, customer_id, customer_name, sales, quantity, discount, profit.

Q1 — Overall Revenue and Profit¶

SELECT
    COUNT(DISTINCT order_id)        AS total_orders,
    COUNT(DISTINCT customer_id)     AS total_customers,
    ROUND(SUM(sales), 0)            AS total_revenue,
    ROUND(SUM(profit), 0)           AS total_profit,
    ROUND(SUM(profit) / SUM(sales) * 100, 1)  AS margin_pct,
    ROUND(SUM(sales) / COUNT(DISTINCT order_id), 2)  AS avg_order_value
FROM sales;

Sample output:

total_orders	total_customers	total_revenue	total_profit	margin_pct	avg_order_value
5009	793	2297201	286397	12.5	458.61

Q2 — Monthly Revenue Trend with Growth¶

WITH monthly AS (
    SELECT
        order_month,
        SUM(sales)      AS revenue,
        SUM(profit)     AS profit
    FROM sales
    GROUP BY order_month
)
SELECT
    order_month,
    ROUND(revenue, 0)                                   AS revenue,
    ROUND(profit, 0)                                    AS profit,
    ROUND(LAG(revenue) OVER (ORDER BY order_month), 0)  AS prev_month,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY order_month))
        / LAG(revenue) OVER (ORDER BY order_month) * 100, 1
    )                                                   AS mom_growth_pct
FROM monthly
ORDER BY order_month;

Q3 — Revenue and Profit by Category¶

SELECT
    category,
    ROUND(SUM(sales), 0)            AS revenue,
    ROUND(SUM(profit), 0)           AS profit,
    ROUND(SUM(profit) / SUM(sales) * 100, 1)  AS margin_pct,
    ROUND(SUM(sales) / SUM(SUM(sales)) OVER () * 100, 1)  AS revenue_share_pct
FROM sales
GROUP BY category
ORDER BY revenue DESC;

Sample output:

category	revenue	profit	margin_pct	revenue_share_pct
Technology	836154	145455	17.4	36.4
Furniture	742000	18451	2.5	32.3
Office Supplies	719047	122491	17.0	31.3

The Furniture problem

Furniture is the #2 revenue category but generates only 6% of total profit. Its 2.5% margin is 7× lower than the other categories. This needs investigation in the dashboard.

Q4 — Worst Sub-Categories by Profit¶

SELECT
    sub_category,
    category,
    ROUND(SUM(sales), 0)            AS revenue,
    ROUND(SUM(profit), 0)           AS profit,
    ROUND(SUM(profit) / SUM(sales) * 100, 1)  AS margin_pct,
    ROUND(AVG(discount) * 100, 1)   AS avg_discount_pct
FROM sales
GROUP BY sub_category, category
ORDER BY profit ASC
LIMIT 5;

Sample output:

sub_category	category	revenue	profit	margin_pct	avg_discount_pct
Tables	Furniture	206966	-17725	-8.6	26.1
Bookcases	Furniture	114880	-3473	-3.0	21.1
Supplies	Office Supplies	46674	-1189	-2.5	7.6

Tables LOSE money — they generate $207k in revenue but a $17.7k loss, driven by a 26% average discount.

Q5 — Discount vs Profit Relationship¶

SELECT
    discount_band,
    COUNT(*)                        AS line_items,
    ROUND(SUM(sales), 0)            AS revenue,
    ROUND(SUM(profit), 0)           AS profit,
    ROUND(AVG(profit_margin) * 100, 1)  AS avg_margin_pct
FROM sales
GROUP BY discount_band
ORDER BY discount_band;

Sample output:

discount_band	line_items	revenue	profit	avg_margin_pct
No Discount	4798	1087908	320987	29.5
Low (0-20%)	3156	701872	95430	13.6
Medium (20-40%)	1241	287654	-28765	-10.0
High (40%+)	799	219767	-101255	-46.1

The killer insight

Profit turns negative at discounts above 20%. Orders discounted 40%+ generate a -46% margin — the company pays customers to take the product. Capping discounts at 20% would dramatically improve profitability.

Q6 — Top 10 Customers by Revenue¶

SELECT
    customer_name,
    segment,
    COUNT(DISTINCT order_id)        AS orders,
    ROUND(SUM(sales), 0)            AS revenue,
    ROUND(SUM(profit), 0)           AS profit
FROM sales
GROUP BY customer_name, segment
ORDER BY revenue DESC
LIMIT 10;

Q7 — Regional Performance¶

SELECT
    region,
    COUNT(DISTINCT order_id)        AS orders,
    ROUND(SUM(sales), 0)            AS revenue,
    ROUND(SUM(profit), 0)           AS profit,
    ROUND(SUM(profit) / SUM(sales) * 100, 1)  AS margin_pct
FROM sales
GROUP BY region
ORDER BY revenue DESC;

Sample output:

region	orders	revenue	profit	margin_pct
West	1611	725458	108418	14.9
East	1401	678781	91523	13.5
Central	1175	501240	39706	7.9
South	822	391722	46749	11.9

Central region has the lowest margin (7.9%) despite respectable revenue — worth investigating its discount practices.

Q8 — Segment Performance¶

SELECT
    segment,
    COUNT(DISTINCT customer_id)     AS customers,
    COUNT(DISTINCT order_id)        AS orders,
    ROUND(SUM(sales), 0)            AS revenue,
    ROUND(SUM(sales) / COUNT(DISTINCT customer_id), 0)  AS revenue_per_customer
FROM sales
GROUP BY segment
ORDER BY revenue DESC;

Q9 — Top Products per Category (Window Function)¶

WITH product_revenue AS (
    SELECT
        category,
        product_name,
        SUM(sales)  AS revenue,
        SUM(profit) AS profit,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(sales) DESC) AS rn
    FROM sales
    GROUP BY category, product_name
)
SELECT category, product_name, ROUND(revenue, 0) AS revenue, ROUND(profit, 0) AS profit
FROM product_revenue
WHERE rn <= 3
ORDER BY category, revenue DESC;

← Data Cleaning · Next: Dashboard Design →