Learn SQL: Free Interactive SQL CurriculumMaster SQL with practical, production-grade lessons covering basic querying, intermediate JOINs & CTEs, and advanced Window Functions.Curriculum Index

1. The SELECT Statement (basic · 5 min)

2. Filtering with WHERE (basic · 8 min)

3. Sorting and Limiting (basic · 5 min)

4. Handling NULLs and Data Types (basic · 10 min)

6. Query Execution Order (basic · 6 min)

8. String Manipulation & Pattern Matching (intermediate · 5 min)

9. CASE WHEN & Conditional Logic (intermediate · 5 min)

10. INNER & LEFT JOINs (intermediate · 6 min)

11. FULL OUTER, RIGHT & CROSS JOINs (intermediate · 5 min)

12. Set Operations: UNION, INTERSECT, EXCEPT (intermediate · 5 min)

13. Subqueries & Correlated Subqueries (intermediate · 6 min)

14. Common Table Expressions (CTEs) & Modular SQL (intermediate · 6 min)

15. Date & Time Intelligence (advanced · 6 min)

16. Window Functions: Introduction & The OVER Clause (advanced · 6 min)

17. Window Functions: ROW_NUMBER, RANK & DENSE_RANK (advanced · 7 min)

18. Window Functions: Lead, Lag & Offset Functions (advanced · 6 min)

19. Window Frames & Running Aggregates (advanced · 6 min)

20. Recursive CTEs & Hierarchies (advanced · 6 min)

21. Query Optimization, Indexing & EXPLAIN Plans (advanced · 6 min)

1. The SELECT Statement

Tier: basic | Estimated reading time: 5 min

A table is a warehouse shelf, and SELECT is the picking list. It reads rows from the source and chooses which labels and calculated values leave the shelf.

Overview

SELECT solves the basic question every query starts with: which values should the result show? In the logical SQL lifecycle it runs at Step 6, after FROM has found source rows and after WHERE, GROUP BY, HAVING, and window functions have done their work. A SELECT list can name columns, calculate an expression, assign a result name with AS, or show every column with *. The engine may scan the whole table when no usable filter limits the rows.

Core Syntax

SELECT
    column_name,
    expression AS result_name
FROM table_name;

Key Rules & Engine Behaviors

Lifecycle Phase: SELECT runs at Step 6. Aliases made here do not exist when WHERE runs at Step 2 or HAVING runs at Step 4, so those clauses cannot refer to them.
NULL & Edge Behavior: SELECT shows NULL as a missing value. An expression such as unit_price * quantity becomes NULL when either value is NULL.
Performance & Indexing: SELECT * can read wide rows and send unused data to the client. Name only needed columns; an index-only scan can help when an index holds every requested column and PostgreSQL can confirm row visibility.

Example 1: Choosing Columns and Naming a Calculation

Input (products table):

product_id	product_name	unit_price	stock_count
101	Mechanical Keyboard	89.00	14
102	Wireless Headphones	120.00	8
103	Desk Mat	25.00	31

Query:

SELECT
    product_name,
    unit_price * stock_count AS stock_value
FROM products;

Output:

product_name	stock_value
Mechanical Keyboard	1246.00
Wireless Headphones	960.00
Desk Mat	775.00

Explanation: FROM reads the three product rows at Step 1. SELECT runs at Step 6, keeps product_name, multiplies price by count for each row, and labels that result stock_value. No ORDER BY appears, so SQL does not promise this display order even if the table currently returns it this way.

Example 2: A NULL Inside a Calculated Column

Input (order_lines table):

line_id	product_name	unit_price	quantity
201	USB-C Dock	95.00	2
202	4K Monitor	320.00	NULL
203	Ergonomic Chair	210.00	1

Query:

SELECT
    product_name,
    unit_price * quantity AS line_total,
    COALESCE(quantity, 0) AS recorded_quantity
FROM order_lines;

Output:

product_name	line_total	recorded_quantity
USB-C Dock	190.00	2
4K Monitor	NULL	0
Ergonomic Chair	210.00	1

Explanation: Multiplication cannot invent a missing quantity, so the 4K Monitor total stays NULL. COALESCE supplies zero only for the separate displayed value. Replacing quantity inside the multiplication would change the meaning from unknown total to a known zero total; use that only when zero is the real business rule.

Tips & Tricks

Syntactic Sugar / Shorthand: AS is optional for simple aliases, but writing AS makes calculated names easier to spot.
Defensive SQL Pattern: Use COALESCE only when a replacement value is true for the data rule, not just to hide missing data.
Mental-Check Debugging Tactic: First run SELECT * on a small filtered sample, then add one calculated column at a time and check its inputs.

Common Pitfalls & Anti-Patterns

Using an Alias in WHERE: WHERE stock_value > 500 fails because stock_value appears at Step 6. Repeat the expression or place the SELECT in a subquery.
Depending on Table Order: A plain SELECT has no fixed order. Add ORDER BY when row sequence matters.

Key Takeaways

Column Choice: SELECT decides the result columns, not the rows read from the source.
Alias Scope: A SELECT alias is available to ORDER BY at Step 9, not to WHERE or HAVING.
Small Results: Choosing explicit columns prevents accidental data exposure and reduces data sent to the client.

Pro Tip: An index does not make SELECT * cheap by itself. Check EXPLAIN ANALYZE before adding indexes; a sequential scan is often faster when a query needs much of a small table.

2. Filtering with WHERE

Tier: basic | Estimated reading time: 8 min

Retrieving an entire table is rarely useful. The WHERE clause acts as a strict bouncer at a club, inspecting every single row and only letting it into the final result if it meets your exact conditions.

Overview

WHERE executes row-by-row. If a row evaluates to TRUE for the given condition, it is included in the result set; if FALSE, it is immediately discarded.

Key Concepts

Logical AND: Both conditions must be true.
Logical OR: At least one condition must be true.
Comparison Operators: Standard math operators apply (=, >, <, >=, <=, !=).

Example 1: Using AND

Input (products table):

id	name	category	price
1	Laptop	Electronics	1200
2	Desk	Furniture	300
3	Monitor	Electronics	400

Query:

SELECT name, price 
FROM products 
WHERE category = 'Electronics' AND price > 500;

Output:

name	price
Laptop	1200

Explanation: The database checks each row. The Laptop is Electronics AND > 500, so it is kept. The Monitor is Electronics, but its price is NOT > 500, so it is discarded. Both rules must be met.

Example 2: Using OR

Query:

SELECT name, category
FROM products 
WHERE category = 'Furniture' OR price > 1000;

Output:

name	category
Laptop	Electronics
Desk	Furniture

Explanation: The Laptop is kept because its price is > 1000 (even though it's not Furniture). The Desk is kept because it is Furniture (even though it's not > 1000). Only one rule needs to be true.

Pro Tip: When combining AND and OR, always use parentheses to explicitly group your logic. Without them, AND takes precedence over OR, which often leads to wildly incorrect results.

3. Sorting and Limiting

Tier: basic | Estimated reading time: 5 min

Unordered data is hard to read. SQL allows you to elegantly organize your results using ORDER BY and explicitly cap the volume of data returned using LIMIT.

Overview

ORDER BY acts as a sorter, arranging your output alphabetically, numerically, or chronologically. LIMIT acts as a cutoff switch, stopping the database once it finds a specific number of rows.

Key Concepts

ASC (Ascending): The default order (A to Z, 0 to 9, oldest to newest).
DESC (Descending): The reverse order (Z to A, 9 to 0, newest to oldest).
LIMIT: Caps the number of rows returned.

Example 1: ORDER BY (Descending)

Input (scores table):

player	score
Alex	450
Sam	900
Jordan	120

Query:

SELECT player, score 
FROM scores 
ORDER BY score DESC;

Output:

player	score
Sam	900
Alex	450
Jordan	120

Explanation: The database sorts the rows by the score column, starting from the highest number and going down to the lowest because of the DESC keyword.

Example 2: ORDER BY with LIMIT

Query:

SELECT player, score 
FROM scores 
ORDER BY score DESC 
LIMIT 1;

Output:

player	score
Sam	900

Explanation: The database first sorts all rows descending. Then, the LIMIT 1 clause chops off everything after the very first row, giving you the single highest scorer.

Pro Tip: Never rely on the database's default insertion order. If you need data in a specific order, you must use an ORDER BY clause.

4. Handling NULLs and Data Types

Tier: basic | Estimated reading time: 10 min

In SQL, missing data is represented by a special marker called NULL. Handling NULL values incorrectly is the #1 cause of bugs for SQL beginners.

Overview

NULL does not mean zero, and it does not mean an empty string. It literally means "unknown" or "missing". Because it is unknown, you cannot use standard equality operators (= or !=) to check for it.

