Module 5: SQL for Data Analysis | Data Analyst

"SQL is the lingua franca of data. Every database speaks it, every data job requires it, and it hasn't gone out of style in 50 years."

Learning Objectives

By the end of this module, you will be able to:

Write SELECT queries with filtering, sorting, and aggregation
Combine tables using different JOIN types
Use subqueries and Common Table Expressions (CTEs) for complex analysis
Apply window functions for running totals, rankings, and comparisons
Write CASE statements for conditional logic in queries
Explain basic query optimization principles

Why SQL Matters

Python is great for modeling; data usually lives in databases. SQL lets you filter and aggregate before you pull gigabytes into memory, and it’s what you’ll whiteboard in interviews.

Examples use SQLite (stdlib); syntax transfers to PostgreSQL, MySQL, BigQuery, etc.

You want to…	SQL idea
Shrink data early	`WHERE`, `GROUP BY` in the database
Combine entities	`JOIN`
Rank or running sum	Window functions
Readable multi-step logic	CTEs

1. SELECT Fundamentals

SELECT lists columns (or expressions), FROM names the table. Aliases (AS) keep results readable. DISTINCT dedupes rows. LIMIT caps rows while exploring.

Clause (typical order)	Role
`SELECT`	Choose columns / expressions
`FROM`	Source table(s)
`WHERE`	Filter rows before grouping
`GROUP BY`	Bucket rows
`HAVING`	Filter buckets after aggregation
`ORDER BY`	Sort result
`LIMIT`	Trim rows (explore / page)

Try This! Write SELECT 1 style sanity queries in a new database before touching production tables — confirms connectivity and permissions.

WHERE is your row filter: comparisons, AND/OR, IN (...), BETWEEN, LIKE for patterns, IS NULL for missing. Bind parameters in apps to avoid injection — in analysis, still prefer tools that parameterize.

3. Aggregation and GROUP BY

Aggregate functions (SUM, AVG, COUNT, MIN, MAX) collapse groups. Every non-aggregated column in SELECT must appear in GROUP BY (in standard SQL). HAVING filters after aggregation (like WHERE for groups).

Clause	When
`WHERE`	Filter raw rows
`GROUP BY`	Define buckets
`HAVING`	Filter buckets

4. JOINs — Combining Tables

Join	Keeps
`INNER`	Matches in both
`LEFT`	All left + matching right
`RIGHT`	All right + matching left
`FULL OUTER`	Everything (SQLite simulates with UNION tricks)

Keys must be comparable types; watch for one-sided NULLs after outer joins.

5. Subqueries

A query nested in WHERE, FROM, or SELECT. Powerful but can get hard to read — often clearer as a CTE.

6. Common Table Expressions (CTEs)

WITH step AS (SELECT ...) SELECT ... FROM step — build pipelines top to bottom. Great for readability and reuse of intermediate results.

7. Window Functions

OVER (PARTITION BY ... ORDER BY ...) computes per-row results without collapsing rows like GROUP BY.

Pattern	Example use
`ROW_NUMBER()`	Top-N per group
`SUM() OVER`	Running total
`LAG` / `LEAD`	Prior / next row value
`AVG() OVER`	Moving average frame

8. CASE Statements

SQL’s if/else: CASE WHEN condition THEN x ... ELSE y END. Use for bucketing, flags, and conditional aggregates.

9. Query Optimization Basics

Filter early; fewer rows = less work
Indexes on join and filter columns (DBA territory but know the idea)
Avoid SELECT * in production pipelines
Explain plans (EXPLAIN) show what the engine actually does

Fun Fact: The best “optimization” is often not pulling columns you’ll never use.

Key Example: Join + aggregate + window rank — the shape of many “top per category” reports.

sql

[object Object],
  d.dept_name,
  e.name,
  e.salary,
  ,[object Object],() ,[object Object], (,[object Object], ,[object Object], e.department ,[object Object], ,[object Object], e.salary ,[object Object],) ,[object Object], sal_rank
,[object Object], employees e
,[object Object], departments d ,[object Object], e.department ,[object Object], d.dept_name
,[object Object], e.salary ,[object Object], ,[object Object],;

Analyst cheat sheet: SQL ↔ English

English ask	SQL shape
“Last 90 days of sales”	`WHERE sale_date >= CURRENT_DATE - INTERVAL '90' DAY` (dialect varies)
“Revenue by region”	`SELECT region, SUM(amount) … GROUP BY region`
“Top 10 customers by spend”	`ORDER BY total_spend DESC` + `LIMIT 10` or window `RANK`
“Customers with no orders”	`LEFT JOIN` + `WHERE orders.id IS NULL`
“Same period last year”	Self-join or date functions on shifted dates

Readability tip: Format SQL with leading commas or snake_case aliases — pick a team style and stay consistent. Future-you reads WITH monthly AS (...) faster than nested subqueries six levels deep.

Dialect gotchas: string concat (|| vs + vs CONCAT), date literals, boolean types, and LIMIT/TOP/FETCH syntax differ slightly — check docs when switching engines.

Practice Exercises

Exercise 1: Basic Queries (Beginner)

Select, filter, sort, and limit on a single table.

Exercise 2: JOINs and Aggregation (Intermediate)

Revenue per employee with department names; handle employees with no sales.

Exercise 3: Window Functions (Intermediate)

Running total of sales by employee over time; rank products within region.

Exercise 4: Complex CTE Analysis (Advanced)

Multi-CTE pipeline: staging clean sales → monthly aggregates → YoY comparison.

Exercise 5: Full Business Report (Advanced)

One query (or CTE chain) answering executive questions with comments in SQL.

Mini-Project: Sales Performance Dashboard Query Set

A small set of named queries: KPI totals, trend by month, top reps, product mix — each readable via CTEs, ready to plug into BI tools.

Key Takeaways

Push filters and aggregates to SQL when you can.
Know inner vs left join — mismatched keys create NULLs by design.
CTEs document steps; window functions avoid self-join hacks.
CASE buckets business logic cleanly in the database.

Resources for Further Learning

SQLite SQL introduction
Mode SQL Tutorial
PostgreSQL SELECT docs
Use The Index, Luke — indexing intuition

Key Takeaway

Filter and aggregate in SQL to shrink data before Python.
Join with a clear key story: what happens to non-matches?
Use CTEs to make multi-step logic readable and testable.
Apply window functions for ranks and running metrics without collapsing rows.
Express business rules with CASE and document assumptions in comments.

Next up: Module 6 — Data Visualization — turning query results into insight people actually see.