PostgreSQL Querying: Advanced Techniques

Extend your SQL skills with advanced PostgreSQL techniques for real-world data problems.

Go beyond SQL basics and learn advanced PostgreSQL querying patterns used in production. You will compare multiple solution strategies, apply window functions, table expressions, and grouping techniques, and choose the best approach for correctness, readability, and performance.
- Refresher
- Table Expressions
- Set Operators
- Window Ranking, Offset and Aggregate Functions
- Pivoting and Grouping Sets
- Programming with PL/pgSQL

In this module we make sure everyone remembers the basics of querying so we can continue at the same level. Goals:
- Standard elements of a SELECT query
- Joins
- Grouping and Aggregating
- Subqueries

Table expressions are SQL elements that are not a physical table but can be used as if they were one. Goals:
- Views
- Set-returning Functions
- Derived Tables
- Common Table Expressions (CTE)
- LATERAL joins
- When to pick which option?

Set operators allow you to vertically combine and compare two similar result sets. This contrasts joins, where you combine data horizontally. Goals:
- UNION (ALL)
- EXCEPT / INTERSECT

Window functions are ideal for analytics scenarios like running totals and moving averages. They also allo…