SQL + PostgreSQL + NoSQL

SELECT / WHERE / GROUP BY Foundation

The core read pattern: pick columns, filter rows, then aggregate.

What

The basic command to ask a database for data — pick columns, filter rows, and total things up.

Where

Inside any SQL query — in psql, DBeaver, a Python script, or a dbt model.

When

Almost every time you touch a database — this is the query you'll write most often.

How

SELECT the columns you want, FROM the table, WHERE to filter, GROUP BY to total by category.

SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS lifetime_value
FROM orders
WHERE order_status != 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY lifetime_value DESC
LIMIT 20;

• WHERE filters rows before grouping; HAVING filters groups after aggregation.
• Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT — this is why you can't reference a SELECT alias in WHERE.
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX. Every non-aggregated column in SELECT must appear in GROUP BY.
• DISTINCT removes duplicate rows — know the difference between COUNT(*) and COUNT(DISTINCT col).

Postgres runs a query in a set order, even though you don't write it in that order. First it looks at FROM (and any joins). Then WHERE removes rows that don't match. Then GROUP BY groups what's left. Then HAVING filters those groups. Only at the very end does SELECT work out your output columns. That's why WHERE total_amount > 100 works, but WHERE lifetime_value > 100 doesn't — lifetime_value is just a name you gave a column in SELECT, and WHERE runs before SELECT exists. Use HAVING lifetime_value > 100 instead, since HAVING runs after grouping.

Worked example — say orders has these rows:

Walking through the example above: customer 2 gets removed by HAVING COUNT(*) > 3, so they never appear in the results. Customer 1's cancelled $500 order never gets counted either, because WHERE removed it before the SUM even ran. That's why lifetime_value comes out to 200, not 700. Remembering the difference — WHERE removes individual rows, HAVING removes whole groups — will save you from a lot of "why is my total wrong" bugs.

-- a second common pattern: safe aggregation with NULLs
SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(shipped_at) AS shipped_orders,        -- COUNT ignores NULLs, so this counts only shipped rows
  ROUND(AVG(total_amount), 2) AS avg_order_value,
  COALESCE(SUM(discount_amount), 0) AS total_discount   -- COALESCE avoids a NULL result when there are no discounts
FROM orders
GROUP BY customer_id;

JOINs

Combining rows across tables — one of the most important SQL concepts to have solid, since real databases almost always spread data across multiple tables.

What

A way to combine matching rows from two or more tables into one result.

Where

Any query where the data you need is spread across separate tables.

When

Whenever one table alone doesn't have everything you need — e.g. order details plus customer names.

How

Add JOIN table2 ON table1.key = table2.key, choosing INNER/LEFT/etc. based on whether unmatched rows should stay.

SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

Worked example — 3 customers, only 2 have orders:

Try running SELECT c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;. Asha and Ravi show up with their real order_ids. Meera shows up too, just with order_id = NULL. That's the whole point of a LEFT JOIN: every row on the left side stays, matched or not. Swap it for INNER JOIN and Meera would just disappear from the results — exactly the mistake the warning above is about.

Self joins deserve a concrete example too — finding every customer who spent more than the average order in their own history:

SELECT DISTINCT a.customer_id, a.order_id, a.total_amount
FROM orders a
JOIN orders b ON a.customer_id = b.customer_id AND a.order_id != b.order_id
WHERE a.total_amount > (
  SELECT AVG(total_amount) FROM orders b2 WHERE b2.customer_id = a.customer_id
);

Here, the orders table is joined to itself — once as a (the row we're checking) and once as b (every other row to compare against). This "self join" trick is also how you'd answer a classic question like "find every employee who earns more than their manager."

CTEs (Common Table Expressions)

Named, temporary result sets that make multi-step logic readable — the backbone of dbt models.

What

A named, temporary mini-query that makes a big query easier to read.

Where

At the top of any query that would otherwise need several nested subqueries.

When

When a query has multiple logical steps you want to name and reason about separately.

How

Write WITH name AS (query), then use name like a normal table further down.

WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount) AS revenue
  FROM orders
  GROUP BY 1
),
ranked_months AS (
  SELECT month, revenue, RANK() OVER (ORDER BY revenue DESC) AS rnk
  FROM monthly_sales
)
SELECT * FROM ranked_months WHERE rnk <= 3;

