Skip to content
New this month 24 fresh C++, C#, F#, JavaScript, TypeScript and Lua courses just landed. Browse new releases Use code WELCOME10 for 10% off your first order · 14-day refund
WisdomCharms — home
Cart 0 items in cart
Data & Databases Intermediate

Window Functions and Advanced SQL Patterns

Running totals, rankings and gaps-and-islands, explained until they finally feel obvious.

Rated 5.0 out of 5 from 2 reviews 13 students

SN Created by Sofia Navarro

  • Last updated August 2026
  • English
  • 5h 20m of material
  • 40 lessons

What you will learn

8 concrete outcomes

Every bullet below is something you will have built, shipped or be able to explain by the time you finish the last lesson.

  • Explain partitions, ordering and frames, including what the default frame does
  • Choose between ROWS and RANGE deliberately and know why it matters
  • Rank with correct handling of ties using the right ranking function
  • Compare periods with LAG and LEAD instead of self-joining
  • Build running totals, moving averages and percentile buckets
  • Solve gaps and islands to find consecutive runs in event data
  • Deduplicate while keeping the correct row, not an arbitrary one
  • Build cohort and retention grids that a stakeholder can read

Course curriculum

6 modules · 40 lessons · 5h 20m of material

7 lessons running 52m in total. Each lesson ships with the finished source files and a short written recap, so you can follow along in your own editor and skim the module again later.

Lesson-by-lesson titles, code downloads and exercises live inside the course library you get access to straight after checkout.

7 lessons running 56m in total. Each lesson ships with the finished source files and a short written recap, so you can follow along in your own editor and skim the module again later.

Lesson-by-lesson titles, code downloads and exercises live inside the course library you get access to straight after checkout.

7 lessons running 58m in total. Each lesson ships with the finished source files and a short written recap, so you can follow along in your own editor and skim the module again later.

Lesson-by-lesson titles, code downloads and exercises live inside the course library you get access to straight after checkout.

7 lessons running 58m in total. Each lesson ships with the finished source files and a short written recap, so you can follow along in your own editor and skim the module again later.

Lesson-by-lesson titles, code downloads and exercises live inside the course library you get access to straight after checkout.

6 lessons running 50m in total. Each lesson ships with the finished source files and a short written recap, so you can follow along in your own editor and skim the module again later.

Lesson-by-lesson titles, code downloads and exercises live inside the course library you get access to straight after checkout.

6 lessons running 46m in total. Each lesson ships with the finished source files and a short written recap, so you can follow along in your own editor and skim the module again later.

Lesson-by-lesson titles, code downloads and exercises live inside the course library you get access to straight after checkout.

6 modules · 40 lessons

5h 20m total length

Requirements

Short list, and deliberately so. If you meet these you can start today.

  • You can write joins, GROUP BY and subqueries without help
  • PostgreSQL available locally or through the provided Docker setup
  • Some experience querying data that matters to someone

About this course

Window functions are the point at which SQL stops being a way to fetch rows and becomes a way to compute. Most developers learn just enough to copy a ROW_NUMBER example and never build the mental model that makes the rest available. This course builds that model properly.

It begins with the frame, because everything else follows from it. What a partition is, how ordering defines a frame, the difference between ROWS and RANGE, and what the default frame silently does when you omit it. Every concept is shown on a small table where you can verify the output by hand.

From there you work through the function families and the problems they solve: ranking and dense ranking with an honest treatment of ties, LAG and LEAD for period-over-period comparison, running totals and moving averages, first and last value with the frame trap that catches everyone, and percentiles and buckets for distribution work.

The second half is patterns rather than functions. Gaps and islands for finding consecutive runs. Deduplication that keeps the correct row rather than an arbitrary one. Sessionisation of event streams. Cohort and retention grids. Top N per group without a correlated subquery. Each pattern is presented as a recognisable problem shape first, then solved, then stress-tested against edge cases and against a common wrong solution that looks right.

Topics covered

Frequently asked questions

Still unsure about something? Write to misteryjj100@gmail.com and a human answers, usually the same working day.

Yes. Window functions are supported from MySQL 8 and MariaDB 10.2, and dialect notes accompany every pattern where the syntax differs from PostgreSQL.

At a practical level, including when a window function beats a self-join and when sorting for a partition becomes the dominant cost of a query.

Seventy graded exercises with worked solutions, plus a set of deliberately wrong solutions you are asked to diagnose before seeing the explanation.

Checkout is handled on our provider's secure payment page. The moment your payment clears we email your personal access link and access code to the address you used at checkout, and the same link appears in your account library. There is nothing to install and nothing to wait for.

Email misteryjj100@gmail.com within 14 days of your purchase, quote your order number, and we refund the full amount to your original payment method. No form to fill in and no questions about how much of the course you watched.

What students say

Reviews are written by people who bought this course. We publish the critical ones too.

5.0

Rated 5.0 out of 5

Course rating · 2 reviews

Rating distribution

  • 5 stars 100%
  • 4 stars 0%
  • 3 stars 0%
  • 2 stars 0%
  • 1 star 0%
  • OJ

    Ola Jørgensen

    Analytics engineer

    Jan 2026
    Rated 5.0 out of 5

    The frame clause finally makes sense

    ROWS against RANGE when there is a tie in the ordering column. I had been producing subtly wrong running totals for months without knowing why, and watching the frame boundaries drawn row by row fixed it for good.
  • CD

    Camille Dubois

    Data engineer

    Oct 2025
    Rated 5.0 out of 5

    Gaps and islands, demystified

    I had copied gaps-and-islands solutions off forums for years without understanding a single line of them. Numbering the rows twice, subtracting and grouping by the difference is one of those things that becomes obvious the moment somebody walks you through it slowly.

Your instructor

SN

Sofia Navarro

Python and data engineer

  • 231 students taught
  • 11 courses published
  • 4.5 instructor rating
  • Python
  • SQL
  • PostgreSQL
  • Data engineering

Sofia builds the pipelines that keep analytics teams honest, mostly Python on top of PostgreSQL under uncomfortable load. She teaches schema design, indexing and query planning against anonymised production datasets rather than toy tables, then wires the same data into pandas so the SQL and the Python halves stop being separate skills. Expect to spend real time reading execution plans and rather less time reading slides. She consults on migrations for teams that cannot afford downtime.

See every course by Sofia Navarro
$34 USD

One-time payment · lifetime access

The WisdomCharms dispatch

One useful email a week. No fluff, no spam.

New course releases, discount codes before anyone else, and a short, practical breakdown of one technique — a prompt pattern, a C++ idiom, a TypeScript trick — that you can use the same day.

  • Subscriber-only launch pricing
  • Unsubscribe in one click
  • We never sell your address

By subscribing you agree to our Privacy Policy. Questions? Write to misteryjj100@gmail.com.