Advanced SQL (Database-Agnostic)
Duration: 4 days
TRAINING OVERVIEW
This 4-day program deepens participants’ SQL capability beyond CRUD and basic joins into
advanced querying, analytics, performance reasoning, and robust data manipulation
patterns. The training is database-agnostic: concepts and techniques are taught in a way
that transfers across major RDBMS platforms (PostgreSQL, Oracle, SQL Server, MySQL, etc.),
while highlighting common dialect differences and portability strategies. Participants will
practice writing readable, correct, and scalable SQL for reporting, analytics, and
transactional workloads.
TRAINING OBJECTIVES
By the end of the training, participants will be able to:
1. Write advanced SQL queries using CTEs, window functions, set operations, and
advanced joins safely and correctly.
2. Build analytic queries (ranking, cohorts, running totals, time-series, sessions, funnels)
using database-agnostic patterns.
3. Apply query performance reasoning (cardinality, join strategies, predicate pushdown,
sargability) and read execution plans at a conceptual level.
4. Implement robust data engineering SQL: deduplication, upserts/merge patterns,
incremental loads, audit columns, and data quality checks.
5. Design portable SQL by managing dialect differences (date functions, null-handling,
string ops, pagination, MERGE/UPSERT variants).
COURSE CONTENTS
Chapter 1 — Advanced Query Foundations and Readability Patterns
Advanced SELECT structure and query decomposition (layering and progressive
refinement)
Alias discipline, naming conventions, and readability standards for team SQL
Advanced filtering patterns
o Null-safe filtering and three-valued logic (TRUE/FALSE/UNKNOWN)
o Anti-patterns: implicit conversions, overusing SELECT *, non-deterministic
ordering
Advanced joins and join correctness
o Inner vs outer joins beyond the basics (null-extension effects)
o Semi-join and anti-join patterns (EXISTS, NOT EXISTS) vs IN/NOT IN pitfalls
o Join explosion and accidental many-to-many joins (how to detect and prevent)
Set operations and their real use cases
o UNION vs UNION ALL, INTERSECT, EXCEPT/MINUS (concept + portability notes)
o Reconciliation queries (comparing datasets)
Chapter 2 — Common Table Expressions (CTE) and Advanced Subquery Techniques
Non-recursive CTEs for modular SQL
o When CTE improves maintainability vs when it can harm performance
(conceptual optimizer behavior)
Subqueries: scalar, correlated, and derived tables
o Correlated subqueries: when they’re elegant vs when they become slow
o Transforming correlated patterns into join-based solutions
Lateral joins / APPLY (conceptual pattern; portability notes)
o “Row-by-row derived table” use cases (top-N per group, parsing, staged
calculations)
Recursive SQL (conceptual + examples)
o Hierarchies: org chart, bill of materials
o Graph-like patterns: path building, cycle awareness (dialect differences)
Guardrails for correctness
o Determinism, stable ordering, and ensuring reproducible outputs
Chapter 3 — Window Functions for Analytics and Reporting at Scale
Window function mental model: partitions, ordering, frames
Ranking and segmentation
o ROW_NUMBER, RANK, DENSE_RANK, NTILE (top-N, bucketing)
Running totals and moving calculations
o Cumulative sums, rolling averages, sliding windows (frame-based thinking)
Offset and navigation analytics
o LAG, LEAD, “previous/next event” problems (churn, transitions, sessionization)
De-duplication and “latest record per key” patterns (portable strategies)
Advanced aggregation patterns
o Conditional aggregation vs window aggregation (trade-offs)
Common window pitfalls
o Frame defaults, duplicate ordering keys, unintended ties, and performance
implications
Chapter 4 — Advanced Data Transformation Patterns (DB-Agnostic ETL in SQL)
Data shaping patterns
o Pivot/unpivot strategies (portable approaches vs vendor-specific PIVOT)
o Normalization vs denormalization for reporting
Time-series and calendar-driven reporting
o Gap filling (conceptual patterns), date dimension usage
o Period-to-date (MTD/QTD/YTD), trailing periods, fiscal calendars
Event and funnel analytics (portable patterns)
o Building event timelines, step funnels, conversion rates
o Sessionization patterns (time thresholds, sequence grouping)
Data quality checks in SQL
o Uniqueness, referential integrity checks, null rate, outlier checks
o Building “DQ dashboards” tables via SQL
Chapter 5 — Data Modification Beyond Basics: Safe DML, Upsert/Merge, and Incremental
Loads
Transaction fundamentals for SQL developers (portable concepts)
o Atomicity, isolation intuition, and “why my report changed” scenarios
Advanced INSERT patterns
o Insert-select, dedup insert, staging tables
Upsert strategies (portable)
o Conceptual MERGE / UPSERT design (key match, update columns, audit columns)
o Dialect mapping overview: MERGE, INSERT...ON CONFLICT, INSERT...ON
DUPLICATE KEY, etc.
Incremental loading patterns (ETL/ELT)
o Watermark columns, high-water mark tables, CDC-like strategies
o Late-arriving data handling and reprocessing windows
Deletion strategies
o Hard delete vs soft delete, tombstones, validity ranges (SCD-like patterns)
Concurrency pitfalls and how to avoid them conceptually
o Lost updates, double insert, race conditions around upserts
Chapter 6 — Query Performance and Execution Plan Reasoning (Cross-Database)
How databases execute queries (portable mental model)
o Parsing → optimizing → executing
o Cardinality estimates and why they matter
Common join strategies (conceptual)
o Nested loop, hash join, merge join: when each tends to appear
Sargability and predicate design
o Avoiding function-wrapped columns in filters, implicit casts, leading wildcards
Index usage fundamentals (conceptual, DB-agnostic)
o Composite indexes and leftmost prefix intuition
o Covering indexes concept (varies by DB)
Aggregation and sorting costs
o Why ORDER BY and DISTINCT can be expensive
Performance troubleshooting workflow
o Identify hotspot, reduce scanned rows early, verify join keys, limit early, stage
computations
Portable “performance hygiene” checklist
o LIMIT with deterministic order, avoid SELECT *, pre-aggregate, reduce row width
Chapter 7 — Portability and Dialect Strategy (Make SQL Truly DB-Agnostic)
Portable SQL guidelines
o Standard SQL-first approach: joins, CTEs, window functions, set ops
Dialect differences you must anticipate
o Date/time functions and interval arithmetic
o String concatenation and pattern matching
o Null-handling (COALESCE, NULLIF, IS DISTINCT FROM vs alternatives)
o Pagination patterns (OFFSET/FETCH vs LIMIT vs row-number wrapping)
Designing “compatibility layers”
o Views as abstraction, standardized naming, limited function surface area
Testing SQL portability
o Building a query test pack (golden outputs), edge cases, deterministic ordering
Documentation patterns for shared SQL assets
o Inline comments, rationale notes, performance assumptions
Chapter 8 — Applied Capstone: End-to-End Advanced SQL Casework (Integrated Practice)
Capstone scope (reporting + analytics + data engineering)
o Build a high-quality reporting dataset from normalized sources
o Produce analytic outputs: rankings, cohorts, time-series KPIs, and anomaly
checks
Required techniques to demonstrate
o CTE modularization, window functions, dedup strategy, incremental logic, and
performance reasoning
Deliverables
o Query pack (well-documented), results tables/views, and a short “SQL design
notes” document
Review rubric
o Correctness, portability, readability, performance, and maintainability