• CTEs replace nested subqueries with a readable, top-down chain of steps — this is exactly how dbt models are structured.
• Recursive CTEs (WITH RECURSIVE) walk hierarchical data (org charts, category trees, bill-of-materials).
• In Postgres 12+, CTEs are inlined by the planner unless marked MATERIALIZED — don't assume a CTE is always an "optimization fence" anymore.

Recursive CTE example — walking a product category tree (subcategories nested under parents):

WITH RECURSIVE category_tree AS (
  -- anchor: top-level categories with no parent
  SELECT category_id, category_name, parent_id, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- recursive step: join children to the rows found so far
  SELECT c.category_id, c.category_name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree ORDER BY depth, category_name;

The anchor part starts things off — it grabs the categories that have no parent. The recursive part then keeps joining the table to its own results, one level deeper each time, until there's nothing left to add. This same pattern — start, then keep joining to yourself — works for any tree-shaped data: org charts, comment threads, or a list of parts made of other parts.

Subqueries

A query nested inside another — scalar, row, or correlated.

What

A query nested inside another query.

Where

Inside a SELECT list, a WHERE clause, or a FROM clause.

When

When you need a calculated value or filtered list before the main query can run.

How

Wrap the inner query in parentheses and use it wherever a value or table is expected.

-- Scalar subquery
SELECT customer_name
FROM customers
WHERE customer_id = (SELECT customer_id FROM orders ORDER BY total_amount DESC LIMIT 1);

-- Correlated subquery (re-evaluated per outer row)
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > 1000
);

• IN vs EXISTS: EXISTS is generally faster for correlated existence checks on large tables because it can short-circuit.
• A subquery in FROM is a "derived table" — often more readable as a CTE instead.
• A subquery in SELECT must return exactly one row/column (a scalar) — if it might return more, Postgres raises a runtime error, not a silently wrong result.

Worked example — a scalar subquery used inline, alongside ANY/ALL for multi-row comparisons:

-- scalar subquery in SELECT: how far each order is from the customer's average
SELECT
  order_id,
  customer_id,
  total_amount,
  total_amount - (
    SELECT AVG(total_amount) FROM orders o2 WHERE o2.customer_id = o1.customer_id
  ) AS diff_from_avg
FROM orders o1;

-- ANY: matches if the value beats at least one row from the subquery
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'budget');

-- ALL: matches only if the value beats every row from the subquery
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'budget');

price > ANY (...) basically means "bigger than the smallest value in the subquery." price > ALL (...) means "bigger than the biggest value." In practice, a plain MIN() or MAX() subquery usually reads more clearly — but it's worth recognizing ANY/ALL since you'll run into them.

CASE expressions

Inline conditional logic — SQL's if/else.

What

SQL's version of “if this, then that, otherwise something else.”

Where

Inside a SELECT list, or inside WHERE/ORDER BY.

When

Whenever you need to turn raw values into labels or categories.

How

WHEN condition THEN value, repeated as needed, then ELSE a default, then END.

SELECT order_id, total_amount,
  CASE
    WHEN total_amount >= 500 THEN 'high'
    WHEN total_amount >= 100 THEN 'medium'
    ELSE 'low'
  END AS order_tier
FROM orders;

Used constantly for bucketing, pivoting (SUM(CASE WHEN ... THEN amount END)), and cleaning messy categorical data.

Pivot example — turning rows of (month, status, amount) into one column per status, a pattern that comes up constantly when building reporting tables by hand instead of with dbt:

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) AS completed_revenue,
  SUM(CASE WHEN order_status = 'refunded'  THEN total_amount ELSE 0 END) AS refunded_revenue,
  COUNT(CASE WHEN order_status = 'cancelled' THEN 1 END) AS cancelled_count
FROM orders
GROUP BY 1
ORDER BY 1;

Each CASE only adds to its own column. Inside SUM, the ELSE 0 matters: a non-matching row contributes 0, not nothing, so the total stays correct. Inside COUNT, you leave out the ELSE on purpose — a non-matching row becomes NULL, and COUNT simply skips those.

Pivot Tables Rows → Columns

Turning distinct values from one column into their own separate columns — the classic "rotate the table sideways" problem, and a pattern you'll run into constantly when building reports by hand.

What

Reshaping data so that values from one column (like a category) become column headers, with related values lined up underneath them.

Where

Reporting queries and dashboards, wherever "one row per category, side by side" is easier to read than "one row per record."

