ROW_NUMBER vs RANK, frame clauses (ROWS vs RANGE), B-Tree composite index selectivity, and EXPLAIN ANALYZE.
Practical reference for analytical SQL and relational query tuning: window frame semantics, LAG/LEAD sessionization, composite B-Tree index column ordering (ESR rule), and join execution plans.
Higher selectivity (closer to 1.0) makes B-Tree index scans strongly preferred over sequential table scans.
When `ORDER BY` is specified inside `OVER(...)`, the default window frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Always specify `ROWS BETWEEN ...` when duplicate sort values exist and you want deterministic physical row counts.
| Function | Tie Behavior (Values: 100, 100, 90) | Primary Use Case |
|---|---|---|
| ROW_NUMBER() | 1, 2, 3 (no ties, arbitrary break) | Deduplication (`QUALIFY / WHERE rn = 1`) |
| RANK() | 1, 1, 3 (skips next rank after tie) | Competition leaderboards |
| DENSE_RANK() | 1, 1, 2 (no gaps in rank sequence) | Top-N distinct salary/revenue tiers |
| NTILE(k) | Splits partition into k equal buckets | Quartile / decile cohort segmentation |
WITH ranked_Metrics AS (
SELECT
department_id,
employee_id,
metric_date,
revenue,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY revenue DESC
) AS dept_rank,
AVG(revenue) OVER (
PARTITION BY department_id
ORDER BY metric_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg
FROM daily_sales
)
SELECT * FROM ranked_Metrics WHERE dept_rank <= 3;Order composite B-Tree index columns by **E**quality predicates first (`WHERE status = 'active'`), **S**ort columns second (`ORDER BY created_at DESC`), and **R**ange columns last (`WHERE amount > 100`). Use `INCLUDE (col1, col2)` for covering Index-Only Scans.
ROWS counts physical rows before/after the current row, whereas RANGE groups all peer rows that share the same ORDER BY value.