Course overview

This course teaches practical SQL analysis using PostgreSQL syntax and behavior. Each chapter builds one core capability with clear outcomes, realistic tasks, and checkpoints that reflect work done in reporting and operational analytics.

Complete curriculum

Every chapter and lesson

40 lessons total

01
Chapter 1 · 4 lessons
SELECT, WHERE, and ORDER BY
Write clear PostgreSQL queries that pick needed columns, filter rows by business rules, and sort results for reporting.
Why it matters
Most analysis starts here. Reliable filtering and sorting prevent incorrect snapshots and reduce rework in dashboards.
By the end, you will be able to
- Select specific columns and aliases instead of using SELECT *.
- Filter rows with precise boolean logic in WHERE clauses.
- Sort output with ORDER BY using multiple sort keys.
1. 1.1
  Query shape and result sets
  Read a PostgreSQL SELECT statement top to bottom and predict output columns and rows.
2. 1.2
  Filtering with WHERE
  Apply comparison and logical operators to encode business conditions directly in SQL.
3. 1.3
  Sorting and tie-breaking
  Use ORDER BY with ascending or descending rules and deterministic tie-break columns.
4. 1.4
  Null-safe reporting basics
  Handle NULL values in predicates so record counts and filters stay accurate.
Study chapter 1 in detail
02
Chapter 2 · 4 lessons
Joins
Combine PostgreSQL tables with INNER and LEFT JOINs while preserving row grain, missing records, and trustworthy cross-table business totals.
Why it matters
Key metrics usually require data spread across entities such as users, orders, plans, and events.
By the end, you will be able to
- Choose INNER and LEFT joins based on whether unmatched rows must remain visible.
- Write explicit ON clauses that protect row cardinality.
- Diagnose duplicate rows caused by one-to-many joins.
1. 2.1
  Join types and result meaning
  Interpret how PostgreSQL join types include or exclude unmatched rows.
2. 2.2
  Join keys and grain
  Match tables on stable keys and check the grain of each dataset before joining.
3. 2.3
  Preventing accidental fan-out
  Use pre-aggregation or unique keys to avoid inflated counts after joins.
4. 2.4
  Readable multi-table SQL
  Alias tables clearly and keep join predicates separate from business filters.
Study chapter 2 in detail
03
Chapter 3 · 4 lessons
GROUP BY and HAVING
Aggregate PostgreSQL data into trustworthy metrics with grouped calculations and post-aggregate filters.
Why it matters
Team decisions often depend on rollups such as weekly totals, conversion by segment, and top categories.
By the end, you will be able to
- Group rows correctly and compute aggregates like COUNT, SUM, and AVG.
- Use HAVING to filter aggregated groups after calculation.
- Avoid mixing non-grouped columns with aggregates incorrectly.
1. 3.1
  Aggregation fundamentals
  Build grouped metrics with clear dimension columns and measure columns.
2. 3.2
  HAVING versus WHERE
  Apply WHERE before grouping and HAVING after grouping in PostgreSQL query order.
3. 3.3
  Distinct and conditional counts
  Use COUNT(DISTINCT ...) and CASE-based aggregates for cleaner KPI definitions.
4. 3.4
  Metric QA checks
  Validate grouped totals against known baselines to catch logic mistakes early.
Study chapter 3 in detail
04
Chapter 4 · 4 lessons
Subqueries
Use PostgreSQL subqueries for precise filtering, comparison, and existence checks when they are clearer than joins.
Why it matters
Some business questions are naturally phrased as nested logic, such as find customers above their segment average.
By the end, you will be able to
- Write scalar, table, and EXISTS subqueries for practical filters.
- Use correlated subqueries only when they improve clarity.
- Rewrite subqueries into joins when performance or readability benefits.
1. 4.1
  Subquery forms
  Differentiate scalar, IN, and EXISTS patterns in PostgreSQL.
2. 4.2
  Correlated logic
  Apply row-by-row correlation carefully and verify intent with sample outputs.
3. 4.3
  IN versus EXISTS
  Pick the form that expresses intent clearly and performs well for dataset size.
4. 4.4
  Refactoring nested queries
  Convert deep nesting into cleaner shapes when maintenance cost is high.
Study chapter 4 in detail
05
Chapter 5 · 4 lessons
CTEs
Structure multi-step PostgreSQL analysis with common table expressions so logic is easier to review and test.
Why it matters
Readable query structure reduces bugs when analytical logic grows beyond one short SELECT statement.
By the end, you will be able to
- Break complex logic into named CTE steps with WITH.
- Use CTEs to separate data prep, metric calculation, and final selection.
- Explain when CTE readability is the primary benefit in PostgreSQL.
1. 5.1
  WITH clause basics
  Define and reference CTE blocks in order.
2. 5.2
  Pipeline-style query design
  Build sequential steps that each do one job clearly.
3. 5.3
  Reusable metric staging
  Calculate intermediate metrics once and consume them safely downstream.
