Course Preview

Advanced SQL for Analytics

Advanced SQL for analytics is the set of query techniques - window functions, multi-level grouping, recursive queries, and set operations - that compute rankings, running totals, and period comparisons directly inside a relational database, instead of in application code.

itData engineering and analytics | OpenSkills.info

Course pathWalk it in order

Look it upDip in anytime

Go furtherLeaves this page

Advanced SQL for Analytics

Basic SQL retrieves and filters rows. Advanced SQL for analytics computes across rows: rankings, running totals, period-over-period comparisons, subtotal hierarchies, and reshaped result sets, all inside the query itself instead of in application code or a spreadsheet. The techniques in this course - window functions, QUALIFY, GROUPING SETS/ROLLUP/CUBE, common table expressions including recursive ones, set operations, and PIVOT/UNPIVOT - are what separate a query that lists facts from one that answers an analytical question directly.

Window functions: calculating across related rows without collapsing them

A GROUP BY aggregate collapses many rows into one row per group. A window function keeps every row and adds a value computed from a set of related rows. The OVER clause is what makes a function a window function; it is required, and it is the syntax that tells the engine to keep rows individual while still calculating across them.

SELECT depname, empno, salary,
       avg(salary) OVER (PARTITION BY depname) AS dept_avg
FROM empsalary;

PARTITION BY divides the rows into groups that share the same values, similar to GROUP BY, but each row keeps its own columns alongside the computed value. ORDER BY inside OVER sets the logical order the function processes rows in; it does not change the order of the query's output. Adding ORDER BY also changes which rows the function sees by default: with an order, the default window is every row from the start of the partition through the current row - useful for running totals - while omitting ORDER BY uses the entire partition for every row.

Window functions fall into three groups:

Continue the course

This section is part of the paid course.

See pricing to subscribe, or log in if you already have access.

Where this skill leads

Relevant careers

See how this topic contributes to broader role-level skill maps.

Sources