SQLearning
Introduction to SQL Create and manage databases Query execution Advanced queries Language elements Functions Advanced elements

Advanced SQL queries

The SQL that comes up in interviews and in an analyst's daily work: step-by-step queries with CTEs, filters with EXISTS, rankings, comparisons with the previous row, running totals and pivot tables. Every example can be run and edited in your browser.

7 lessons 7 with examples you can run

  1. CTE in SQL: the WITH clause

    A CTE (Common Table Expression) is a named query defined with WITH and used as if it were a table. We will see how to write one, chain several and when to use a recursive CTE

  2. EXISTS and NOT EXISTS in SQL

    EXISTS checks whether a subquery returns at least one row. We will see how to use EXISTS and NOT EXISTS, why NOT IN breaks with NULL values and when to use each option

  3. Window functions in SQL: OVER and PARTITION BY

    Window functions compute aggregates without collapsing rows. We will see what OVER does, how PARTITION BY splits the calculation into groups and how they differ from GROUP BY

  4. ROW_NUMBER, RANK and DENSE_RANK in SQL

    Ranking functions number the rows of a result. We will see how ROW_NUMBER, RANK and DENSE_RANK differ when there are ties, how to get the top N of each group and what NTILE is for

  5. LAG and LEAD functions in SQL

    LAG and LEAD return the value from the previous or next row without a JOIN. We will see their syntax, the default value, how to use them per group and how to compute differences between rows, plus FIRST_VALUE and LAST_VALUE

  6. Running totals and moving averages in SQL

    How to compute a running total, a running total per group and a moving average with SUM and AVG as window functions. We will see the window frame with ROWS BETWEEN and the difference between ROWS and RANGE

  7. PIVOT and UNPIVOT in SQL Server

    PIVOT turns the values of a column into new columns, and UNPIVOT does the opposite. We will see the SQL Server syntax and the standard alternative with CASE inside SUM, which works in any database

What you will learn in this section

SELECT, GROUP BY and JOIN can answer almost any question, but some common ones get long and hard to read: each customer’s most expensive order, the change from the previous month or year-to-date sales. This section brings together the tools that solve them in a few lines.

  1. CTEs with WITH: writing a complex query in named steps.
  2. EXISTS and NOT EXISTS: filtering on what is (or isn’t) in another table, and why NOT IN breaks with NULL.
  3. OVER and PARTITION BY: what a window function is and how it differs from GROUP BY.
  4. ROW_NUMBER, RANK and DENSE_RANK: rankings, ties and the classic “top N per group”.
  5. LAG and LEAD: comparing each row with the previous and the next one.
  6. Running totals: running totals, moving averages and the ROWS BETWEEN frame.
  7. PIVOT: turning rows into columns, with SQL Server’s operator and with CASE.

Every lesson uses the course’s Customers and Orders tables and has runnable examples. Window functions are standard SQL: what you learn here works the same in SQL Server, PostgreSQL, MySQL 8, Oracle and BigQuery.