When

When you need to compare groups side by side, or when an output format specifically asks for one column per category.

How

Give every row a position number within its own group, then group by that position number and pick out one value per category using MAX(CASE WHEN ...).

You already saw the simple version of pivoting in the CASE section above — turning rows into summed columns with SUM(CASE WHEN ...). That works when you're aggregating numbers. But what about pivoting names — individual text values, not sums? That needs one extra idea: giving each row a position number within its category, so rows can line up correctly across columns.

Start with the raw data. Say OCCUPATIONS looks like this:

Step 1 — number each row within its own occupation, alphabetically. This is exactly the "latest record per customer" window function pattern from the section below, just used here to create an alignment key instead of a dedupe key:

SELECT
  Name,
  Occupation,
  ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
FROM OCCUPATIONS;

PARTITION BY Occupation restarts the count at 1 for every new occupation. ORDER BY Name means the alphabetically-first name in each occupation gets rn = 1, the second gets rn = 2, and so on. Running this gives:

Notice rn is what lets rows from different occupations line up on the same output row later — "the 1st Actor" ends up on the same row as "the 1st Doctor" and "the 1st Professor," simply because they all share rn = 1.

Step 2 — group by that row number, and pull one name per occupation into its own column:

SELECT
  MAX(CASE WHEN Occupation = 'Doctor'    THEN Name END) AS Doctor,
  MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
  MAX(CASE WHEN Occupation = 'Singer'    THEN Name END) AS Singer,
  MAX(CASE WHEN Occupation = 'Actor'     THEN Name END) AS Actor
FROM (
  SELECT
    Name,
    Occupation,
    ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
  FROM OCCUPATIONS
) ranked
GROUP BY rn
ORDER BY rn;

Which produces exactly the requested shape:

