Week 8: SQL for Analysts — Views, Optimization & Real Datasets

1. Creating & Using Views

A view is a saved query given a name — query it exactly like a table, and the database re-runs the underlying query behind the scenes every time:

CREATE VIEW monthly_sales_view AS
SELECT DATE_TRUNC('month', order_date) AS month,
       category,
       SUM(amount) AS total_amount,
       COUNT(*)    AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date), category;

-- From here on, query it like any other table
SELECT * FROM monthly_sales_view WHERE category = 'Electronics';

-- Replace its definition later without touching anything that queries it
CREATE OR REPLACE VIEW monthly_sales_view AS
SELECT DATE_TRUNC('month', order_date) AS month,
       category,
       SUM(amount) AS total_amount,
       COUNT(*)    AS order_count,
       AVG(amount) AS avg_order_value   -- added a column, nothing else changes
FROM orders
GROUP BY DATE_TRUNC('month', order_date), category;

Three things make views worth reaching for: they stop Week 6 and Week 7's more involved GROUP BY/CTE logic from being retyped in every new query that needs it; they let a database grant read access to just the view — hiding columns like raw customer emails without hiding the whole table; and, starting next week, they give Power BI a single clean object to import instead of a raw table plus a pile of transform steps.

2. Views vs. Materialized Views

A plain view stores nothing — it's purely the saved query text, re-executed in full on every read. A materialized view (supported in PostgreSQL; MySQL has no native equivalent) actually stores the query's results on disk, like a real table, and has to be told to refresh:

CREATE MATERIALIZED VIEW monthly_sales_snapshot AS
SELECT DATE_TRUNC('month', order_date) AS month, category, SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_TRUNC('month', order_date), category;

-- Reads are now instant -- no recomputation -- but the data is frozen
-- until this is run again:
REFRESH MATERIALIZED VIEW monthly_sales_snapshot;

The trade-off is always the same one: a plain view is always current but pays the full query cost on every read; a materialized view is fast to read but can be stale the moment new orders arrive, until someone (or something on a schedule) refreshes it.

3. Reading a Query Plan

EXPLAIN shows how the database actually intends to run a query, without running it — EXPLAIN ANALYZE runs it too and reports real timings. The detail matters far less than one thing: whether it says scan or seek on each table.

EXPLAIN SELECT * FROM orders WHERE customer_id = 482;

-- Seq Scan on orders  (cost=0.00..1834.00 rows=12 width=48)
--   Filter: (customer_id = 482)
-- ^ "Seq Scan" = every single row in orders was read and checked --
--   fine on a few thousand rows, expensive on a few million

A sequential scan (Seq Scan in PostgreSQL, ALL under MySQL's EXPLAIN) means the engine read every row in the table to find the matches — the plan-reading skill worth having day one isn't memorizing every plan node, it's recognizing that line and knowing what fixes it, which is exactly Section 4.

4. Indexes: What They Actually Do

An index is a separate, sorted structure the database maintains alongside a table, built on one or more columns, so it can jump straight to matching rows instead of scanning every one:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 482;

-- Index Scan using idx_orders_customer_id on orders
--   (cost=0.29..8.31 rows=12 width=48)
--   Index Cond: (customer_id = 482)
-- ^ Same query, same result -- the cost estimate just dropped by orders of magnitude

The columns worth indexing are the ones that repeatedly show up in a WHERE, a JOIN ... ON, or an ORDER BY — foreign key columns (like customer_id here) are the single most common case, since Week 5's joins filter and match on them constantly.

5. Guided Analysis: A Real Dataset

Put all three ideas together against the question "who are our top 5 customers by revenue this quarter, and how fast is their spending growing?" — using the orders and customers tables from Weeks 5–7:

-- Step 1: an index on the column this whole analysis filters by
CREATE INDEX idx_orders_order_date ON orders(order_date);

-- Step 2: a view that does the heavy lifting once
CREATE OR REPLACE VIEW customer_quarterly_totals AS
SELECT c.customer_id, c.customer_name,
       DATE_TRUNC('quarter', o.order_date) AS quarter,
       SUM(o.amount) AS total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, DATE_TRUNC('quarter', o.order_date);

-- Step 3: rank + compare quarters, reading from the view like a normal table
SELECT customer_name, quarter, total_amount,
       LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY quarter) AS prev_quarter,
       RANK() OVER (PARTITION BY quarter ORDER BY total_amount DESC) AS rank_in_quarter
FROM customer_quarterly_totals
WHERE quarter = DATE_TRUNC('quarter', CURRENT_DATE)
ORDER BY total_amount DESC
LIMIT 5;

Notice the shape: an index on the filter column (Section 4), a view that owns the join and aggregation once (Section 1), and Week 7's window functions layered on top of the view exactly as if it were a plain table. This is the pattern real analyst work actually looks like — not one clever query, but a few simple, named, reusable pieces stacked together.

6. Hands-on Exercise

7. Knowledge Check

Four quick questions. Expand each to check your answer.