Grouping

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Multiple grouping columns
SELECT year, month, region, SUM(revenue)
FROM sales
GROUP BY year, month, region;

-- Group by expression
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Group by position (1-based — avoid for readability)
SELECT department, COUNT(*) FROM employees GROUP BY 1;

-- Only departments with more than 5 employees
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY cnt DESC;

-- Both WHERE (pre-group filter) and HAVING (post-group filter)
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'          -- exclude inactive before grouping
GROUP BY department
HAVING AVG(salary) > 75000;     -- keep only high-paying departments

-- HAVING with multiple conditions
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(total) > 1000 AND COUNT(*) >= 3;

SELECT
    name, salary, department,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

function_name(args) OVER (
    [PARTITION BY partition_col, ...]
    [ORDER BY order_col [ASC|DESC], ...]
    [ROWS|RANGE|GROUPS frame_spec]
)

SELECT name, salary,
    ROW_NUMBER()  OVER (ORDER BY salary DESC) AS row_num,
    RANK()        OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK()  OVER (ORDER BY salary DESC) AS dense_rank,
    NTILE(4)      OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- Top 1 per group (using ROW_NUMBER)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn = 1;

-- Top N per group
SELECT * FROM (
    SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
) t WHERE rk <= 3;

SELECT
    date,
    revenue,
    LAG(revenue, 1, 0)   OVER (ORDER BY date) AS prev_revenue,
    LEAD(revenue, 1, 0)  OVER (ORDER BY date) AS next_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day_change
FROM daily_revenue;

SELECT
    id, created_at,
    FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order_amount,
    LAST_VALUE(amount)  OVER (
        PARTITION BY user_id ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders;

-- Running total
SELECT id, amount,
    SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS running_total
FROM payments;

-- Cumulative total, partitioned by user
SELECT user_id, created_at, amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS user_running_total
FROM payments;

-- Moving average (last 7 days of rows)
SELECT date, revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        AS moving_7_avg
FROM daily_revenue;

-- Compare each row to the partition total
SELECT department, name, salary,
    SUM(salary) OVER (PARTITION BY department)              AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2)
        AS pct_of_dept
FROM employees;

ROWS | RANGE | GROUPS
BETWEEN
    UNBOUNDED PRECEDING
  | n PRECEDING
  | CURRENT ROW
  | n FOLLOWING
  | UNBOUNDED FOLLOWING
AND
    (same options)

-- Cumulative (default for ORDER BY, same as RANGE UNBOUNDED PRECEDING)
SUM(x) OVER (ORDER BY date)

-- Exactly the current row only
SUM(x) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND CURRENT ROW)

-- All rows in partition (whole partition)
SUM(x) OVER (PARTITION BY dept ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- Rolling 3-row centered window
AVG(x) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Rolling 7-day window by value range (not row count)
SUM(x) OVER (ORDER BY date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

SELECT
    name, salary,
    RANK()        OVER w AS rank,
    DENSE_RANK()  OVER w AS dense_rank,
    NTILE(5)      OVER w AS quintile
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

-- GROUP BY: one row per department
SELECT department, MAX(salary) FROM employees GROUP BY department;

-- PARTITION BY: each row kept, department max added
SELECT name, salary, department,
    MAX(salary) OVER (PARTITION BY department) AS dept_max
FROM employees;

-- ROLLUP: hierarchical subtotals (right to left)
SELECT region, country, SUM(revenue)
FROM sales
GROUP BY ROLLUP(region, country);
-- Rows: (region,country), (region,NULL=subtotal), (NULL,NULL=grand total)

-- CUBE: all combinations of subtotals
SELECT year, quarter, SUM(revenue)
FROM sales
GROUP BY CUBE(year, quarter);
-- Rows: (year,quarter), (year,NULL), (NULL,quarter), (NULL,NULL)

-- GROUPING SETS: specify exactly which groupings to produce
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
    (region, product),
    (region),
    (product),
    ()                  -- grand total
);

-- GROUPING(col): 1 if that col is an aggregated NULL (rollup placeholder)
SELECT
    GROUPING(region) AS is_region_total,
    region,
    SUM(revenue)
FROM sales
GROUP BY ROLLUP(region);