PostgreSQL in Practice: Joins, JSONB, Window Functions & Views
Join and reshape data the PostgreSQL way with LATERAL, DISTINCT ON and recursive CTEs, query arrays and JSONB, rank and compare rows with window functions, and build views, materialized views and upserts, with every answer checked as you go.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
This intermediate course teaches the PostgreSQL features that make everyday reporting shorter and safer. In 20 hands-on labs you join tables with USING and LATERAL, pick the latest row per group with DISTINCT ON, walk category trees with recursive CTEs and fill calendar gaps with generate_series. You query text arrays with @>, && and ANY, unnest them without double counting, and read JSONB documents with ->, ->>, ? and containment, including the indexes that serve them. Window functions cover ranking, running totals, frames and LAG/LEAD, and the course ends with views, materialized views and INSERT ... ON CONFLICT upserts. Every lab shows real SQL next to fictional data; you work out the exact result and get every answer checked.
Lessons
- Joins in PostgreSQL: USING, Aliases and a First ReportFree
- LEFT JOIN, COALESCE and the FILTER Clause Locked
- DISTINCT ON: The Latest Row per Group Locked
- LATERAL Joins: Top N Rows per Group Locked
- CTEs: Building a Report in Readable Steps Locked
- WITH RECURSIVE: Walking a Category Tree Locked
- generate_series: Reports Without Missing Days Locked
- Arrays: @>, && and ANY Locked
- unnest, array_agg and the Double-Counting Trap Locked
- JSONB: ->, ->>, ? and @> Locked
- JSONB Arrays to Rows: jsonb_to_recordset Locked
- GIN Indexes and Selectivity for JSONB and Arrays Locked
- Window Functions: ROW_NUMBER, RANK, DENSE_RANK Locked
- Running Totals and Window Frames: RANGE vs ROWS Locked
- LAG and LEAD: Month-over-Month Change Locked
- Shares and Top N per Group with Window Aggregates Locked
- Views: Saved Queries That Are Always Current Locked
- Materialized Views: Fast but Stale Locked
- UPSERT with INSERT ... ON CONFLICT Locked
- Final Project: A Customer Report in One Query Locked