Why MAX, and why the NULLs appear on their own: for a given rn group (say rn = 2), only one row in that group actually has Occupation = 'Doctor' — every other row makes the CASE return nothing (an implicit NULL, since there's no ELSE). MAX() across a group of mostly-NULLs with one real value just returns that one real value, because MAX ignores NULLs the same way COUNT does. And when a group has no row at all for that occupation (like rn = 2 has no second Doctor), every value going into MAX is NULL, so the result is NULL too — which is exactly the "print NULL when there are no more names" requirement, with no extra code needed to produce it.

Two more practice questions on this same OCCUPATIONS table — neither needs a pivot, but both are good practice for string building and grouped counts.

SELECT Name || '(' || LEFT(Occupation, 1) || ')' AS name_and_occupation
FROM OCCUPATIONS
ORDER BY Name;

|| is Postgres's string concatenation operator (CONCAT(Name, '(', LEFT(Occupation, 1), ')') works identically, if you prefer a function over the operator). LEFT(Occupation, 1) grabs just the first character of the occupation — "Doctor" becomes "D", "Singer" becomes "S". ORDER BY Name sorts the whole thing alphabetically by name, which is why "AnActorName" and "AProfessorName" can end up interleaved in the output rather than grouped by occupation.

SELECT 'There are a total of ' || COUNT(*) || ' ' || LOWER(Occupation) || 's.' AS summary
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(*) ASC, Occupation ASC;

This is a plain GROUP BY with two things stacked on top: building a sentence with string concatenation, and a two-part ORDER BY. ORDER BY COUNT(*) ASC, Occupation ASC sorts by the count first — so the rarest occupation comes first — and only falls back to sorting alphabetically by Occupation when two occupations have the exact same count, breaking the tie. LOWER(Occupation) handles the "lowercase" requirement regardless of how the data is actually capitalized in the table.

With the earlier sample data (1 Doctor, 3 Professors, 2 Singers, 3 Actors), this produces:

There are a total of 1 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

Actor and Professor tie at 3, so they fall back to alphabetical order — "Actor" sorts before "Professor," so it comes first even though both have the same count.

Window functions High-value

ROW_NUMBER, RANK, LAG, LEAD — calculate across a set of rows related to the current row, without collapsing them like GROUP BY does.

What

A calculation across a group of related rows, without squashing them into one row like GROUP BY does.

Where

In a SELECT list, for things like ranking, running totals, or comparing to the previous row.

When

When you need per-row detail AND an aggregate-style calculation at the same time.

How

function() OVER (PARTITION BY column ORDER BY column).

SELECT
  customer_id,
  order_date,
  total_amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq,
  LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount,
  RANK() OVER (ORDER BY total_amount DESC) AS amount_rank
FROM orders;

• ROW_NUMBER() — unique sequential number, no ties.
• RANK() — ties share a rank, next rank skips (1,1,3).
• DENSE_RANK() — ties share a rank, no gap (1,1,2).
• LAG/LEAD — look at the previous/next row's value (great for month-over-month deltas).
• Classic use: "find the latest record per customer" via ROW_NUMBER() ... QUALIFY (or a wrapping subquery in Postgres, since Postgres lacks QUALIFY).

"Latest record per customer" worked example — this exact pattern shows up constantly in real pipelines, and it's how you dedupe (Week 4) a table down to one current row per key:

SELECT * FROM (
  SELECT
    customer_id,
    address,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_addresses
) ranked
WHERE rn = 1;

Read this one from the inside out. The inner query numbers the rows 1, 2, 3... starting over at 1 for every new customer_id, with the newest row always getting rn = 1 (thanks to ORDER BY updated_at DESC). The outer query then keeps only the rows where rn = 1. You can't filter on a window function directly inside WHERE, so wrapping everything in a subquery like this is the normal way to do it in Postgres.

Running total example — a second extremely common pattern, cumulative revenue per customer over time:

SELECT
  customer_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW just means "every row from the start of this group up to the current one." That's actually already the default when you add ORDER BY to a window, so SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) alone gives you the same running total. Writing the frame out by hand becomes useful once you want something narrower, like ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average.

Date functions

What

Functions for working with dates and timestamps — rounding, extracting parts, doing date math.

Where

Anywhere a column holds a date/time value.

When

Whenever you need to group by month, find a day of the week, or add/subtract time.

How

Use DATE_TRUNC to round, EXTRACT to pull out a part, and +/- INTERVAL to shift a date.

SELECT
  order_date,
  DATE_TRUNC('month', order_date) AS order_month,
  EXTRACT(DOW FROM order_date) AS day_of_week,
  order_date + INTERVAL '30 days' AS due_date,
  AGE(NOW(), order_date) AS time_since_order
FROM orders;

• DATE_TRUNC — round down to a unit (day/month/year) — essential for grouping time series.
• EXTRACT — pull a specific field (year, month, dow) out of a timestamp.
• INTERVAL arithmetic — add/subtract durations directly.
• Know the difference between TIMESTAMP and TIMESTAMPTZ — always prefer TIMESTAMPTZ in real pipelines to avoid timezone bugs.

Worked example — a common reporting need is a complete daily series even for days with zero orders, which plain GROUP BY order_date can't produce because it only returns days that actually appear in the table:

SELECT
  d.day::date AS report_date,
  COALESCE(SUM(o.total_amount), 0) AS revenue
FROM generate_series(
  '2026-08-01'::date, '2026-08-18'::date, INTERVAL '1 day'
) AS d(day)
LEFT JOIN orders o ON DATE_TRUNC('day', o.order_date) = d.day
GROUP BY d.day
ORDER BY d.day;

generate_series builds a full list of calendar days, even ones with no orders at all. The LEFT JOIN then keeps every single day from that list, matched or not, and COALESCE turns any missing total into a plain 0 instead of a blank. This is the standard fix whenever a report is missing days, weeks, or months instead of showing them as zero.

Indexes

What

A shortcut structure that helps the database find rows fast, instead of checking every row.

Where

Attached to a column (or columns) on a table.

When

When a column is filtered, joined, or sorted on often, especially on a large table.

How

CREATE INDEX name ON table(column).

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date DESC);
CREATE UNIQUE INDEX idx_customers_email ON customers(email);

• An index is a sorted lookup structure so Postgres doesn't scan every row — huge win on large tables, filtered/joined columns.
• Default index type is B-tree — good for equality and range queries. Other types (GIN, GiST, BRIN) exist for JSON, full-text, geospatial.
• Indexes speed up reads but slow down writes (every INSERT/UPDATE must update the index too) — don't over-index.
• Index columns used in WHERE, JOIN, and ORDER BY — not columns you rarely filter on.

Composite and partial indexes — most real indexing decisions involve more than one column, or only need to cover part of the table:

