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

Don't Panic: Advanced SQL for Analytics

Advanced SQL for analytics is what happens when a query stops listing rows and starts answering a question. Rankings, running totals, subtotals, hierarchy walks, set comparisons, and reshaped reports can all happen inside the database. This is excellent news, provided you remember that the database is very literal and has never been impressed by your spreadsheet habits.

The load-bearing idea is the window function, which calculates across related rows while keeping each row in the result. A GROUP BY aggregate collapses rows. A window function adds context beside them. The OVER clause tells the engine how to partition rows, how to order them, and which frame of nearby rows the function can read.

The frame is where many surprises live. A running total wants rows from the start of the partition through the current row. LAST_VALUE often needs a wider frame, because the default frame may end at the current row. The name sounds confident. The frame has the receipts.

Filtering also has timing. Window functions are computed after WHERE, so a database cannot filter on a value that does not exist yet. Some engines provide QUALIFY, which filters window results directly. Others need the old reliable wrapper: compute in a subquery or CTE, then filter outside.

The rest of the course is a set of controlled superpowers. GROUPING SETS produce several subtotal levels in one result. Recursive CTEs walk trees without knowing their depth first. UNION, INTERSECT, and EXCEPT combine result sets, sometimes with deduplication you did not mean to ask for. PIVOT turns values into columns, which is useful and occasionally how reports become furniture.

Start with the Practice Reference when writing queries. Use the Exercise to build a layered report and check where each clause runs. Keep the Cheatsheet open for dialect differences, especially QUALIFY, frames, recursion guards, and set operators. The point is not fancy SQL. The point is a query whose analytical answer is still correct after every convenient shortcut has been interrogated.

Where this skill leads

Relevant careers

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

Sources