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.

How to actually learn this — practise, don't just copy

Every lesson shows you SQL you can run. The temptation is to copy and paste it into your editor, hit Run, and move on. Resist that. The single biggest difference between people who recognise SQL and people who write SQL is muscle memory with the syntax — and you only build that by typing the queries out yourself.

So when you see a query in a lesson:

  1. Read it carefully to understand what it is doing.
  2. Type it out yourself — not copy/paste — into your own editor or psql session.
  3. Run it, then change a column or a filter and run it again to see what shifts.

Yes, it's slower the first few times. That is the point. After a section or two, the patterns start to feel automatic — and that is when SQL stops being something you look up and starts being something you simply write.

Use the Step Through feature

For most query examples in this course, you'll see a Step Through button next to the SQL block. It walks you through the query in the order PostgreSQL actually executes it — FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, and finally ORDER BY and LIMIT. This is not the order you read the query top-to-bottom, and once you internalise the real execution order a lot of confusing behaviour (why your WHERE can't see an alias, why HAVING runs after grouping, why LIMIT happens last) suddenly makes sense.

Use Step Through whenever a query feels too dense to hold in your head. It is the single best tool in this course for visually seeing where to focus.

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:

-- ❌ Illustrative only — column_a/column_b are placeholders
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. */

The query results might differ

As we build queries and run random data generation, so it might happend that the query response that you see on the lesson might differ from what you get, so do not worry about it. The key thing is to learn how the results are derived.

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:

PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

Next →