-- composite index: speeds up queries filtering on BOTH columns together,
-- and also on customer_id alone (leftmost-prefix rule) — but NOT on order_date alone
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- partial index: only indexes the rows that matter for a specific hot query,
-- smaller and cheaper to maintain than indexing the whole table
CREATE INDEX idx_orders_pending ON orders(order_date) WHERE order_status = 'pending';

Here's the detail that trips people up: an index on (customer_id, order_date) can help a query that filters on customer_id alone, or on both columns together — but it can't help a query that only filters on order_date. The order you list the columns in matters. Put whichever column you filter on most often (or filter with = rather than a range) first.

Query optimization

What

Techniques for making a slow query run faster.

Where

Applied to any query that's taking longer than expected.

When

When a report or pipeline step is noticeably slow, or before shipping a query that will run often.

How

Run EXPLAIN ANALYZE to see what the database is actually doing, then fix the slow part (usually a missing index or a full table scan).

EXPLAIN ANALYZE
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id;

• EXPLAIN shows the planned execution; EXPLAIN ANALYZE actually runs it and shows real timings — learn to read "Seq Scan" vs "Index Scan".
• Avoid SELECT * in production queries — pull only the columns you need.
• Filter as early as possible; push predicates down before joining large tables.
• Watch for implicit type casts on join/filter columns — they silently disable index usage.

Reading real EXPLAIN ANALYZE output — here's what you'd see for the query above on an indexed vs. unindexed table:

-- without an index on order_date:
Seq Scan on orders  (cost=0.00..1834.00 rows=52000 width=12) (actual time=0.021..14.302 rows=51823 loops=1)
  Filter: (order_date >= '2026-01-01')
  Rows Removed by Filter: 148177
Planning Time: 0.112 ms
Execution Time: 16.891 ms

-- with an index on order_date:
Index Scan using idx_orders_date on orders  (cost=0.43..612.10 rows=52000 width=12) (actual time=0.034..3.112 rows=51823 loops=1)
  Index Cond: (order_date >= '2026-01-01')
Planning Time: 0.098 ms
Execution Time: 3.876 ms

Seq Scan means Postgres checked every single row in the table, one by one, throwing out anything that didn't match — here, that's 148,177 wasted row checks. Index Scan means it skipped straight to the matching rows. The cost numbers are just the planner's guess (useful for comparing two plans); actual time is real, measured milliseconds — which is why you need EXPLAIN ANALYZE, not just EXPLAIN, to see it.

This kind of problem has a name: the N+1 query problem. It happens when you run one query to get a list of orders, then loop through that list and run a separate query for each order's line items — one query, plus N more. The fix is almost always to combine it into a single JOIN, or one query using WHERE order_id IN (...), instead of looping.

Transactions

What

A way to group several database changes so they all succeed together, or all fail together.

Where

Around any multi-step write — updating more than one row or table as one logical action.

When

Whenever a partial update would leave your data in a broken or inconsistent state.

How

BEGIN, run your statements, then COMMIT to save them or ROLLBACK to undo them.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
-- or ROLLBACK; on failure

• ACID: Atomicity, Consistency, Isolation, Durability — the guarantees a transaction gives you.
• Isolation levels (Read Committed, Repeatable Read, Serializable) trade off consistency vs concurrency — Postgres defaults to Read Committed.
• In pipelines: wrap multi-step writes (e.g. load + update watermark) in a transaction so a failure doesn't leave partial state.

Here's why atomicity matters: imagine the first UPDATE (taking $100 from account 1) works, but the second one (adding it to account 2) fails partway through — say the connection drops. Without a transaction, that $100 would just vanish: deducted from one account, never added to the other. Wrapping both statements in BEGIN...COMMIT guarantees Postgres applies both changes together, or neither at all. There's no broken in-between state anyone could ever see.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- if a downstream check fails, roll back just this part without losing the whole transaction:
-- ROLLBACK TO SAVEPOINT before_credit;

COMMIT;

SAVEPOINT creates a rollback point inside a transaction — useful when you want to undo one step of a multi-step pipeline write without discarding everything that came before it in the same transaction.

Views

What

A saved query that behaves like a table when you query it.

Where

Anywhere you'd otherwise repeat the same complex query over and over.

When

When several people or reports need the same “shape” of data without copy-pasting SQL.

How

CREATE VIEW name AS SELECT ...; then just SELECT * FROM name.

CREATE VIEW customer_ltv AS
SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id;

CREATE MATERIALIZED VIEW customer_ltv_cached AS
SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id;
-- REFRESH MATERIALIZED VIEW customer_ltv_cached;

