SQL GROUP BY — 40% Revenue Inflation from Missing Column

-- Sample table: orders
-- Columns: order_id, customer_id, category, amount, order_date

-- ── BASIC GROUP BY ──────────────────────────────────────────────
-- Goal: find total revenue and number of orders per product category

SELECT
    category,                        -- the grouping column — one row per unique value
    COUNT(order_id)   AS order_count, -- how many orders fell into this group
    SUM(amount)       AS total_revenue,-- aggregate: sum of all amounts in the group
    ROUND(AVG(amount), 2) AS avg_order_value -- aggregate: mean order size
FROM orders
GROUP BY category                    -- collapse every category into one summary row
ORDER BY total_revenue DESC;         -- show highest-revenue category first

-- ── GROUPING BY MULTIPLE COLUMNS ────────────────────────────────
-- Goal: break revenue down by category AND by the year of the order
-- This gives one row per (category, year) combination

SELECT
    category,
    EXTRACT(YEAR FROM order_date) AS order_year, -- derive year from the date column
    COUNT(order_id)               AS order_count,
    SUM(amount)                   AS total_revenue
FROM orders
GROUP BY
    category,               -- group first by category...
    EXTRACT(YEAR FROM order_date) -- ...then by year within each category
ORDER BY order_year, total_revenue DESC;

-- ── HAVING: filter groups by their aggregate value ──────────────
-- Goal: find product categories that have generated over $50,000
-- and had more than 100 individual orders

SELECT
    category,
    COUNT(order_id)  AS order_count,
    SUM(amount)      AS total_revenue
FROM orders
GROUP BY category
HAVING
    SUM(amount)     > 50000   -- only keep groups where the SUM clears this bar
    AND COUNT(order_id) > 100 -- AND the group contains more than 100 orders
ORDER BY total_revenue DESC;


-- ── WHERE + GROUP BY + HAVING together ──────────────────────────
-- Goal: same report, but exclude any orders placed before 2023
-- (WHERE trims raw rows BEFORE grouping — faster than filtering in HAVING)

SELECT
    category,
    COUNT(order_id)  AS order_count,
    SUM(amount)      AS total_revenue
FROM orders
WHERE order_date >= '2023-01-01'  -- row-level filter: remove old orders FIRST
GROUP BY category
HAVING
    SUM(amount)     > 50000        -- group-level filter: applied AFTER aggregation
    AND COUNT(order_id) > 100
ORDER BY total_revenue DESC;


-- ── HAVING without GROUP BY (edge case worth knowing) ────────────
-- HAVING can technically apply to the entire table as one group.
-- This returns the row only if the whole orders table has > 1000 rows.
-- Rarely useful, but interviewers love asking if it's valid SQL.

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 1000;

-- ── PATTERN 1: Top-N customers by total spend ───────────────────
-- Goal: find the 5 highest-spending customers in 2024

SELECT
    customer_id,
    COUNT(order_id)  AS total_orders,
    SUM(amount)      AS lifetime_spend
FROM orders
WHERE order_date >= '2024-01-01'   -- narrow to 2024 first (WHERE = pre-filter)
GROUP BY customer_id
HAVING SUM(amount) > 0             -- exclude any customer with $0 net (edge case)
ORDER BY lifetime_spend DESC
LIMIT 5;                           -- take only the top 5 after sorting


-- ── PATTERN 2: Duplicate detection ──────────────────────────────
-- Goal: find any email addresses that appear more than once in the
-- customers table — a sign of duplicate registrations

SELECT
    email,
    COUNT(*) AS registration_count   -- count how many rows share this email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1                  -- HAVING filters to only the duplicates
ORDER BY registration_count DESC;    -- worst offenders first


-- ── PATTERN 3: Engagement cohort — users who placed 3+ orders ───
-- Goal: identify loyal repeat customers for a marketing campaign

SELECT
    customer_id,
    COUNT(order_id)      AS order_count,
    MIN(order_date)      AS first_order_date,  -- when they became a customer
    MAX(order_date)      AS latest_order_date, -- most recent activity
    SUM(amount)          AS total_spend
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) >= 3          -- only customers with 3 or more orders
ORDER BY total_spend DESC;

-- ── MISTAKE 1: Using WHERE on an aggregate ───────────────────────
-- WRONG — this throws: "aggregate functions are not allowed in WHERE"
SELECT category, SUM(amount) AS total_revenue
FROM orders
WHERE SUM(amount) > 50000   -- ❌ WHERE runs before aggregation — SUM doesn't exist yet
GROUP BY category;

-- FIXED — move the aggregate condition to HAVING
SELECT category, SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 50000;  -- ✅ HAVING runs after aggregation — SUM exists here


-- ── MISTAKE 2: Referencing a SELECT alias in HAVING ──────────────
-- WRONG — most databases can't resolve the alias 'total_revenue' in HAVING
-- (PostgreSQL and SQL Server will error; MySQL may accept it as a non-standard extension)
SELECT
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING total_revenue > 50000;  -- ❌ alias not guaranteed to be visible in HAVING

-- FIXED — repeat the full aggregate expression in HAVING
SELECT
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 50000;   -- ✅ reference the expression, not the alias


-- ── MISTAKE 3: Forgetting that COUNT(*) includes NULLs ───────────
-- COUNT(*) counts all rows in the group, including rows with NULL values.
-- COUNT(column_name) counts only non-NULL values in that column.
-- These can give different numbers — and the difference matters.

SELECT
    category,
    COUNT(*)          AS all_rows,        -- counts every row, NULLs included
    COUNT(amount)     AS non_null_amounts -- counts only rows where amount IS NOT NULL
FROM orders
GROUP BY category;

-- If your 'amount' column has NULLs, these two columns will differ.
-- Use COUNT(column_name) when you specifically want to exclude NULLs from your count.