4. 5.4
  Performance awareness
  Use EXPLAIN to confirm that a readable CTE structure still performs acceptably.
Study chapter 5 in detail
06
Chapter 6 · 4 lessons
Window Functions
Apply PostgreSQL window functions to rank rows, compute running totals, and compare each row to group context.
Why it matters
Windows provide advanced analysis without collapsing detail rows, which is critical for trend and cohort work.
By the end, you will be able to
- Use PARTITION BY and ORDER BY inside OVER clauses correctly.
- Compute rankings, running sums, and lag or lead comparisons.
- Choose window frames that match analytical intent.
1. 6.1
  Window function anatomy
  Understand function, partition, order, and frame pieces in PostgreSQL window syntax.
2. 6.2
  Ranking patterns
  Use ROW_NUMBER, RANK, and DENSE_RANK for leaderboard and top-N analysis.
3. 6.3
  Running and moving metrics
  Build cumulative and rolling calculations with explicit frame definitions.
4. 6.4
  Comparative row analysis
  Use LAG and LEAD to measure change between adjacent events.
Study chapter 6 in detail
07
Chapter 7 · 4 lessons
Date-Time Analysis
Work safely with PostgreSQL dates, timestamps, intervals, and truncation for stable period reporting.
Why it matters
Time logic is a common source of reporting bugs, especially around time zones and period boundaries.
By the end, you will be able to
- Truncate timestamps into day, week, and month buckets.
- Apply interval arithmetic for retention and cycle-time metrics.
- Handle timezone-aware analysis using PostgreSQL timestamp types.
1. 7.1
  Time data types
  Distinguish date, timestamp, and timestamptz behavior in PostgreSQL.
2. 7.2
  Period bucketing
  Use date_trunc and casting patterns for consistent reporting windows.
3. 7.3
  Timezone correctness
  Convert and compare timestamps with explicit timezone assumptions.
4. 7.4
  Retention interval logic
  Measure elapsed time and lagged events with interval-aware SQL.
Study chapter 7 in detail
08
Chapter 8 · 4 lessons
Optimization Basics
Improve PostgreSQL query speed with indexing, selective predicates, and plan inspection before risky rewrites.
Why it matters
Fast, stable queries keep dashboards responsive and reduce infrastructure cost under growth.
By the end, you will be able to
- Read basic EXPLAIN output to spot expensive operations.
- Use indexes that match common filter and join predicates.
- Avoid anti-patterns that defeat index usage.
1. 8.1
  Plan-first optimization
  Inspect PostgreSQL execution plans before changing query structure.
2. 8.2
  Index strategy fundamentals
  Map frequent predicates to practical index definitions.
3. 8.3
  Sargable filter patterns
  Write predicates that allow index scans when possible.
4. 8.4
  Safe performance testing
  Benchmark with representative data and verify result equivalence.
Study chapter 8 in detail
09
Chapter 9 · 4 lessons
Data Modeling and Normalization
Design PostgreSQL tables with keys and constraints that reduce duplication and preserve data quality.
Why it matters
Solid schema design prevents update anomalies and makes analytics easier to trust over time.
By the end, you will be able to
- Define primary keys, foreign keys, and essential constraints.
- Apply normalization principles to reduce redundant storage.
- Balance normalized design with query readability and performance.
1. 9.1
  Relational modeling basics
  Model entities and relationships with clear table boundaries.
2. 9.2
  Normal forms in practice
  Use practical first, second, and third normal form checks.
3. 9.3
  Constraints as guardrails
  Enforce invariants with NOT NULL, UNIQUE, CHECK, and foreign keys in PostgreSQL.
4. 9.4
  Schema choices and analytics
  Evaluate how modeling decisions affect downstream query complexity.
Study chapter 9 in detail
10
Chapter 10 · 4 lessons
Transactions and ACID
Use PostgreSQL transaction control to keep multi-step updates consistent when failures or concurrent writes occur.
Why it matters
Business workflows such as checkout or inventory updates require all-or-nothing correctness.
By the end, you will be able to
- Explain ACID guarantees in practical PostgreSQL terms.
- Use BEGIN, COMMIT, and ROLLBACK for safe multi-step operations.
- Recognize isolation concerns in concurrent transaction scenarios.
1. 10.1
  Transaction lifecycle
  Control units of work explicitly with BEGIN, COMMIT, and ROLLBACK.
2. 10.2
  Atomic workflow design
  Group dependent SQL statements so partial updates cannot be committed.
3. 10.3
  Isolation and concurrency
  Understand how concurrent transactions can interact and why isolation levels matter.
4. 10.4
  Failure handling patterns
  Use transactional guards and retries safely in application-facing SQL paths.
Study chapter 10 in detail

SQL for Data Analysis

Course overview

Every chapter and lesson

SELECT, WHERE, and ORDER BY

Joins

GROUP BY and HAVING

Subqueries

CTEs

Window Functions

Date-Time Analysis

Optimization Basics

Data Modeling and Normalization

Transactions and ACID

Learn this with an AI teacher that starts from what you already know.