• A regular VIEW is a saved query — always up to date, re-executed each time it's queried.
• A MATERIALIZED VIEW physically stores results — faster reads, but needs manual/scheduled REFRESH.
• Conceptually this is what dbt models do: turn a SQL definition into a table or view in the warehouse.

Here's the practical difference: query customer_ltv (the regular view) right after adding a new order, and you'll see it reflected immediately — Postgres just re-runs the SELECT every time. Query customer_ltv_cached (the materialized view) and you'll see old numbers until someone runs REFRESH MATERIALIZED VIEW — it's a saved snapshot, not a live query. Use a regular view when you always need fresh data. Use a materialized view when the query is slow and slightly outdated numbers are fine, like a dashboard that only needs to update once an hour.

-- refresh without blocking readers (requires a unique index on the materialized view)
CREATE UNIQUE INDEX idx_customer_ltv_cached_id ON customer_ltv_cached(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_ltv_cached;

Adding CONCURRENTLY stops the materialized view from locking up while it refreshes. Without it, any query that tries to read the view has to wait until the refresh finishes — a nasty surprise the first time someone else's query gets stuck behind yours.

Practice project — E-commerce database

Build this schema locally in PostgreSQL and use it for every exercise above.

What

A hands-on e-commerce database to practice everything on this page.

Where

A local PostgreSQL database (or a free online one).

When

Right after learning a new concept above — practice on real tables, not just theory.

How

Create the tables shown below, insert a few rows, then write queries against them.

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  customer_name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  signup_date DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT,
  price NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  order_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  order_status TEXT NOT NULL DEFAULT 'pending',
  total_amount NUMERIC(10,2)
);

CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(order_id),
  product_id INT REFERENCES products(product_id),
  quantity INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL
);

CREATE TABLE payments (
  payment_id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(order_id),
  payment_method TEXT,
  amount NUMERIC(10,2),
  paid_at TIMESTAMPTZ
);

Seed it with a handful of rows so every query above has real data to run against:

INSERT INTO customers (customer_name, email, signup_date) VALUES
  ('Asha Rao', 'asha@example.com', '2026-01-15'),
  ('Ravi Shah', 'ravi@example.com', '2026-02-03'),
  ('Meera Nair', 'meera@example.com', '2026-03-20');

INSERT INTO products (product_name, category, price) VALUES
  ('Wireless Mouse', 'electronics', 25.00),
  ('Standing Desk', 'furniture', 350.00),
  ('Notebook Set', 'stationery', 12.50);

INSERT INTO orders (customer_id, order_status, total_amount) VALUES
  (1, 'completed', 375.00),
  (1, 'completed', 12.50),
  (2, 'cancelled', 25.00);

Three worked problems to show the pattern — solve these, then write 47 more of your own:

-- 1. Top N customers by lifetime spend (completed orders only)
SELECT c.customer_name, SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_status = 'completed'
GROUP BY c.customer_name
ORDER BY lifetime_value DESC
LIMIT 10;

-- 2. Products that have never been ordered (anti-join via LEFT JOIN + IS NULL)
SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL;

-- 3. Customers who churned: signed up, but no order in the last 90 days
SELECT c.customer_name, MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_name
HAVING MAX(o.order_date) < NOW() - INTERVAL '90 days' OR MAX(o.order_date) IS NULL;

