absolutelearners.com

Getting Started

Welcome to Advance SQL Techniques

This course is for engineers and analysts who already know SQL — you can write a SELECT, filter with WHERE, join two tables, and group results. You're not a beginner.

What you probably haven't mastered yet is the layer above that: the features that turn a competent SQL writer into someone who can answer genuinely hard questions in a single query, and do it fast.

That's what this course covers.

What You'll Be Able to Do After This Course

By the end you'll be comfortable with:

  • Window functions — rank rows, compute running totals, compare each row to its neighbours, all without losing detail
  • CTEs — break complex queries into readable, testable steps
  • Advanced aggregations — multi-dimensional GROUP BY, conditional aggregates, collapsing rows into arrays
  • Query optimisation — read an EXPLAIN plan, spot performance killers, and fix them
  • JSON in PostgreSQL — query and index semi-structured data
  • Concurrency — understand isolation levels and write safe concurrent queries

How the Course Is Structured

Each section covers one major topic. Within a section:

  1. Concept — what it is and the mental model behind it
  2. Syntax — the exact SQL with annotated output
  3. Worked examples — realistic scenarios, not toy data
  4. Practice exercise — a problem for you to solve before reading the solution

You don't need to follow sections in order after Section 2 (Window Functions) — each section is self-contained.

What Database Are We Using?

All examples use PostgreSQL 15+. Most syntax also works in other databases (BigQuery, Snowflake, DuckDB) but we'll note when something is Postgres-specific.

You don't need to install anything if you don't want to — the next lesson gives you a ready-made practice schema you can run anywhere, including free cloud options.

A Note on Style

Queries in this course follow a consistent style:

SELECT
    column_a,
    column_b,
    SUM(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY column_a, column_b
ORDER BY total DESC;
Keywords in UPPER CASE. One clause per line. Aliases that describe what the value means. This isn't a style war — it's just what we'll use so examples are easy to scan.

Prerequisite

This is an advanced-level course, so it is recommended that you have a solid understanding of SQL queries and related concepts. For a more detailed foundation, you can refer to:

https://www.udemy.com/course/postgresqlmasterclass/

Next →