SQL Cheatsheet¶

Query Anatomy

SELECT [DISTINCT] {column_name}
FROM table_a AS a
JOIN table_b AS b on b.id = a.id
WHERE {condition}
GROUP BY {column_name, ...}
HAVING {condition}
ORDER BY {column_name} [ASC|DESC]
LIMIT {number_of_rows}
OFFSET {number_of_rows}

Key Functions¶

Aggregate Functions

COUNT() — count rows. SUM() — sum of values. AVG() — average of values. MIN() — minimum value. MAX() — maximum value.

COALESCE(val1, val2, ...) — return the first non-null value.

CASE WHEN condition THEN result [ELSE result] END — conditional logic.

CASE WHEN condition THEN result [WHEN condition THEN result ...] ELSE result END — multiple conditions.

SELECT
    CASE WHEN age < 18 THEN 'child'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
    END AS age_group
FROM users;

NULLIF(val1, val2) — return NULL if val1 equals val2, otherwise return val1.

CAST(val AS type) — convert a value to a different data type. CAST(val AS VARCHAR) — convert to string. CAST(val AS INT) — convert to integer. CAST(val AS DATE) — convert to date. CAST(val AS TIMESTAMP) — convert to timestamp. CAST(val AS BOOLEAN) — convert to boolean.

Joins¶

SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id;

INNER — the intersection (both sides match).
LEFT — everything on the left, matched right side.
RIGHT — everything on the right, matched left side.
FULL OUTER — everything from both sides.

Anti-join pattern — find rows in the left table with no match in the right:

SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

💡 Tip: use LEFT JOIN + WHERE right_key IS NULL instead of NOT IN — it handles NULL values correctly and is usually faster. Use LEFT/RIGHT joins only when the column names aren't the same or you care which side keeps all rows; otherwise they're interchangeable.

ROLLING WINDOW¶

OVER (
PARTITION BY column_name 
ORDER BY column_name 
ROWS BETWEEN ... PRECEDING AND CURRENT ROW)

ROWS BETWEEN 6 DAYS PRECEDING AND CURRENT ROW - window of 7 days

INTERVIEW PATTERNS¶

Selecting with group by condition

SELECT 
    user_id, 
    name
FROM users
where user_id in (
    SELECT user_id
    FROM orders
    GROUP BY user_id
    HAVING count(*) > 1
)

TOP N per group: Select the top 3 most recent users per user_id:

with x as (
    SELECT 
        user_id, 
        name,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
    FROM users
)
SELECT
    *
FROM x
WHERE rn <= 3;

ROW_NUMBER() - assign each row to a unique number within its partition, in this case its user_id group, ordered by created_at descending.