Key Concepts

IS NULL / IS NOT NULL: The only safe way to check if a value is missing or present.
COALESCE(): A function that replaces a NULL with a fallback value.

Example 1: Using IS NULL

Input (contacts table):

name	phone
Sarah	555-0100
David	NULL

Query:

SELECT name
FROM contacts
WHERE phone IS NULL;

Output:

name
David

Explanation: The database looks specifically for rows where the phone data is completely missing.

Example 2: Using COALESCE

Query:

SELECT
    name,
    COALESCE(phone, 'No Phone') AS contact_info
FROM contacts;

Output:

name	contact_info
Sarah	555-0100
David	No Phone

Explanation: The COALESCE function looks at the phone column. For Sarah, it sees a real value and returns it. For David, it sees NULL, so it falls back to the second argument we provided: 'No Phone'.

Pro Tip: If you write WHERE phone_number = NULL, your query will execute successfully but return 0 rows. NULL = NULL evaluates to NULL, not TRUE.

6. Query Execution Order

Tier: basic | Estimated reading time: 6 min

Writing SQL is different from writing standard code. In most languages, code executes top-to-bottom. In SQL, the order you write the query is completely different from the order the database executes it.

Overview

If you don't understand execution order, you will encounter confusing errors like "column does not exist" when trying to filter by a newly created alias.

The Execution Order

When you submit a query, the database engine processes the clauses in this exact order:

FROM / JOIN: The database first figures out where the raw data is coming from and merges the tables.
WHERE: It then filters out any rows that don't match the base conditions.
GROUP BY: It takes the surviving rows and squashes them into aggregate groups.
HAVING: It filters the newly squashed aggregate groups.
SELECT: Finally, it computes which columns to actually return, and evaluates math/aliases.
ORDER BY: It sorts the final result set.
LIMIT: It chops off any excess rows.

Example: The Alias Trap

Incorrect Query:

SELECT name, price * 0.8 AS discounted_price 
FROM products 
WHERE discounted_price < 100; -- ERROR!

Why it fails: Because WHERE (Step 2) executes before SELECT (Step 5)! At the moment the database is running the WHERE clause, the discounted_price alias literally does not exist yet.

Correct Query:

SELECT name, price * 0.8 AS discounted_price 
FROM products 
WHERE (price * 0.8) < 100;

Pro Tip: ORDER BY (Step 6) executes after SELECT (Step 5). This means you can use aliases in your ORDER BY clause: ORDER BY discounted_price DESC.

8. String Manipulation & Pattern Matching

Tier: intermediate | Estimated reading time: 5 min

Text functions are measuring tools for labels. They trim, split, search, and reshape stored characters without changing the original row unless an update is issued.

Overview

String expressions usually run in SELECT at Step 6 or WHERE at Step 2. PostgreSQL offers LIKE for case-sensitive patterns, ILIKE for case-insensitive patterns, and functions such as TRIM, LOWER, UPPER, CONCAT, SPLIT_PART, and SUBSTRING. Pattern matching turns messy labels into usable filters, but a broad pattern can force a table scan.

Core Syntax

SELECT
    TRIM(customer_name) AS clean_name,
    SPLIT_PART(email, '@', 2) AS email_domain
FROM customers
WHERE product_name ILIKE '%headphones%';

Key Rules & Engine Behaviors

Lifecycle Phase: WHERE pattern checks run at Step 2. Aliases such as clean_name are created in SELECT at Step 6, so WHERE and HAVING at Step 4 cannot use them in the same query block.
NULL & Edge Behavior: Most string functions return NULL for a NULL input. LIKE and ILIKE also return UNKNOWN for NULL, which WHERE removes.
Performance & Indexing: A pattern beginning with % normally cannot use a standard B-tree index. A prefix search can use a suitable index; large free-text searches may need a trigram or full-text index.

Example 1: Matching Product Text Without Case Sensitivity

Input (products table):

product_id	product_name	category
801	Wireless Headphones	Audio
802	Headphone Stand	Accessories
803	Mechanical Keyboard	Peripherals
804	wireless charger	Power

Query:

SELECT
    product_name,
    category
FROM products
WHERE product_name ILIKE '%headphone%'
ORDER BY product_name ASC;

Output:

product_name	category
Headphone Stand	Accessories
Wireless Headphones	Audio

Explanation: WHERE checks every name at Step 2 and ignores letter case because ILIKE is used. The charger does not contain headphone, and the keyboard does not match either. SELECT shows the requested columns at Step 6; ORDER BY sorts those finished rows at Step 9.

Example 2: Splitting an Email Address

Input (subscribers table):

subscriber_id	customer_name	email
811	Liam Chen	liam.chen@northmail.com
812	Sophia Patel	sophia@northmail.com
813	Elena Brooks	elena@streamline.io
814	Marcus Vance	NULL

Query:

SELECT
    customer_name,
    SPLIT_PART(email, '@', 2) AS email_domain
FROM subscribers
WHERE email IS NOT NULL
ORDER BY email_domain ASC, customer_name ASC;

Output:

customer_name	email_domain
Liam Chen	northmail.com
Sophia Patel	northmail.com
Elena Brooks	streamline.io

Explanation: WHERE removes the NULL email before SPLIT_PART runs. SPLIT_PART takes text after the first @ sign. email_domain is an alias made at Step 6, so ORDER BY may use it; placing email_domain in WHERE would fail because that filter runs earlier.

Tips & Tricks

Syntactic Sugar / Shorthand: CONCAT_WS(' ', first_name, last_name) joins non-NULL parts with one separator.
Defensive SQL Pattern: Apply TRIM before matching imported text when trailing spaces are possible.
Mental-Check Debugging Tactic: Select the original value beside the transformed value for a small sample before filtering on it.

Common Pitfalls & Anti-Patterns

Using LIKE for Case-Insensitive Search: LIKE is case-sensitive in PostgreSQL. Use ILIKE when the comparison should ignore case.
Leading Wildcards on Busy Tables: ILIKE '%term%' can scan every row. Measure it and add the appropriate search index only when needed.

Key Takeaways

Text at Two Stages: WHERE filters text early; SELECT formats text late.
Missing Text: NULL needs IS NULL or IS NOT NULL, not an ordinary comparison.
Pattern Cost: Prefix patterns and contains patterns have different index options.

Pro Tip: If matching should ignore case often, consider a stored normalized value or an expression index on LOWER(column). Keep the query expression identical to the index expression.

9. CASE WHEN & Conditional Logic

Tier: intermediate | Estimated reading time: 5 min

CASE is a switchboard inside a row. It tests conditions from top to bottom and returns the value from the first branch that matches.

Overview

CASE adds conditional logic to SELECT expressions, aggregates, ORDER BY, and other SQL expressions. In a SELECT list it runs at Step 6; a CASE used inside WHERE runs at Step 2. It is useful for labels, buckets, conditional counts, and safe fallbacks. Branch order matters when conditions overlap.

Core Syntax

SELECT
    amount,
    CASE
        WHEN amount >= 100.00 THEN 'high'
        WHEN amount >= 50.00 THEN 'medium'
        ELSE 'low'
    END AS spend_band
FROM orders;

Key Rules & Engine Behaviors

Lifecycle Phase: A CASE in SELECT runs at Step 6, so its alias is out of scope for WHERE at Step 2 and HAVING at Step 4. ORDER BY at Step 9 may use the alias.
NULL & Edge Behavior: A WHEN condition that is UNKNOWN does not match. Add WHEN amount IS NULL when missing values need their own label; otherwise ELSE handles them.
Performance & Indexing: A CASE around an indexed column in WHERE can make filtering harder to index. Write direct, simple predicates when they express the same rule.

Example 1: Assigning Spend Bands

Input (orders table):

order_id	customer_name	amount
901	Maya Lin	130.00
902	Jordan Miller	75.00
903	Chloe Bennett	20.00
904	Julian Ward	100.00

Query:

SELECT
    customer_name,
    amount,
    CASE
        WHEN amount >= 100.00 THEN 'high'
        WHEN amount >= 50.00 THEN 'medium'
        ELSE 'low'
    END AS spend_band
FROM orders
ORDER BY amount DESC;

Output:

customer_name	amount	spend_band
Maya Lin	130.00	high
Julian Ward	100.00	high
Jordan Miller	75.00	medium
Chloe Bennett	20.00	low

Explanation: SELECT checks high before medium. Julian Ward matches the first branch because 100.00 is at least 100.00; the lower branch is never reached. Reversing the two tests would label 100.00 as medium, since CASE stops at its first TRUE branch.

Example 2: Counting Conditions Without Dropping Rows

Input (payments table):

payment_id	payment_status	amount
911	paid	50.00
912	failed	30.00
913	paid	80.00
914	pending	25.00

