Advanced SQL Techniques
Master window functions, CTEs, query optimization, and advanced aggregations used by senior data engineers.
260
Lessons
25
Sections
8
Industry datasets
63+
Free preview lessons
∞
Lifetime access
Curriculum
Foundations & Setup
Window Functions
- How Window Functions Work
- ROW_NUMBER() — Unique Row Numbering
- RANK() and DENSE_RANK() — Handling Ties
- NTILE() — Percentile Buckets
- Running Totals with SUM() OVER
- Moving Averages and Frame Clauses
- LAG() and LEAD() — Accessing Adjacent Rows
- FIRST_VALUE(), LAST_VALUE() and NTH_VALUE()
- PERCENT_RANK() and CUME_DIST() — Statistical Distribution
- Named Windows — The WINDOW Clause
- Window Functions with GROUP BY — The Execution Order Trap
- ROWS vs RANGE vs GROUPS — Frame Modes in Depth
- Multi-Step Analysis — CTEs and Window Functions Together
- Gap and Island Problems
- Sessionization — Grouping Events into Sessions
- Conditional Running Totals — Resets, Caps, and Flags
- Top-N Per Group — Patterns and Performance
- Period Comparison Patterns — YoY, MoM, and Rolling Windows
- Window Function Performance — Plans, Pitfalls, and Optimization
Common Table Expressions
- Practice Datasets — Overview
- What is a CTE — Basic Syntax and Mental Model
- Multiple CTEs — Chaining and Reuse
- CTEs for Data Cleaning and Transformation
- Step-by-Step Aggregation with CTEs
- Recursive CTEs — Fundamentals
- Recursive CTEs — Hierarchical Data
- Recursive CTEs — Graph Traversal
- MATERIALIZED CTEs and Performance
- Funnel Analysis with CTEs
- Cohort Analysis with CTEs
- Gap Filling and Forward-Fill with CTEs
- Pivot and Unpivot Patterns with CTEs
- Data Validation and Anomaly Detection with CTEs
- DML with CTEs — UPDATE, DELETE, INSERT
- CTEs Combined with Window Functions
- Period Comparison Patterns — YoY, MoM, WoW
Materialized Views & Incremental Computation
- Views vs Materialized Views vs CTEs — Choosing the Right Tool
- REFRESH MATERIALIZED VIEW — Full vs CONCURRENTLY
- Indexing Materialized Views — Accelerating Pre-Aggregated Data
- Incremental Refresh — Updating Only What Changed
- Materialized View Chains — Multi-Layer Aggregation Pipelines
- Auto-Refresh with pg_cron and Triggers
- Mat Views for Dashboard Caching — A Complete Production Example
- When to Use What — Mat Views vs CTEs vs Temp Tables vs Views
- Dependency Tracking and Safe Deployment of Materialized Views
- Production Patterns — Size, Monitoring and When to Abandon Mat Views
Full Text Search — Complete System
- How PostgreSQL FTS Works — tsvector, tsquery, and the @@ Operator
- Ranking Results — ts_rank and ts_rank_cd
- Phrase Search and Proximity — The <-> Operator
- ts_headline — Highlighting Matches in Search Results
- FTS Indexes — GIN vs GiST, Size and Update Cost
- Custom Text Search Configurations — Synonyms, Stop Words and Thesaurus
- Multilingual Full Text Search
- Combining FTS with Structured Filters — Hybrid Search
- FTS vs pg_trgm — When to Use Which
- Building a Complete Search System — Putting It All Together
Advanced Aggregations
- GROUPING SETS — Multiple GROUP BY Levels in One Query
- ROLLUP — Hierarchical Subtotals
- CUBE — Multi-Dimensional Cross-Tabulation
- FILTER Clause — Conditional Aggregation
- Ordered-Set Aggregates — Percentiles and Mode
- Statistical Aggregates — Correlation, Regression, and Variance
- STRING_AGG and ARRAY_AGG — Aggregating Into Collections
- Hypothetical-Set Aggregates — Rank and Percentile for Hypothetical Values
- Advanced HAVING and Post-Aggregation Filtering
- Advanced Aggregation Patterns — Combining Multiple Techniques
JSON & Array Operations
- JSON vs JSONB — Data Types, Storage, and When to Use Each
- Querying and Filtering JSONB — Operators, Paths, and Indexes
- Modifying JSONB — Building, Updating, and Merging Documents
- Working with JSONB Arrays — UNNEST, Aggregation, and Lateral Joins
- JSONB for Event Logging and Audit Trails
- JSON Aggregation — row_to_json, json_agg, and Building API Responses
- PostgreSQL Native Arrays — Operations, Indexing, and Patterns
- Advanced JSONB Patterns — Schema Validation, Transformation, and Migration
- Full-Text Search Integration with JSONB and Arrays
- JSONB Performance and Production Patterns
Long Queries — Production-Grade SQL
- What Makes a Query Production-Grade — Reading Long SQL
- The SaaS Monthly Business Review — MRR Waterfall, Cohort Retention, and Quick Ratio
- Hospital Patient Flow Dashboard — LOS, Readmissions, Capacity, and Cost
- Supply Chain Health Report — Inventory Risk, Supplier SLA, and Route Cost
- HR Workforce Snapshot — Org Tree, Pay Equity, Attrition Risk, and Headcount
- E-commerce Revenue Attribution — Multi-Touch Funnel, Cohort LTV, and A/B Test Analysis
Time Series Analytics — Advanced SQL Patterns
- Setting Up the Time Series Dataset
- Date Spine & Gap Filling — Never Miss a Missing Row
- Rolling Windows — Moving Averages, Bollinger Bands & Momentum
- Gaps and Islands — Detecting Continuous Runs & Outages
- Sessionization — Reconstructing User Sessions from Events
- Period-over-Period Comparisons — WoW, MoM, YoY in One Query
- Anomaly Detection — Z-Score and IQR Methods in Pure SQL
- Resampling — Downsampling and Upsampling Time Series
- Linear Regression & Trend Forecasting in SQL
- Full Time Series Dashboard — Multi-Signal Composite Query
Query Optimization — Complete Guide
- Understanding EXPLAIN
- Index Strategies
- Reading EXPLAIN — Query Plans from Start to Finish
- Index Types and Strategies — B-tree, GIN, GiST, BRIN, and Hash
- Query Rewriting — CTEs, Subqueries, and Lateral Joins Compared
- work_mem, parallel queries, and Configuration Tuning
- Vacuuming, Bloat, and Autovacuum Tuning
- Advanced Index Strategies — Partial, Composite, Covering, and Functional
- pg_stat_statements — Production Query Monitoring and Slow Query Analysis
- Connection Pooling, PgBouncer, and Managing Connections at Scale
- Identifying and Fixing N+1 Queries and Missing Join Optimizations
- Bulk Operations — COPY, INSERT...SELECT, and Efficient Batch Processing
Expert PostgreSQL — The Hard Stuff
- Range Types — Overlap Detection with daterange, tsrange & int4range
- SELECT FOR UPDATE SKIP LOCKED — Race-Free Job Queues
- LATERAL Joins — Per-Row Subqueries and Top-N Without Window Functions
- MERGE — Conditional Upsert, Delete, and Insert in One Statement
- crosstab() — Dynamic Pivot Tables with the tablefunc Extension
- Fuzzy Matching — pg_trgm, levenshtein & Similarity Search
- Bitemporal Tables — Valid Time, Transaction Time & History Queries
- unnest WITH ORDINALITY — Array Index Preservation & Multi-Array Joins
- Exclusion Constraints — Database-Enforced No-Overlap Rules
- Advanced Recursion — Bill of Materials, Cost Rollup & Shortest Path
Partitioning and Table Design
- Table Partitioning — RANGE, LIST, and HASH
- Partitioning Patterns — Time-Series, Multi-Tenant, and Archival
- Table Design Patterns — Normalisation Trade-offs and Schema Evolution
- Partition Indexes — Local vs Global and Query Routing
- Partitioning for Multi-Tenancy — Isolation, Performance, and Maintenance
- Table Design for Write-Heavy Workloads — HOT Updates, Fill Factor, and Bloat
- Generated Columns, Check Constraints, and Defensive Schema Design
- Tablespaces and Storage Management — Tiering Data Across Storage Classes
- Zero-Downtime Schema Migrations — Adding Columns, Constraints, and Indexes
- Data Archival and Lifecycle Management
Stored Procedures and PL/pgSQL
- PL/pgSQL Fundamentals — Functions, Variables, and Control Flow
- Triggers — Automating Audit Trails and Derived Data
- Dynamic SQL and Stored Procedures
- Error Handling and Exception Management in PL/pgSQL
- Cursors — Row-by-Row Processing for Large Result Sets
- Row-Level Security — Enforcing Access Control in the Database
- Advanced PL/pgSQL Patterns — Polymorphic Functions and Meta-Programming
- LISTEN/NOTIFY — Event-Driven Database Programming
- Security Definer Functions and Privilege Escalation Patterns
- Performance Profiling PL/pgSQL — auto_explain, pg_stat_user_functions, and Optimisation
Stock Market Analytics
- Setting Up the Stock Market Dataset
- Introduction to Stock Market Data
- Daily Returns and Price Changes
- Moving Averages and Trend Analysis
- Volatility and Risk Metrics
- Technical Indicators — RSI, MACD, Bollinger Bands
- Portfolio Analysis and Correlation
- Dividend Analysis and Total Return
- 52-Week Highs, Lows, and Breakout Detection
- Earnings and Volume Pattern Analysis
- Sector Rotation and Market Breadth
Sports Analytics
- Setting Up the Sports Analytics Dataset
- Introduction to Sports Analytics with SQL
- League Table and Standings Calculations
- Player Performance Metrics and Comparisons
- Head-to-Head Analysis and Derby Rivalries
- Scoring Patterns and Match Statistics
- Player Transfers and Career Tracking
- Season Comparison and Historical Records
- Winning and Losing Streaks
- Fantasy Sports and Player Ratings
- Match Prediction Features with SQL
Finance & Invoicing Analytics
- Setting Up the Finance Dataset
- Introduction to Finance and Invoicing Data
- Accounts Receivable and Aging Analysis
- Cash Flow Analysis and Projections
- Invoice Analytics and Billing Patterns
- Client Segmentation and Lifetime Value
- Payment Methods and Discount Analysis
- Financial KPIs and Executive Dashboards
- Revenue Recognition and Deferred Revenue
- Reconciliation and Audit Queries
- Budget vs Actual Variance Analysis
Real-World Patterns & Recipes
- Setting Up the Real-World Analytics Dataset
- RFM Customer Segmentation
- Cohort Retention and Churn Analysis
- Product Analytics and Recommendation Signals
- A/B Testing Analysis with SQL
- Time Intelligence — Rolling, Cumulative, and Period Queries
- Geographic and Segmentation Analysis
- Event Funnel and Conversion Analysis
- Session Analysis and User Journeys
- Retention Curve and Churn Prediction
- Revenue Attribution and Multi-Touch Models
Healthcare & Hospital Analytics
- Setting Up the Healthcare Dataset
- Admissions Analysis — Volume, Length of Stay, and Readmissions
- Diagnosis Patterns — ICD Code Analysis and Comorbidities
- Lab Results — Abnormal Value Detection and Patient Trending
- Staff Utilisation — Capacity, Workload, and Ward Occupancy
- Medication Analysis — Prescribing Patterns and Polypharmacy
- Procedure Cost Analysis — Revenue, Insurance Mix, and Cost Drivers
- Patient Outcome Analysis — Discharge Disposition
- Temporal Patterns — Seasonal Trends and Time-of-Day Analysis
- Readmission Risk Scoring — Multi-Factor Patient Stratification
SaaS & Subscription Analytics
- Setting Up the SaaS Analytics Dataset
- MRR Waterfall — New, Expansion, Contraction, Churn, Reactivation
- Cohort Retention Matrix — Monthly Cohort Analysis
- Customer Lifetime Value — LTV Calculation and Channel Attribution
- Churn Analysis — Rates, Timing, and Leading Indicators
- Feature Adoption and Product Engagement
- Trial Conversion Analysis — Funnel and Time-to-Convert
- ARR, Quick Ratio, and Revenue Forecasting
- Payment Failure and Recovery Analysis
- Expansion Revenue — Upsell Patterns and Account Growth
Logistics & Supply Chain
- Setting Up the Logistics & Supply Chain Dataset
- Inventory Reorder Analysis — Identifying Stock Below Reorder Point
- Supplier SLA Performance — Lead Time and Reliability
- Shipment Tracking — On-Time Delivery & Carrier Performance
- Safety Stock Calculation and Warehouse Capacity Analysis
- Recursive CTE — Building a Shipment Event Timeline
- Recursive CTE — Shipment Event Timeline Reconstruction
- Purchase Order Analysis — Order Cycle and Supplier Spend
- Network Flow Analysis — Which Warehouses Ship the Most?
- Total Cost of Ownership — Landed Cost and SKU Profitability
HR & Workforce Analytics
- Setting Up the HR & Workforce Analytics Dataset
- Recursive CTE — Org Hierarchy Traversal
- Headcount Trends & Attrition Analysis
- Compensation & Pay Equity Analysis
- Performance Calibration & Rating Distribution Analysis
- Tenure & Retention — Cohort Survival Analysis
- Promotion Velocity & Career Progression Analysis
- Manager Effectiveness — Team Metrics Rollup
- Workforce Planning — Headcount Forecasting & Gap Analysis
- Attrition Risk Scoring — Multi-Factor Early Warning Model
Twitter Data Exploration
- Setting Up the Twitter Dataset
- User Engagement — Reach, Resonance & Activity Patterns
- Hashtag Trend Analysis — Velocity, Peaks & Emerging Topics
- Follower Network — Graph Metrics & Influence Scoring
- Viral Content Detection — Retweet Cascades & Engagement Spikes
- Bot Detection — Behavioural Signals in SQL
- Reply Threads — Conversation Depth & Top Discussions
- Content Performance — What, When & How to Post
- Mention Networks & Hashtag Co-occurrence Analysis
- Full Twitter Analytics Dashboard — Multi-Signal Summary
Python × SQL — Dynamic Queries & Pipelines
- psycopg2 Fundamentals — Safe Connections & Parameterised Queries
- Dynamic Filter Builder — Safe WHERE Clauses from User Input
- pandas Integration — SQL Results into DataFrames & Back
- Batch Operations — executemany vs execute_values vs COPY
- Jinja2 SQL Templating — Conditional Blocks & Reusable Fragments
- Query Builder Class — Chainable, Reusable SQL in Python
- SQLAlchemy Core — Type-Safe Queries Without the ORM
- Async Queries — Concurrent SQL with asyncpg & asyncio
- Alerting Pipeline — Scheduled Query → Threshold → Webhook
- ETL Mini-Pipeline — Extract, Transform, Load with Python & SQL
PostgreSQL 18 — New & Advanced Features
- PostgreSQL 18 Overview — What Changed and Why It Matters
- Virtual Generated Columns — Zero-Storage Computed Attributes
- Async I/O and io_uring — How PostgreSQL 18 Transforms Read Performance
- JSON_TABLE — Shredding JSON into Relational Rows
- Named NOT NULL Constraints — Deferrable and Introspectable
- COPY ... WHERE — Selective Bulk Loading
- pg_stat_io Improvements — Observing I/O at Depth
- Logical Replication Improvements in PostgreSQL 18
- Query Planner and Statistics Improvements in PostgreSQL 18
- OAuth 2.0 Authentication and Security Improvements in PostgreSQL 18
Bonus — Interview Prep & Career
- How to Use This Section — Interview Strategy and Preparation
- Interview Questions — Core PostgreSQL Concepts (With Answers)
- Interview Questions — Query Writing Challenges (With Solutions)
- Interview Questions — Schema Design Challenges (With Discussion)
- Interview Questions — Production Scenarios and Performance Debugging
- Resume Writing — SQL Skills Section (3 Variations)
- A Warm Thank You