Analytical SQL for Business Intelligence

-- A plain GROUP BY gives you ONE level: a total per region.
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

-- That's fine, but a real report also wants a GRAND TOTAL row,
-- and often per-month totals too. Doing that by hand means
-- running three queries and gluing them together. There's a
-- better way: GROUPING SETS, ROLLUP and CUBE (next sections).

-- ROLLUP builds a HIERARCHY of subtotals from left to right:
--   (region, month)  -> every detail row
--   (region)         -> a subtotal per region   (month is NULL)
--   ()               -> one grand total          (both are NULL)
SELECT region, month, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, month)
ORDER BY region, month;

-- The NULLs in the region/month columns are not missing data —
-- they MARK the subtotal and grand-total rows. We'll label them
-- properly with GROUPING
...

-- GROUPING(col) returns 1 when this row is a subtotal for that
-- column (the value was "rolled up"), and 0 for a normal value.
-- Use it to print readable labels instead of bare NULLs.
SELECT
    CASE WHEN GROUPING(region) = 1 THEN 'All regions' ELSE region END AS region,
    CASE WHEN GROUPING(month)  = 1 THEN 'All months'  ELSE month  END AS month,
    SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, month)
ORDER BY GROUPING(region), region, GROUPING(month), month;

-- ORDER BY GROUP
...

-- GROUPING SETS lets you pick EXACTLY which totals you want —
-- no detail rows, just a region summary AND a month summary
-- AND a grand total, all in one pass over the table.
SELECT region, month, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS ((region), (month), ())
ORDER BY GROUPING(region), region, GROUPING(month), month;

-- () is the empty grouping set — that's the grand total.
-- ROLLUP(region, month) is just shorthand for the grouping sets
-- (region, month), (region) and ().

-- CUBE gives EVERY combination: detail rows, a subtotal per
-- region, a subtotal per month, AND the grand total. Use it when
-- a dashboard needs to slice the same numbers any direction.
SELECT region, month, SUM(amount) AS total
FROM sales
GROUP BY CUBE (region, month)
ORDER BY GROUPING(region), region, GROUPING(month), month;

-- CUBE(a, b) = grouping sets (a,b), (a), (b), ().
-- It can explode in size: n columns produce 2^n grouping sets,
-- so reach for ROLLUP unless you truly need every c
...

-- Averages hide outliers; PERCENTILES describe the whole spread.
-- WITHIN GROUP (ORDER BY ...) tells the function which column to
-- rank. PERCENTILE_CONT interpolates between rows (a "true"
-- median); PERCENTILE_DISC returns an actual value from the data.
SELECT
    ROUND(AVG(amount), 2)                                            AS mean,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)             AS median_cont,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount)             AS 
...

-- NTILE(n) splits ordered rows into n equal-sized buckets.
-- NTILE(4) = quartiles, NTILE(10) = deciles, NTILE(100) = percentiles.
SELECT region, month, amount,
       NTILE(2) OVER (ORDER BY amount) AS half      -- 1 = lower half, 2 = upper half
FROM sales
ORDER BY amount;

-- 6 rows sorted by amount split 3 + 3: the smallest three amounts
-- (100,200,300) land in bucket 1, the largest three (400,500,600)
-- in bucket 2. NTILE handles uneven counts by making the early
-- buckets one row bigger
...

-- RUNNING / CUMULATIVE totals: add up everything from the start of
-- the window down to the current row. Build a clean monthly series
-- first (a subquery), then accumulate it with a window SUM.
SELECT month, revenue,
    SUM(revenue) OVER (ORDER BY month
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS running_total
FROM (
    SELECT month, SUM(amount) AS revenue
    FROM sales
    GROUP BY month
) monthly
ORDER BY month;

-- Monthly revenue is Jan 500, Feb 
...

-- PERIOD-OVER-PERIOD: compare each month with the one before it.
-- LAG(x, 1) reaches back one row; the first row has no previous
-- value, so it returns NULL (here we'd show '-').
SELECT month, revenue,
    LAG(revenue, 1) OVER (ORDER BY month)                AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month)      AS change,
    ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month))
          / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100, 1)
        AS pct_change
FR
...

-- COHORT & FUNNEL queries reuse everything above — they are just
-- grouped aggregates with safe division. Two patterns to recognise:

-- 1) FUNNEL: one CASE per stage, summed, then ratios between stages.
--    (Imagine an 'events' table; here is the SHAPE of the query.)
SELECT
    COUNT(*)                                          AS sessions,
    SUM(CASE WHEN stage >= 1 THEN 1 ELSE 0 END)       AS viewed,
    SUM(CASE WHEN stage >= 2 THEN 1 ELSE 0 END)       AS added_to_cart,
    SUM(CASE WHE
...