Query:

SELECT
    COUNT(*) AS payment_count,
    SUM(CASE WHEN payment_status = 'paid' THEN amount ELSE 0 END) AS paid_amount,
    COUNT(*) FILTER (WHERE payment_status = 'failed') AS failed_count
FROM payments;

Output:

payment_count	paid_amount	failed_count
4	130.00	1

Explanation: There is no GROUP BY, so all rows form one group at Step 3. CASE contributes amounts only for paid rows and zero for the others. FILTER counts only failed rows while COUNT(*) still sees every row. WHERE would remove rows completely, which is not the intended report.

Tips & Tricks

Syntactic Sugar / Shorthand: FILTER (WHERE condition) is often clearer than SUM(CASE ...) for conditional counts.
Defensive SQL Pattern: Put the narrowest overlapping CASE condition first and include ELSE with an intentional fallback.
Mental-Check Debugging Tactic: Select the CASE result next to its inputs and inspect each boundary value.

Common Pitfalls & Anti-Patterns

Missing an ELSE: CASE returns NULL when no WHEN matches and no ELSE exists. That may be correct, but make it deliberate.
Using a CASE Alias in WHERE: Filter with the original CASE expression or an outer query because the alias is made later.

Key Takeaways

First Match Wins: CASE evaluates branches in written order.
Conditional Aggregates: CASE and FILTER calculate several measures from one row set.
Clear Boundaries: Test exactly at each threshold.

Pro Tip: Keep business thresholds in one visible CASE expression instead of repeating slightly different conditions across reports. When the rule changes, one review catches every band.

10. INNER & LEFT JOINs

Tier: intermediate | Estimated reading time: 6 min

A join matches two stacks of cards using a shared label. INNER JOIN keeps only pairs that match; LEFT JOIN keeps every card from the left stack even when the right label is absent.

Overview

FROM and JOINs run at Step 1, before WHERE filters rows. INNER JOIN combines matching rows. LEFT JOIN preserves each left-table row and fills right-table columns with NULL when no match exists. A join condition should state the real key relationship; a missing or loose condition can multiply rows and corrupt totals.

Core Syntax

SELECT
    o.order_id,
    c.customer_name
FROM orders AS o
LEFT JOIN customers AS c
    ON c.customer_id = o.customer_id;

Key Rules & Engine Behaviors

Lifecycle Phase: JOIN runs at Step 1. WHERE runs at Step 2, so a WHERE condition on a right-table column can remove NULL-extended LEFT JOIN rows. SELECT aliases appear at Step 6 and are unavailable to WHERE or HAVING at Step 4.
NULL & Edge Behavior: NULL = NULL is UNKNOWN, so NULL keys do not match ordinary equality joins. A LEFT JOIN returns NULL right-side fields for unmatched left rows.
Performance & Indexing: PostgreSQL can use nested-loop, hash, or merge joins. Index join keys when selective lookups are common; check data types match so casts do not block an index.

Example 1: Matching Existing Customers

Input (orders table):

order_id	customer_id	amount
1001	1	120.00
1002	2	75.00
1003	3	90.00

Input (customers table):

customer_id	customer_name
1	Liam Chen
2	Sophia Patel

Query:

SELECT
    o.order_id,
    c.customer_name,
    o.amount
FROM orders AS o
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id
ORDER BY o.order_id ASC;

Output:

order_id	customer_name	amount
1001	Liam Chen	120.00
1002	Sophia Patel	75.00

Explanation: At Step 1, orders 1001 and 1002 find matching customer keys. Order 1003 has no customer row, so INNER JOIN drops it. SELECT shows columns from the matched pairs and ORDER BY sorts their finished output.

Example 2: Preserving Unmatched Orders

Input (orders table):

order_id	customer_id	status
1011	1	completed
1012	3	pending
1013	NULL	pending

Input (customers table):

customer_id	customer_name
1	Elena Brooks
2	Marcus Vance

Query:

SELECT
    o.order_id,
    o.status,
    c.customer_name
FROM orders AS o
LEFT JOIN customers AS c
    ON c.customer_id = o.customer_id
ORDER BY o.order_id ASC;

Output:

order_id	status	customer_name
1011	completed	Elena Brooks
1012	pending	NULL
1013	pending	NULL

Explanation: LEFT JOIN retains all three order rows. Customer 3 has no match, and the NULL key cannot match customer 2, so both get NULL customer_name values. Adding WHERE c.customer_name IS NOT NULL would turn this result into an inner-join-like result.

Tips & Tricks

Syntactic Sugar / Shorthand: Short table aliases make join conditions readable when used consistently.
Defensive SQL Pattern: Put right-table conditions that must preserve unmatched left rows in the ON clause, not WHERE.
Mental-Check Debugging Tactic: Count rows before and after a join; an unexpected increase often reveals duplicate keys.

Common Pitfalls & Anti-Patterns

Filtering the Right Table in WHERE: WHERE c.status = 'active' removes unmatched LEFT JOIN rows. Put the condition in ON when they should remain.
Joining on a Non-Unique Key: A customer with two matching rows duplicates each matching order. Enforce or verify key uniqueness.

Key Takeaways

Join Type Matters: INNER JOIN removes nonmatches; LEFT JOIN preserves the left side.
Join Before Filter: WHERE sees the rows produced by the join.
Row Multiplication: One-to-many joins increase row count by design; aggregate only after confirming the relationship.

Pro Tip: A foreign key protects referential data quality but does not create an index automatically on the referencing column. Add one when joins or deletes regularly search that column.

11. FULL OUTER, RIGHT & CROSS JOINs

Tier: intermediate | Estimated reading time: 5 min

Outer joins keep cards that did not find a partner. CROSS JOIN skips matching entirely and lays every left card beside every right card.

Overview

All joins run in FROM at Step 1. RIGHT JOIN preserves right-table rows, though a reversed LEFT JOIN often reads more naturally. FULL OUTER JOIN preserves rows from both tables, filling the absent side with NULL. CROSS JOIN creates every possible pair and should be used only when that grid is the intended result.

Core Syntax

SELECT
    a.item_id,
    b.location_name
FROM inventory AS a
FULL OUTER JOIN locations AS b
    ON b.location_id = a.location_id;

Key Rules & Engine Behaviors

Lifecycle Phase: These joins produce rows at Step 1. WHERE runs at Step 2 and can remove unmatched NULL-extended rows, while SELECT aliases do not exist until Step 6.
NULL & Edge Behavior: FULL OUTER JOIN shows NULL for fields from an absent partner. CROSS JOIN with an empty input returns zero rows because there are no pairs.
Performance & Indexing: FULL OUTER JOIN can be expensive for large sets. CROSS JOIN grows as left row count times right row count; filter either side before joining when a smaller grid is sufficient.

Example 1: Finding Inventory and Location Gaps

Input (inventory table):

item_id	product_name	location_id
1101	USB-C Dock	1
1102	Desk Mat	3

Input (locations table):

location_id	location_name
1	North Depot
2	South Depot

Query:

SELECT
    i.product_name,
    l.location_name
FROM inventory AS i
FULL OUTER JOIN locations AS l
    ON l.location_id = i.location_id
ORDER BY l.location_name ASC NULLS LAST, i.product_name ASC NULLS LAST;

Output:

product_name	location_name
USB-C Dock	North Depot
NULL	South Depot
Desk Mat	NULL

Explanation: The North Depot row matches USB-C Dock. South Depot has no inventory item, so its product is NULL. Desk Mat points at location 3, which has no location row, so its location is NULL. FULL OUTER JOIN preserves all three facts.

Example 2: Generating a Small Planning Grid

Input (days table):

day_name
Monday
Tuesday

Input (shifts table):

shift_name
Morning
Evening

Query:

SELECT
    d.day_name,
    s.shift_name
FROM days AS d
CROSS JOIN shifts AS s
ORDER BY d.day_name ASC, s.shift_name ASC;

Output:

day_name	shift_name
Monday	Evening
Monday	Morning
Tuesday	Evening
Tuesday	Morning

Explanation: CROSS JOIN creates two days times two shifts, producing four rows. No ON clause appears because no values are compared. This is useful for a deliberate schedule grid; it is a serious bug when an intended join condition was forgotten.

Tips & Tricks

Syntactic Sugar / Shorthand: Prefer LEFT JOIN by swapping table order instead of writing RIGHT JOIN when it improves the reading direction.
Defensive SQL Pattern: Add a count check before and after a CROSS JOIN and calculate the expected product of row counts.
Mental-Check Debugging Tactic: Select the join keys from both sides and inspect NULL keys before adding more columns.

Common Pitfalls & Anti-Patterns