Problem 2 uses a pattern called an "anti-join": LEFT JOIN from the table you want everything from, then keep only the rows where nothing matched on the other side (that's what IS NULL is checking). This pattern comes up constantly — "customers with no orders," "products nobody's viewed," "employees with no manager."

NoSQL Databases Beyond SQL

Not every problem fits neatly into rows and tables. NoSQL databases give up some of SQL's structure in exchange for flexibility and easier scaling.

What

A family of databases that don't use the traditional table-of-rows model — data can be stored as documents, simple key-value pairs, wide columns, or graphs instead.

Where

Usually alongside your SQL database, not instead of it — for the specific parts of a system that need a flexible schema or very high write volume.

When

When your data doesn't fit a fixed schema well, or you need to scale far beyond what one Postgres server can comfortably handle.

How

Pick the "shape" that matches how you'll access the data — document for nested/variable records, key-value for simple fast lookups, wide-column for huge write-heavy tables, graph for relationship-heavy data.

"NoSQL" isn't one thing — it's an umbrella term for four different data shapes. Here's each one, using the same e-commerce order from your practice project above, so you can see how the same real-world data looks completely different depending on the database.

1. Document databases — e.g. MongoDB

Instead of splitting an order across five normalized tables (orders, customers, order_items...), a document database stores the whole thing as one flexible, JSON-like document.

// one order document — customer info and line items are embedded right inside it
db.orders.insertOne({
  order_id: 1042,
  customer: { id: 7, name: "Asha Rao", email: "asha@example.com" },
  items: [
    { sku: "MOUSE-1", qty: 2, price: 25.0 },
    { sku: "DESK-1", qty: 1, price: 350.0 }
  ],
  status: "completed",
  order_date: ISODate("2026-08-17")
});

// query it back — no JOIN needed, everything is already together
db.orders.find({ "customer.id": 7, status: "completed" });

Beginner-friendly way to think about it: a document database is like storing each order as its own self-contained JSON file. That's great when every order can carry slightly different fields (a gift order might have a "gift_message" field that regular orders don't) — something a fixed SQL schema makes awkward.

2. Key-value stores — e.g. Redis, DynamoDB

The simplest possible model: a key maps to a value, nothing more. Extremely fast, because there's no query planning — just a direct lookup.

# Redis: cache an already-computed customer lifetime value so you don't
# recalculate it from Postgres on every single page load
SET customer:7:ltv 1249.50 EX 3600   # store it, expire automatically after 1 hour
GET customer:7:ltv                    # instant lookup by key

Think of it like a giant dictionary/hash map that lives outside your application and can be shared by many servers at once — perfect for caching, session storage, or anything where you always look things up by one exact key.

3. Wide-column stores — e.g. Cassandra, HBase, Bigtable

Built to accept an enormous number of writes per second across many machines — the kind of volume you'd see reading straight off a Kafka topic (Week 11).

CREATE TABLE orders_by_customer (
  customer_id int,
  order_id int,
  order_date timestamp,
  total_amount decimal,
  PRIMARY KEY (customer_id, order_date)
) WITH CLUSTERING ORDER BY (order_date DESC);

SELECT * FROM orders_by_customer WHERE customer_id = 7 LIMIT 10;

This looks almost like SQL (the query language is called CQL, deliberately SQL-like) — but under the hood it's optimized purely for "write huge amounts, read by a known key," not for flexible ad-hoc queries or joins.

4. Graph databases — e.g. Neo4j

Stores data as nodes (things) and edges (relationships) — the natural fit when the connections between records matter as much as the records themselves.

// Cypher: find products frequently bought together with what customer 7 ordered
MATCH (c:Customer)-[:PLACED]->(o:Order)-[:CONTAINS]->(p:Product)
WHERE c.id = 7
RETURN p.name, count(*) AS times_ordered
ORDER BY times_ordered DESC;

A "products frequently bought together" or "friends of friends" query is a nightmare of self-joins in SQL, but a short, natural query in a graph database — because relationships are first-class, not something you reconstruct with foreign keys every time.

SQL vs NoSQL Decision guide

In practice you'll rarely pick just one. Most real systems use SQL for the core transactional data and reach for NoSQL only where it clearly wins.

What

A side-by-side comparison to help you decide which kind of database fits a given piece of a system.

Where

At design time, when you're deciding how to store a new type of data — not something you want to rethink after the system is already built.

When

Ask this question before writing any code — switching a live system's storage model later is expensive and risky.

How

Match the tool to your data's shape and your read/write pattern, not to what's trendy. Many systems genuinely need both.

A simple rule of thumb:

• Reach for SQL when relationships between things matter (orders belong to customers, which have addresses...), when you need multiple rows to update together correctly (a payment and an order status), or when the data's shape stays consistent row to row.
• Reach for NoSQL when you need to handle a massive, fast stream of writes, when each record's shape genuinely varies (one product has 3 attributes, another has 30), or when your access pattern is simple — "give me everything for this one key," with no need for joins.

This approach has a name: polyglot persistence — using more than one type of database in the same system, each doing what it's best at. Your own capstone project already does this, even without calling it that: Postgres/Redshift stores the modeled warehouse tables (Week 4), while the vector database in the RAG pipeline is its own specialized NoSQL-style store, built just for searching embeddings. Neither one replaces the other — they're solving two different problems inside the same system.