GBase 8a OLAP Window Functions in Practice: Ranking, Running Totals, MoM, and Ratio Analysis

function_name() OVER (
    [PARTITION BY column]   -- window group
    [ORDER BY column]       -- ordering within window
    [ROWS|RANGE BETWEEN ... AND ...]  -- frame definition
)

SELECT
    dept_id,
    salesperson,
    sale_amount,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS dense_rnk
FROM sales
WHERE sale_date >= '2024-01-01';

WITH ranked AS (
    SELECT dept_id, salesperson, sale_amount,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS rn
    FROM sales WHERE YEAR(sale_date) = 2024
)
SELECT dept_id, salesperson, sale_amount
FROM ranked WHERE rn <= 3
ORDER BY dept_id, rn;

SELECT dept_id, salesperson, sale_amount,
       NTILE(4) OVER (PARTITION BY dept_id ORDER BY sale_amount DESC) AS quartile
FROM sales WHERE YEAR(sale_date) = 2024;

SELECT dept_id, sale_month, monthly_amount,
       SUM(monthly_amount) OVER (
           PARTITION BY dept_id ORDER BY sale_month
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS ytd_amount
FROM (
    SELECT dept_id,
           DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
           SUM(sale_amount) AS monthly_amount
    FROM sales
    GROUP BY dept_id, DATE_FORMAT(sale_date, '%Y-%m')
) t
ORDER BY dept_id, sale_month;

SELECT dept_id, sale_month, monthly_amount,
       ROUND(AVG(monthly_amount) OVER (
           PARTITION BY dept_id ORDER BY sale_month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3m
FROM monthly_sales
ORDER BY dept_id, sale_month;

-- Cumulative to current row
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- N preceding + current
ROWS BETWEEN N PRECEDING AND CURRENT ROW
-- Centered window
ROWS BETWEEN N PRECEDING AND N FOLLOWING
-- Entire partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

SELECT dept_id, sale_month, monthly_amount,
       LAG(monthly_amount, 1) OVER (PARTITION BY dept_id ORDER BY sale_month) AS prev_month_amount,
       ROUND(
           (monthly_amount - LAG(monthly_amount, 1) OVER (PARTITION BY dept_id ORDER BY sale_month))
           / NULLIF(LAG(monthly_amount, 1) OVER (PARTITION BY dept_id ORDER BY sale_month), 0) * 100, 2
       ) AS mom_growth_pct
FROM monthly_sales
ORDER BY dept_id, sale_month;

SELECT sale_month, monthly_amount,
       LEAD(monthly_amount, 1) OVER (ORDER BY sale_month) AS next_month_amount,
       LEAD(monthly_amount, 3) OVER (ORDER BY sale_month) AS next_quarter_amount
FROM monthly_sales;

SELECT sale_month, monthly_amount,
       LAG(monthly_amount, 12) OVER (PARTITION BY dept_id ORDER BY sale_month) AS same_month_last_year,
       ROUND(
           (monthly_amount - LAG(monthly_amount, 12) OVER (PARTITION BY dept_id ORDER BY sale_month))
           / NULLIF(LAG(monthly_amount, 12) OVER (PARTITION BY dept_id ORDER BY sale_month), 0) * 100, 2
       ) AS yoy_growth_pct
FROM monthly_sales
ORDER BY dept_id, sale_month;

SELECT dept_id, sale_month, monthly_amount,
       ROUND(monthly_amount * 100.0 / SUM(monthly_amount) OVER (PARTITION BY sale_month), 2) AS pct_of_total,
       ROUND(monthly_amount * 100.0 / SUM(monthly_amount) OVER (PARTITION BY dept_id), 2) AS pct_of_dept_annual
FROM monthly_sales
ORDER BY sale_month, dept_id;

SELECT COALESCE(dept_id, 'Total') AS dept,
       COALESCE(sale_month, 'Subtotal') AS month,
       SUM(sale_amount) AS total_amount
FROM sales WHERE YEAR(sale_date) = 2024
GROUP BY ROLLUP(dept_id, sale_month)
ORDER BY dept_id, sale_month;

SELECT COALESCE(CAST(dept_id AS CHAR), 'All') AS dept,
       COALESCE(region, 'All') AS region,
       SUM(sale_amount) AS total
FROM sales
GROUP BY CUBE(dept_id, region)
ORDER BY dept_id, region;

SELECT dept_id, sale_month, monthly_amount,
       FIRST_VALUE(monthly_amount) OVER (
           PARTITION BY dept_id ORDER BY monthly_amount ASC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS hist_min,
       FIRST_VALUE(monthly_amount) OVER (
           PARTITION BY dept_id ORDER BY monthly_amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS hist_max
FROM monthly_sales;