Accidental CROSS JOIN: A missing ON clause can create millions of pairs. Use explicit CROSS JOIN only when every pair is wanted.
WHERE on an Outer Side: A WHERE condition on a NULL-extended side can discard the unmatched rows the outer join was meant to keep.

Key Takeaways

Full Coverage: FULL OUTER JOIN reports matches and both kinds of mismatch.
Direction Is Readability: RIGHT JOIN is equivalent to a reversed LEFT JOIN.
Pair Growth: CROSS JOIN size multiplies input row counts.

Pro Tip: For reconciliation reports, include each table's key alongside display labels in a FULL OUTER JOIN. It tells you which source lacks a match without guessing from a NULL label.

12. Set Operations: UNION, INTERSECT, EXCEPT

Tier: intermediate | Estimated reading time: 5 min

Set operations stack finished result lists on a workbench. UNION combines lists, INTERSECT keeps shared rows, and EXCEPT removes rows found in the second list.

Overview

Each SELECT branch completes before a set operation combines its results at Step 8. UNION removes duplicate rows, while UNION ALL keeps every row and is usually cheaper. INTERSECT returns rows common to both results; EXCEPT returns rows in the first result but not the second. Every branch needs the same number of columns in compatible positions.

Core Syntax

SELECT customer_name
FROM newsletter_subscribers
UNION ALL
SELECT customer_name
FROM event_attendees;

Key Rules & Engine Behaviors

Lifecycle Phase: Set operations run at Step 8, after each branch has reached SELECT at Step 6 and DISTINCT at Step 7 where needed. A final ORDER BY runs at Step 9 and refers to the combined output.
NULL & Edge Behavior: UNION treats matching NULL values as duplicates for set removal. INTERSECT can return a NULL row when both inputs contain it.
Performance & Indexing: UNION, INTERSECT, and EXCEPT need duplicate comparison work, often by sorting or hashing. Use UNION ALL when duplicates are meaningful or later logic handles them.

Example 1: Keeping All Source Rows with UNION ALL

Input (newsletter_subscribers table):

customer_name
Liam Chen
Sophia Patel

Input (event_attendees table):

customer_name
Sophia Patel
Elena Brooks

Query:

SELECT
    customer_name
FROM newsletter_subscribers
UNION ALL
SELECT
    customer_name
FROM event_attendees
ORDER BY customer_name ASC;

Output:

customer_name
Elena Brooks
Liam Chen
Sophia Patel
Sophia Patel

Explanation: Each branch reads and selects its own rows. UNION ALL combines the two results at Step 8 without removing the repeated Sophia Patel row. The final ORDER BY sorts the four combined rows; it does not sort each branch separately.

Example 2: Finding Customers in One List but Not Another

Input (active_customers table):

customer_name
Liam Chen
Marcus Vance
Sophia Patel

Input (recent_buyers table):

customer_name
Marcus Vance
Sophia Patel

Query:

SELECT
    customer_name
FROM active_customers
EXCEPT
SELECT
    customer_name
FROM recent_buyers
ORDER BY customer_name ASC;

Output:

customer_name
Liam Chen

Explanation: EXCEPT compares the completed first result to the completed second result and removes names present in both. Marcus Vance and Sophia Patel are removed. Unlike a join, EXCEPT compares the chosen output columns, so include keys rather than display names when names are not unique.

Tips & Tricks

Syntactic Sugar / Shorthand: Add a literal source column in each branch, such as 'newsletter' AS source_name, when tracking where rows came from.
Defensive SQL Pattern: Select matching columns in the same order and give the first branch clear aliases, because it supplies the combined output names.
Mental-Check Debugging Tactic: Run each branch alone before combining them, then compare row counts with UNION and UNION ALL.

Common Pitfalls & Anti-Patterns

Using UNION When Duplicates Matter: UNION silently removes duplicates. Use UNION ALL for events, transactions, and other repeated facts.
Mismatched Column Meaning: Two text columns may be type-compatible but mean different things. Match the data meaning, not just the type.

Key Takeaways

Branch First: Each SELECT creates a result before Step 8 combines it.
Duplicate Choice: UNION removes duplicates; UNION ALL preserves them.
Column Contract: Branches need compatible columns in matching positions.

Pro Tip: Use UNION ALL first during debugging. If the result looks right, change to UNION only when deduplication is an actual business rule and inspect what disappeared.

13. Subqueries & Correlated Subqueries

Tier: intermediate | Estimated reading time: 6 min

A subquery is a smaller lookup sheet inside a larger query. A correlated subquery reads a value from the current outer row, so its answer can differ for every row.

Overview

Subqueries can return one value, a list of values, or a derived table. Use scalar subqueries for one result, EXISTS for checking whether a related row exists, and NOT EXISTS for checking that none exists. PostgreSQL can often rewrite a subquery into a join, but write the form that states the intended rule clearly.

Core Syntax

SELECT
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

Key Rules & Engine Behaviors

Lifecycle Phase: A subquery has its own lifecycle. In this example, the outer WHERE runs at Step 2 and asks the inner query whether a related row exists; outer SELECT aliases at Step 6 are unavailable to either WHERE or HAVING at Step 4.
NULL & Edge Behavior: EXISTS returns TRUE or FALSE based on row presence and does not care whether selected inner values are NULL. NOT IN behaves differently when its subquery contains NULL; prefer NOT EXISTS for anti-matching.
Performance & Indexing: An index on orders.customer_id helps correlated existence checks. PostgreSQL can transform many EXISTS queries into a semi-join, but avoid scalar subqueries that unexpectedly return more than one row.

Example 1: Customers with at Least One Order

Input (customers table):

customer_id	customer_name
1301	Liam Chen
1302	Sophia Patel
1303	Elena Brooks

Input (orders table):

order_id	customer_id	amount
2001	1301	90.00
2002	1301	120.00
2003	1302	75.00

Query:

SELECT
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_name ASC;

Output:

customer_name
Liam Chen
Sophia Patel

Explanation: For each outer customer row, the inner WHERE compares order customer_id to that current customer_id. Liam Chen and Sophia Patel each have at least one match, so EXISTS is TRUE. Elena Brooks has none, so EXISTS is FALSE and outer WHERE removes her.

Example 2: Customers with No Orders, Even When Keys Can Be NULL

Input (customers table):

customer_id	customer_name
1311	Marcus Vance
1312	Nora Davis
1313	Chloe Bennett

Input (orders table):

order_id	customer_id
2011	1311
2012	NULL

Query:

SELECT
    c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_name ASC;

Output:

customer_name
Chloe Bennett
Nora Davis

Explanation: The NULL order key matches no customer because equality with NULL is UNKNOWN. Marcus Vance has a matching order and fails NOT EXISTS. Nora Davis and Chloe Bennett have no matching orders and pass. Using NOT IN (SELECT customer_id FROM orders) here would return no rows because its list contains NULL.

Tips & Tricks

Syntactic Sugar / Shorthand: SELECT 1 inside EXISTS states that only row presence matters; the selected value is not returned.
Defensive SQL Pattern: Use NOT EXISTS instead of NOT IN when the inner value may be NULL.
Mental-Check Debugging Tactic: Run the inner query for one outer key by hand to confirm the correlation condition.

Common Pitfalls & Anti-Patterns

Scalar Subquery with Many Rows: A scalar subquery must return zero or one row; more than one raises an error.
Missing Correlation: Leaving out o.customer_id = c.customer_id makes EXISTS test whether any order exists, not whether the current customer has one.

Key Takeaways

Subquery Shape: Scalar, list, and existence subqueries answer different questions.
Safe Anti-Match: NOT EXISTS handles NULL safely.
Index the Link: Correlated lookups benefit from an index on the related key.

Pro Tip: Compare an EXISTS query with an INNER JOIN only after checking whether the join key is unique. EXISTS returns at most one outer row; an inner join can duplicate it for multiple matches.

14. Common Table Expressions (CTEs) & Modular SQL

Tier: intermediate | Estimated reading time: 6 min

A CTE is a named workbench for one query. It gives an intermediate result a clear label so the next calculation can read it as a table.

Overview

A WITH clause defines one or more Common Table Expressions before the main query. CTEs make multi-stage SQL easier to inspect, especially when a SELECT alias needs to become a source column for a later WHERE filter. Each CTE is a query block with its own logical lifecycle. PostgreSQL can inline a simple CTE or materialize one when requested or needed.

Core Syntax

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_amount
FROM customer_totals
WHERE total_amount >= 100.00;

Key Rules & Engine Behaviors

Lifecycle Phase: The CTE query finishes its own FROM through SELECT work first. Its result becomes a source at Step 1 of the outer query, so outer WHERE at Step 2 can use its total_amount column; a same-block SELECT alias still is not visible to WHERE or HAVING at Step 4.
NULL & Edge Behavior: A CTE does not change NULL behavior. Preserve missing values until a real rule calls for COALESCE or another transformation.
Performance & Indexing: PostgreSQL 12+ can inline many non-recursive CTEs. MATERIALIZED can prevent repeated work but may force storing rows; use it only after EXPLAIN ANALYZE shows a reason.

