SQL & Relational Database Indexing Cheatsheet

Practical SQL cheat sheet: joins, window functions (ROW_NUMBER, RANK), CTEs, and B-Tree indexing rules.

1. Window Functions for Advanced Analytics

Window functions compute values across rows without collapsing rows like GROUP BY does.

CODE SNIPPET

-- Calculate running totals and rankings per department
SELECT 
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank,
    SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) as running_payroll
FROM employees;

2. Common Table Expressions (CTE) & Deduplication

CTEs create readable, temporary result sets ideal for complex multi-stage mutations and deduplication.

CODE SNIPPET

WITH RankedDuplicates AS (
    SELECT 
        id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM RankedDuplicates WHERE rn > 1);

Need another cheat sheet?

We add new reference guides every week based on community requests.

Request a Cheatsheet →