Example 1: Filtering a Calculated Total in an Outer Query

Input (orders table):

order_id	customer_name	amount
2101	Liam Chen	80.00
2102	Liam Chen	70.00
2103	Sophia Patel	220.00
2104	Elena Brooks	40.00

Query:

WITH customer_totals AS (
    SELECT
        customer_name,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_name
)
SELECT
    customer_name,
    total_amount
FROM customer_totals
WHERE total_amount >= 100.00
ORDER BY total_amount DESC, customer_name ASC;

Output:

customer_name	total_amount
Sophia Patel	220.00
Liam Chen	150.00

Explanation: The CTE groups orders and names total_amount in its SELECT step. The outer FROM then reads customer_totals as its source, which makes total_amount a real input column. Outer WHERE can filter it at Step 2; Elena Brooks is removed before the final sort.

Example 2: Building Two Clear Stages

Input (subscription_events table):

event_id	customer_name	event_type	event_at
2111	Marcus Vance	started	2024-06-01 09:00:00
2112	Marcus Vance	cancelled	2024-06-10 12:00:00
2113	Nora Davis	started	2024-06-03 10:00:00
2114	Nora Davis	started	2024-06-04 08:00:00

Query:

WITH started_events AS (
    SELECT
        customer_name,
        event_at
    FROM subscription_events
    WHERE event_type = 'started'
), latest_start AS (
    SELECT
        customer_name,
        MAX(event_at) AS latest_started_at
    FROM started_events
    GROUP BY customer_name
)
SELECT
    customer_name,
    latest_started_at
FROM latest_start
ORDER BY latest_started_at ASC;

Output:

customer_name	latest_started_at
Marcus Vance	2024-06-01 09:00:00
Nora Davis	2024-06-04 08:00:00

Explanation: started_events filters out the cancellation at its Step 2. latest_start groups only the remaining start rows and finds the latest timestamp per customer. The main query simply reads that small result and sorts it. Each stage has one job, which makes the condition and aggregate easy to test separately.

Tips & Tricks

Syntactic Sugar / Shorthand: Give each CTE a noun that describes its rows, such as started_events or customer_totals.
Defensive SQL Pattern: Use a CTE when repeating a long expression would risk mismatched logic between SELECT and WHERE.
Mental-Check Debugging Tactic: Run each CTE body as a standalone SELECT before running the full query.

Common Pitfalls & Anti-Patterns

Using a CTE for One Simple Query: A CTE adds a layer. Keep a one-stage query flat when it is already clear.
Assuming a CTE Always Stores Rows: PostgreSQL may inline it. Use EXPLAIN ANALYZE before relying on a materialization effect.

Key Takeaways

Named Stages: CTEs turn intermediate results into readable sources.
New Scope: A CTE SELECT alias becomes a column that an outer WHERE can filter.
Plan Evidence: CTE structure aids readability; EXPLAIN ANALYZE decides whether it is efficient.

Pro Tip: Do not use MATERIALIZED as a default. It can help when one expensive CTE is referenced several times, but it can also block filter pushdown and force unnecessary storage.

15. Date & Time Intelligence

Tier: advanced | Estimated reading time: 6 min

Date functions turn a wall calendar into queryable parts. They group events into periods, shift time by known intervals, and expose the boundary rules hidden inside timestamps.

Overview

PostgreSQL stores DATE values without a time and TIMESTAMP values with a date and clock time. Use DATE_TRUNC to place timestamps into a named period, EXTRACT to read one part, and INTERVAL for date arithmetic. These expressions run in SELECT at Step 6 or WHERE at Step 2. Use ISO 8601 literals so a query means the same thing on every machine.

Core Syntax

SELECT
    DATE_TRUNC('month', created_at) AS month_start,
    COUNT(*) AS order_count
FROM orders
WHERE created_at >= TIMESTAMP '2024-03-01 00:00:00'
  AND created_at < TIMESTAMP '2024-04-01 00:00:00'
GROUP BY DATE_TRUNC('month', created_at);

Key Rules & Engine Behaviors

Lifecycle Phase: A date filter in WHERE runs at Step 2. A month_start alias is created by SELECT at Step 6, so WHERE and HAVING at Step 4 cannot use it in the same query block.
NULL & Edge Behavior: Date arithmetic with NULL returns NULL. DATE_TRUNC returns the start of the named unit, not its end; timestamps at exactly the next period boundary need a half-open filter.
Performance & Indexing: Compare an indexed timestamp directly to a start and exclusive end. DATE_TRUNC(created_at) in WHERE often prevents a normal index range scan unless an expression index exists.

Example 1: Grouping Orders by Month

Input (orders table):

order_id	customer_name	created_at
1501	Liam Chen	2024-03-01 08:30:00
1502	Sophia Patel	2024-03-29 15:10:00
1503	Elena Brooks	2024-04-01 00:00:00
1504	Marcus Vance	2024-04-05 11:45:00

Query:

SELECT
    DATE_TRUNC('month', created_at) AS month_start,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month_start ASC;

Output:

month_start	order_count
2024-03-01 00:00:00	2
2024-04-01 00:00:00	2

Explanation: GROUP BY at Step 3 changes both March timestamps into the same March 1 bucket and both April timestamps into the same April 1 bucket. SELECT names each bucket. ORDER BY can use month_start because it runs after SELECT.

Example 2: A Safe Monthly Range Filter

Input (support_tickets table):

ticket_id	customer_name	opened_at
1511	Jordan Miller	2024-03-31 23:59:59
1512	Chloe Bennett	2024-04-01 00:00:00
1513	Nora Davis	2024-04-15 12:00:00
1514	Maya Lin	2024-05-01 00:00:00

Query:

SELECT
    ticket_id,
    customer_name
FROM support_tickets
WHERE opened_at >= TIMESTAMP '2024-04-01 00:00:00'
  AND opened_at < TIMESTAMP '2024-05-01 00:00:00'
ORDER BY opened_at ASC;

Output:

ticket_id	customer_name
1512	Chloe Bennett
1513	Nora Davis

Explanation: WHERE excludes the final second of March and the exact first second of May. The lower bound is included and the next-month bound is excluded. This half-open range works for any timestamp precision and leaves created_at untouched, which helps a normal index range scan.

Tips & Tricks

Syntactic Sugar / Shorthand: CURRENT_DATE gives today's date, while CURRENT_TIMESTAMP includes the current time and time zone.
Defensive SQL Pattern: Use >= period_start and < next_period_start for timestamp reports instead of guessing an end-of-day value.
Mental-Check Debugging Tactic: Test the first instant, last instant, and exact next-period boundary of every date filter.

Common Pitfalls & Anti-Patterns

Inclusive End Date on a Timestamp: <= DATE '2024-04-30' ends at midnight and misses the rest of April 30.
Grouping in WHERE: WHERE DATE_TRUNC('month', created_at) = ... can hide an index range. Filter raw timestamps first.

Key Takeaways

Period Starts: DATE_TRUNC returns a period's first instant.
Stable Ranges: Half-open bounds avoid precision bugs.
Index-Friendly Time: Direct timestamp comparisons are easier to optimize.

Pro Tip: Store a timestamp with time zone for events that occur at a real instant across locations. Convert it to a reporting zone before truncating; otherwise a midnight boundary may land in the wrong local day.

16. Window Functions: Introduction & The OVER Clause

Tier: advanced | Estimated reading time: 6 min

A window function is a surveyor's level. It measures and compares neighboring rows across a room without knocking down the walls or collapsing the chairs into a single heap.

Overview

Standard aggregate functions with GROUP BY collapse rows into a single summary line per group, discarding individual row identities. In contrast, window functions calculate metrics across a group of related rows (a "window") while preserving every original row in the final output. Window functions run at Step 5 of the logical SQL execution lifecycle, after WHERE, GROUP BY, and HAVING, but before SELECT projection at Step 6. The OVER clause tells the database engine how to frame the window.

Core Syntax

SELECT
    column_name,
    agg_function(column_name) OVER (
        PARTITION BY partition_column
        ORDER BY sort_column
    ) AS window_metric
FROM table_name;

Key Rules & Engine Behaviors

Lifecycle Phase: Evaluates at Step 5. Aliases created by window functions cannot be referenced directly in WHERE (Step 2) or HAVING (Step 4); filtering requires a Common Table Expression (CTE) or subquery.
The Anatomy of OVER():
- Empty OVER (): Treats the entire result set as a single, global window.
- PARTITION BY: Divides the dataset into independent calculation slices (analogous to GROUP BY, but without collapsing rows). The window calculation restarts from scratch for each partition.
- ORDER BY: Defines the sequence in which rows within each partition are evaluated.
Performance & Memory: The database engine groups and sorts rows in memory (work_mem). Large partitions without indexes on the partition and sort keys may spill to temporary disk.

Example 1: Comparing Individual Salaries to the Department Average

Input (employees table):

employee_name	department	salary
Liam Chen	Engineering	120000.00
Sophia Patel	Engineering	110000.00
Elena Brooks	Marketing	90000.00
Marcus Vance	Marketing	80000.00

Query:

SELECT
    employee_name,
    department,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg_salary,
    salary - ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS diff_from_avg
FROM employees
ORDER BY department ASC, salary DESC;

Output:

employee_name	department	salary	dept_avg_salary	diff_from_avg
Liam Chen	Engineering	120000.00	115000.00	5000.00
Sophia Patel	Engineering	110000.00	115000.00	-5000.00
Elena Brooks	Marketing	90000.00	85000.00	5000.00
Marcus Vance	Marketing	80000.00	85000.00	-5000.00

Explanation: PARTITION BY department splits rows into Engineering and Marketing. Unlike GROUP BY, all four employee records remain visible. The engine calculates the average for each department ($115,000 for Engineering, $85,000 for Marketing) and displays it directly beside each individual salary for immediate comparison.

Example 2: Calculating Percentage of Department Revenue Contribution

Input (sales_deals table):

rep_name	region	deal_amount
Liam Chen	North	60000.00
Sophia Patel	North	40000.00
Elena Brooks	South	75000.00
Marcus Vance	South	25000.00

Query:

SELECT
    rep_name,
    region,
    deal_amount,
    SUM(deal_amount) OVER (PARTITION BY region) AS region_total,
    ROUND((deal_amount * 100.0) / SUM(deal_amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales_deals
ORDER BY region ASC, deal_amount DESC;

Output:

rep_name	region	deal_amount	region_total	pct_of_region
Liam Chen	North	60000.00	100000.00	60.0
Sophia Patel	North	40000.00	100000.00	40.0
Elena Brooks	South	75000.00	100000.00	75.0
Marcus Vance	South	25000.00	100000.00	25.0

Explanation: SUM(deal_amount) OVER (PARTITION BY region) computes the denominator ($100,000) for each territory without requiring a second query or self-join. Multiplying by 100.0 prevents PostgreSQL integer division and produces a clean percentage.

Tips & Tricks

Named Windows: If you use the same window specification multiple times in a query, define it at the bottom using WINDOW w AS (PARTITION BY region ORDER BY deal_amount DESC) and reference OVER w.
Defensive Casting: When computing ratios with window totals, always multiply by 1.0 or 100.0 before division to avoid integer truncation in PostgreSQL.
Debugging Partition Boundaries: Run COUNT(*) OVER (PARTITION BY col) first to verify that your partitions contain the expected row counts before layering complex calculations.

Common Pitfalls & Anti-Patterns

Filtering in WHERE Directly: Writing WHERE AVG(salary) OVER (...) > 100000 produces an immediate syntax error. Window functions run at Step 5; WHERE runs at Step 2. Wrap the query in a CTE first.
Confusing GROUP BY with PARTITION BY: Using GROUP BY when you need row-level details forces unnecessary joins. Use window functions whenever the output row count must equal the input row count.

Key Takeaways

No Row Collapse: Window functions preserve every original row while computing group metrics.
The OVER Clause: Controls the calculation scope via PARTITION BY (grouping) and ORDER BY (sequencing).
Step 5 Execution: Runs after filtering and grouping; cannot be used in WHERE without a CTE.

Pro Tip: Create a composite index on (partition_col, order_col). PostgreSQL can leverage this index to read rows in pre-sorted partition order, eliminating expensive sort operations in EXPLAIN plans.

17. Window Functions: ROW_NUMBER, RANK & DENSE_RANK

Tier: advanced | Estimated reading time: 7 min

Ranking functions are leaderboard scorekeepers. They assign numerical positions to rows based on order, but each handles ties and gaps with a fundamentally different rule.

Overview

SQL provides three core ranking functions, all evaluated at Step 5 of the execution lifecycle within the OVER (ORDER BY ...) clause:

ROW_NUMBER(): Assigns a strictly unique, incrementing integer (1, 2, 3, 4) to every row. It never creates ties and never leaves gaps.
RANK(): Implements Olympic competition scoring (1, 2, 2, 4). When rows share identical ordering values, they receive the exact same rank, and subsequent ranks skip by the number of tied rows (K + N).
DENSE_RANK(): Implements unbroken tier numbering (1, 1, 2, 3). Tied rows share the same rank, but the very next distinct value receives the immediate next integer—leaving zero gaps.

Choosing the right ranking function is one of the most common SQL interview challenges and a fundamental skill in production reporting.

Core Syntax

SELECT
    item_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM leaderboard;

Key Rules & Engine Behaviors

The Tie & Gap Comparison Matrix:
- ROW_NUMBER(): No ties allowed; no gaps. If scores match, row assignment is arbitrary unless a deterministic secondary sort column (like a primary key ID) is added.
- RANK(): Ties share rank; gaps occur. If two rows share rank 2, rank 3 is skipped and the next row receives rank 4.
- DENSE_RANK(): Ties share rank; no gaps. If two rows share rank 2, the next row receives rank 3.
NULL Ordering in PostgreSQL: By default in PostgreSQL, ORDER BY col DESC places NULLS FIRST. When ranking from highest to lowest, always append NULLS LAST so missing values do not steal rank 1!
Filtering Lifecycle Rule: Because ranking evaluates at Step 5, you cannot filter WHERE rnk <= 3 in the same SELECT block. You must wrap the calculation in a CTE or subquery.

Example 1: Side-by-Side Comparison of All Three Functions on Tied Data

Input (rep_sales table):

rep_name	region	total_sales
Liam Chen	East	50000.00
Elena Brooks	East	40000.00
Sophia Patel	East	40000.00
Marcus Vance	East	30000.00
Jordan Miller	East	20000.00

Query:

SELECT
    rep_name,
    total_sales,
    ROW_NUMBER() OVER (ORDER BY total_sales DESC) AS row_num,
    RANK() OVER (ORDER BY total_sales DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY total_sales DESC) AS dense_rank_num
FROM rep_sales
ORDER BY total_sales DESC, rep_name ASC;

Output:

rep_name	total_sales	row_num	rank_num	dense_rank_num
Liam Chen	50000.00	1	1	1
Elena Brooks	40000.00	2	2	2
Sophia Patel	40000.00	3	2	2
Marcus Vance	30000.00	4	4	3
Jordan Miller	20000.00	5	5	4

Explanation:

Liam Chen: Unambiguous 1st place across all three functions ($50,000).
Elena Brooks & Sophia Patel: Tie at $40,000.
- ROW_NUMBER arbitrarily assigns 2 and 3 because it refuses to tie.
- RANK assigns both reps rank 2.
- DENSE_RANK also assigns both reps rank 2.
Marcus Vance: The turning point at $30,000:
- ROW_NUMBER continues to 4.
- RANK jumps to 4 (skipping 3 because two people shared 2nd place).
- DENSE_RANK moves to 3 (consecutive tier with zero gaps).

Example 2: Canonical Interview Patterns (Top-1 Deduplication vs N-th Highest Salary)

Input (salaries table):

employee_id	employee_name	department	salary
1701	Maya Lin	Engineering	140000.00
1702	Liam Chen	Engineering	140000.00
1703	Sophia Patel	Engineering	115000.00
1704	Marcus Vance	Engineering	95000.00

Query (Finding the 2nd Highest Distinct Salary):

WITH ranked_salaries AS (
    SELECT
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_tier
    FROM salaries
)
SELECT employee_name, department, salary
FROM ranked_salaries
WHERE salary_tier = 2;

Output:

employee_name	department	salary
Sophia Patel	Engineering	115000.00

Explanation: Maya Lin and Liam Chen tie for the top salary ($140,000), so both receive salary_tier = 1. Because DENSE_RANK leaves no gaps, Sophia Patel ($115,000) is guaranteed to receive salary_tier = 2. If standard RANK had been used, Sophia would have received rank 3, and filtering for salary_tier = 2 would have returned zero rows!

Tips & Tricks

Top-1 Deduplication Pattern: When keeping only the most recent row per user or device, always use ROW_NUMBER() with WHERE rn = 1. It guarantees exactly one row returned per partition even if timestamps tie.
Deterministic Sorting: In ROW_NUMBER(), always include a unique tie-breaker column (e.g., ORDER BY created_at DESC, id DESC) so query results remain identical across runs.
Descending NULL Placement: Always specify ORDER BY score DESC NULLS LAST in ranking functions to prevent NULL scores from taking 1st place.

Common Pitfalls & Anti-Patterns

Using RANK to find the "Runner-Up": Filtering WHERE rnk = 2 fails whenever there is a tie for 1st place (ranks 1, 1, 3). Always use DENSE_RANK when querying for the N-th distinct value tier.
Using ROW_NUMBER for Awards/Podiums: Assigning ROW_NUMBER to contestants arbitrarily strips one tied contestant of their earned award based on internal database tuple storage order.

Key Takeaways

ROW_NUMBER: Strictly unique (1, 2, 3, 4). Best for pagination and deduplication.
RANK: Gaps on ties (1, 2, 2, 4). Best for competitive sports and academic leaderboards.
DENSE_RANK: Consecutive tiers (1, 1, 2, 3). Best for financial tiers and N-th highest value queries.

Pro Tip: When an interview question asks for the "top 3 highest earners per department", clarify whether ties should expand the podium (use DENSE_RANK for top 3 salary amounts, or RANK for top 3 entrants) or cap strictly at 3 rows (use ROW_NUMBER).

18. Window Functions: Lead, Lag & Offset Functions

Tier: advanced | Estimated reading time: 6 min

LAG and LEAD let each row glance at its neighbor in an ordered line. They expose the prior or next value without a self-join.

Overview

LAG returns a value from an earlier row in its window order; LEAD returns one from a later row. Both run at Step 5 with other window functions. They are useful for period-over-period changes, next event timing, and change detection. The order inside OVER defines what prior and next mean, so it must be complete and stable.

Core Syntax

SELECT
    occurred_at,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY occurred_at ASC
    ) AS prior_amount
FROM payments;

Key Rules & Engine Behaviors

Lifecycle Phase: LAG and LEAD run at Step 5. An alias made in SELECT at Step 6 cannot appear in WHERE at Step 2 or HAVING at Step 4; place a filter in an outer query.
NULL & Edge Behavior: The first LAG value and last LEAD value are NULL by default when no neighbor exists. A present neighbor with a NULL value also returns NULL; use a supplied default only when it has a real meaning.
Performance & Indexing: The engine must order each partition. Large partitions can require a sort and temporary disk; filter early in WHERE before applying the window.

Example 1: Calculating Change from the Prior Month

Input (monthly_sales table):

month_start	amount
2024-01-01	100.00
2024-02-01	125.00
2024-03-01	110.00

Query:

SELECT
    month_start,
    amount,
    LAG(amount) OVER (ORDER BY month_start ASC) AS prior_amount,
    amount - LAG(amount) OVER (ORDER BY month_start ASC) AS amount_change
FROM monthly_sales
ORDER BY month_start ASC;

Output:

month_start	amount	prior_amount	amount_change
2024-01-01	100.00	NULL	NULL
2024-02-01	125.00	100.00	25.00
2024-03-01	110.00	125.00	-15.00

Explanation: The ordered window lines months up from January through March. January has no earlier row, so its prior value and calculated change are NULL. February and March each subtract the prior row's amount. This does not assume a calendar month exists for every row; it compares the preceding available row.

Example 2: Finding Time Until the Next Event

Input (delivery_events table):

event_id	shipment_id	occurred_at
1701	1	2024-08-01 09:00:00
1702	1	2024-08-01 14:30:00
1703	2	2024-08-02 10:00:00
1704	2	2024-08-02 11:15:00

Query:

SELECT
    shipment_id,
    occurred_at,
    LEAD(occurred_at) OVER (
        PARTITION BY shipment_id
        ORDER BY occurred_at ASC, event_id ASC
    ) AS next_occurred_at
FROM delivery_events
ORDER BY shipment_id ASC, occurred_at ASC, event_id ASC;

Output:

shipment_id	occurred_at	next_occurred_at
1	2024-08-01 09:00:00	2024-08-01 14:30:00
1	2024-08-01 14:30:00	NULL
2	2024-08-02 10:00:00	2024-08-02 11:15:00
2	2024-08-02 11:15:00	NULL

Explanation: PARTITION BY prevents shipment 1 from looking into shipment 2. Within each shipment, LEAD returns the timestamp from the next event. The last event in each partition gets NULL because no next row exists. event_id makes the order fixed if timestamps tie.

Tips & Tricks

Syntactic Sugar / Shorthand: LAG(value, 2) looks back two rows; LAG(value, 1, 0) returns zero when no prior row exists.
Defensive SQL Pattern: Keep a NULL first-row comparison unless zero is genuinely the correct baseline.
Mental-Check Debugging Tactic: Sort and display the partition key, ordering keys, and prior or next column together.

Common Pitfalls & Anti-Patterns

No Tie-Breaker: Equal ordering values can make the neighbor choice unpredictable. Add a unique final key.
Calling Prior Month Without a Calendar: LAG returns the prior row, not automatically the prior calendar period. Join a calendar when missing months matter.

Key Takeaways

Neighbor Access: LAG and LEAD compare rows without a self-join.
Order Defines Meaning: The OVER order defines prior and next.
Partition Isolation: PARTITION BY limits comparisons to related rows.

Pro Tip: Calculate LAG once in a CTE when several later expressions need it. That keeps the report rule in one place and makes first-row handling easy to inspect.

19. Window Frames & Running Aggregates

Tier: advanced | Estimated reading time: 6 min

A window frame is a movable ruler over an ordered set of rows. It defines exactly which nearby rows feed the total shown beside the current row.

Overview

An ordered window aggregate can calculate running totals and moving averages without collapsing detail rows. ROWS counts physical rows around the current row. RANGE uses ordering-value peers, which can include several rows with equal sort values. State the frame explicitly when a report depends on its boundaries; the default frame with ORDER BY can surprise readers.

Core Syntax

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales;

Key Rules & Engine Behaviors

Lifecycle Phase: Window aggregates run at Step 5 and keep all rows. Their aliases appear at SELECT Step 6, so WHERE and HAVING cannot filter running_total in the same query block.
NULL & Edge Behavior: SUM skips NULL amounts and returns NULL only if its frame has no non-NULL amount. AVG also skips NULL values. ROWS and RANGE differ when ordering keys tie.
Performance & Indexing: Ordered windows often need sorted input. Large partitions and broad frames can use substantial memory or temporary disk; reduce rows in WHERE before the window stage.

Example 1: A Running Daily Total

Input (daily_sales table):

sale_id	sale_date	amount
1801	2024-09-01	50.00
1802	2024-09-02	75.00
1803	2024-09-03	40.00

Query:

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date ASC, sale_id ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales
ORDER BY sale_date ASC, sale_id ASC;

Output:

sale_date	amount	running_total
2024-09-01	50.00	50.00
2024-09-02	75.00	125.00
2024-09-03	40.00	165.00

Explanation: For the first row, the frame contains only 50.00. Each later frame starts at the first row and extends through the current row. The query retains the individual daily amounts while showing the growing total beside them.

Example 2: A Three-Row Moving Average

Input (daily_sales table):

sale_id	sale_date	amount
1811	2024-09-01	30.00
1812	2024-09-02	60.00
1813	2024-09-03	90.00
1814	2024-09-04	120.00

Query:

SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date ASC, sale_id ASC
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_row_average
FROM daily_sales
ORDER BY sale_date ASC, sale_id ASC;

Output:

sale_date	amount	three_row_average
2024-09-01	30.00	30.0000000000000000
2024-09-02	60.00	45.0000000000000000
2024-09-03	90.00	60.0000000000000000
2024-09-04	120.00	90.0000000000000000

Explanation: The frame takes the current row and up to two preceding physical rows. It has one row on September 1, two on September 2, and three thereafter. This is a three-row average, not necessarily a three-day average if dates are missing or repeated.

Tips & Tricks

Syntactic Sugar / Shorthand: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW states a running total plainly.
Defensive SQL Pattern: Include a unique tie-breaker in the window ORDER BY before using ROWS frames.
Mental-Check Debugging Tactic: For one selected row, list the exact rows that should enter its frame and calculate the result by hand.

Common Pitfalls & Anti-Patterns

Confusing ROWS and RANGE: ROWS counts rows; RANGE can include all peer rows with the same sort value.
Calling a Row Frame a Time Frame: Three rows can cover three minutes or three months. Use a calendar-based method when time distance is the rule.

Key Takeaways

Frame Controls Inputs: The frame, not the aggregate name, decides which rows contribute.
Detail Remains: Window aggregates keep source detail rows.
Tie Safety: A stable order makes row-based frames repeatable.

Pro Tip: State the frame even when PostgreSQL's default happens to match today's intent. An explicit frame prevents a later ORDER BY change from quietly altering every running value.

20. Recursive CTEs & Hierarchies

Tier: advanced | Estimated reading time: 6 min

A recursive CTE walks a tree one branch at a time. It starts with known root rows, then repeatedly finds children until no new row remains.

Overview

WITH RECURSIVE contains an anchor query and a recursive query joined with UNION ALL. The anchor provides the first rows. The recursive part reads the prior iteration and finds the next level. It is useful for organization charts, folder trees, and other parent-child paths. A bad cycle can make recursion never finish, so guard against it when source data may contain loops.

Core Syntax

WITH RECURSIVE hierarchy AS (
    SELECT
        employee_id,
        manager_id,
        employee_name,
        0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.manager_id,
        e.employee_name,
        h.depth + 1 AS depth
    FROM employees AS e
    JOIN hierarchy AS h
        ON e.manager_id = h.employee_id
)
SELECT
    employee_name,
    depth
FROM hierarchy;

Key Rules & Engine Behaviors

Lifecycle Phase: Each anchor and recursive member follows its own query lifecycle. The accumulated CTE becomes a source for the outer FROM at Step 1; its SELECT aliases are not visible to WHERE in their same query block.
NULL & Edge Behavior: Roots usually have manager_id IS NULL because equality with NULL does not match. An orphan with a non-NULL manager that is absent from the table is not reached from a root.
Performance & Indexing: Index the child table's manager_id for each recursive lookup. Deep or broad trees create many work rows; include only columns the walk needs.

Example 1: Walking an Organization Tree

Input (employees table):

employee_id	employee_name	manager_id
1901	Maya Lin	NULL
1902	Liam Chen	1901
1903	Sophia Patel	1901
1904	Elena Brooks	1902

Query:

WITH RECURSIVE hierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        h.depth + 1 AS depth
    FROM employees AS e
    JOIN hierarchy AS h
        ON e.manager_id = h.employee_id
)
SELECT
    employee_name,
    depth
FROM hierarchy
ORDER BY depth ASC, employee_name ASC;

Output:

employee_name	depth
Maya Lin	0
Liam Chen	1
Sophia Patel	1
Elena Brooks	2

Explanation: The anchor returns Maya Lin as the only root. The first recursive pass finds Liam Chen and Sophia Patel, whose manager is Maya Lin. The next pass finds Elena Brooks under Liam Chen. No employee reports to Elena Brooks, so the walk stops.

Example 2: Carrying a Readable Path

Input (categories table):

category_id	category_name	parent_id
1911	Electronics	NULL
1912	Audio	1911
1913	Headphones	1912
1914	Furniture	NULL

Query:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_id,
        category_name::TEXT AS category_path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_id,
        t.category_path || ' > ' || c.category_name AS category_path
    FROM categories AS c
    JOIN category_tree AS t
        ON c.parent_id = t.category_id
)
SELECT
    category_name,
    category_path
FROM category_tree
ORDER BY category_path ASC;

Output:

category_name	category_path
Audio	Electronics > Audio
Headphones	Electronics > Audio > Headphones
Electronics	Electronics
Furniture	Furniture

Explanation: The anchor creates a path for each root. Each recursive row appends the child name to its parent's path. The TEXT cast makes both UNION ALL branches return compatible path types. The outer sort orders text paths, not hierarchy traversal order.

Tips & Tricks

Syntactic Sugar / Shorthand: Carry a depth integer to indent, limit, or inspect a tree.
Defensive SQL Pattern: Keep an array of visited IDs and reject a child already in the path when untrusted data can contain cycles.
Mental-Check Debugging Tactic: Run the anchor alone, then check one recursive pass by joining children to the anchor's IDs.

Common Pitfalls & Anti-Patterns

UNION Instead of UNION ALL by Habit: UNION adds duplicate removal work and can hide repeated paths. Use it only when duplicate removal is the intended safety rule.
Ignoring Cycles: A row that eventually points back to an ancestor can recurse forever or until resource limits stop it.

Key Takeaways

Two Parts: Recursive CTEs need an anchor and a recursive member.
Parent Index: Recursion repeatedly looks up children by parent key.
Cycle Safety: Hierarchical data needs a loop policy when source data is not guaranteed clean.

Pro Tip: Keep a depth ceiling during exploratory work, such as WHERE h.depth < 20 in the recursive member. Remove or adjust it only after verifying the data's maximum real depth.

21. Query Optimization, Indexing & EXPLAIN Plans

Tier: advanced | Estimated reading time: 6 min

EXPLAIN is the engine's route map, not a grade for a query. It shows how PostgreSQL expects to find, join, sort, and aggregate rows; EXPLAIN ANALYZE adds what actually happened.

Overview

PostgreSQL's planner chooses a physical plan based on table statistics, available indexes, data size, and estimated cost. A sequential scan reads a table in physical order; an index scan follows an index to matching rows. Use EXPLAIN ANALYZE to compare estimated rows with actual rows and find expensive scans, joins, sorts, or aggregates. Optimize measured bottlenecks, not query appearance.

Core Syntax

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    order_id,
    customer_id,
    created_at
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMP '2024-01-01 00:00:00'
ORDER BY created_at DESC;

Key Rules & Engine Behaviors

Lifecycle Phase: Logical SQL still runs from FROM at Step 1 through LIMIT at Step 10, but the planner may change physical join order or push safe filters earlier. SELECT aliases remain unavailable to WHERE and HAVING because their logical scopes do not change.
NULL & Edge Behavior: Statistics track NULL fractions and value distribution, which influence estimates. Indexes can support NULL checks, but a condition's selectivity decides whether an index is worthwhile.
Performance & Indexing: An index helps when it avoids enough work. Composite index column order matters: equality filters commonly come before range or order columns. Extra indexes slow writes and use storage.

Example 1: A Filter and Order That Match One Index

Input (orders table):

order_id	customer_id	created_at	amount
2001	42	2024-01-05 10:00:00	80.00
2002	17	2024-01-06 11:00:00	40.00
2003	42	2024-02-01 09:00:00	120.00
2004	42	2023-12-30 15:00:00	60.00

Query:

SELECT
    order_id,
    created_at,
    amount
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMP '2024-01-01 00:00:00'
ORDER BY created_at DESC;

Output:

order_id	created_at	amount
2003	2024-02-01 09:00:00	120.00
2001	2024-01-05 10:00:00	80.00

Explanation: WHERE removes the order for customer 17 and the 2023 order before SELECT. ORDER BY sorts the two qualifying rows by newest timestamp. On a large table, an index such as (customer_id, created_at DESC) matches the equality filter and requested order, but EXPLAIN ANALYZE must confirm that PostgreSQL uses it.

Example 2: A Non-Sargable Date Filter and Its Direct Form

Input (orders table):

order_id	customer_name	created_at
2011	Liam Chen	2024-03-01 08:00:00
2012	Sophia Patel	2024-03-31 20:00:00
2013	Elena Brooks	2024-04-01 00:00:00
2014	Marcus Vance	2024-04-15 10:00:00

Query:

SELECT
    order_id,
    customer_name
FROM orders
WHERE created_at >= TIMESTAMP '2024-03-01 00:00:00'
  AND created_at < TIMESTAMP '2024-04-01 00:00:00'
ORDER BY created_at ASC;

Output:

order_id	customer_name
2011	Liam Chen
2012	Sophia Patel

Explanation: This direct range returns March rows and excludes the April boundary. It is generally easier for an index on created_at than WHERE DATE_TRUNC('month', created_at) = TIMESTAMP '2024-03-01 00:00:00', which computes a value for each candidate row. The two forms can return the same result, but not the same plan cost.

Tips & Tricks

Syntactic Sugar / Shorthand: EXPLAIN shows estimates without running a change; EXPLAIN ANALYZE runs the query and reports actual timing and row counts.
Defensive SQL Pattern: Add an index only for a measured read pattern, then keep it only if its benefit outweighs write and storage cost.
Mental-Check Debugging Tactic: Compare estimated rows to actual rows at each plan node. Large gaps usually point to stale or weak statistics.

Common Pitfalls & Anti-Patterns

Forcing an Index: A sequential scan is often faster when a query reads most of a small table. Trust measured plans over index folklore.
Function-Wrapped Search Column: Functions on an indexed filter column can block a plain index scan. Use a direct range or a matching expression index.

Key Takeaways

Measure First: EXPLAIN ANALYZE is the evidence for tuning decisions.
Index Shape Matters: Composite index order must match real filters and ordering.
Planner Estimates: Accurate statistics help PostgreSQL choose the right route.

Pro Tip: Refresh statistics with ANALYZE after a major data change before judging a plan. If estimates still miss badly, inspect value skew and consider higher statistics targets for the specific columns involved.