Go to main content
Back to Courses
MODULE 14

Advanced SQL for Analytical Systems

Use SQL as a reasoning language, not just extraction, with patterns for cohorts, funnels, experiments, and performance.

Editorial cover of the Advanced SQL for Analytical Systems module
Index - Mini-series on Window Functions - official lesson image on GinnyTech, created by AD
1
Advanced 18 min

Execution order, logical plans, and query thinking

Execution order, logical plans, and query thinking. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Window Functions: OVER, PARTITION BY and analytic logic - official lesson image on GinnyTech, created by AD
2
Advanced 18 min

Advanced joins, semi-join, anti-join, and set logic

Advanced joins, semi-join, anti-join, and set logic. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
ROW_NUMBER, RANK and group deduplication - official lesson image on GinnyTech, created by AD
3
Advanced 18 min

'Window functions: mental model'

Window functions: mental model. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
LAG, LEAD and sequential event analysis - official lesson image on GinnyTech, created by AD
4
Advanced 18 min

Ranking, lag/lead, cumulative logic, and frames

Ranking, lag/lead, cumulative logic and frames. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
SUM, COUNT, AVG, MIN, and MAX over windows - official lesson image on GinnyTech, created by AD
5
Advanced 22 min

Cohort analysis in SQL

Cohort analysis in SQL. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
FIRST_VALUE, LAST_VALUE, NTILE and window frames - official lesson image on GinnyTech, created by AD
6
Advanced 22 min

Funnel analysis in SQL

Funnel analysis in SQL. Core lesson of the Advanced SQL for Analytical Systems module with real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Guided exercises on Window Functions - official lesson image on GinnyTech, created by AD
7
Advanced 18 min

Sessionization and behavioral grouping

Sessionization and behavioral grouping. Core lesson of the Advanced SQL for Analytical Systems module with real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Date Spine, Rolling Metrics, and OHLC - official lesson image on GinnyTech, created by AD
8
Advanced 22 min

Experiments and A/B analysis in SQL

Experiments and A/B analysis in SQL. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Cohort and Retention Analysis in SQL - official lesson image on GinnyTech, created by AD
9
Advanced 18 min

Attribution queries and path analytics

Attribution queries and path analytics. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Funnel, Sessionization and User Paths - official lesson image on GinnyTech, created by AD
10
Advanced 18 min

JSON, arrays, and semi-structured analytics

JSON, arrays and semi-structured analytics. Core lesson of the Advanced SQL for Analytical Systems module with real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Pivot, ROLLUP and KPI Table for Reporting - official lesson image on GinnyTech, created by AD
11
Advanced 18 min

Date-time pitfalls and timezone correctness

Date-time pitfalls and timezone correctness. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Anomalies, Pareto, and Segmentation with SQL - official lesson image on GinnyTech, created by AD
12
Advanced 18 min

EXPLAIN, optimization, and performance tuning

EXPLAIN, optimization, and performance tuning. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Cheat Sheet - Window Functions for Analytics - official lesson image on GinnyTech, created by AD
13
Advanced 22 min

Testing, refactoring, and reusable SQL patterns

Testing, refactoring, and reusable SQL patterns. Core lesson of the Advanced SQL for Analytical Systems module with a real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson
Case Study - Window Functions for an Omnichannel Retailer - official lesson image on GinnyTech, created by AD
14
Advanced 28 min

'Advanced lab: professional queries on real cases'

Advanced lab: professional queries on real cases. Core lesson of the Advanced SQL for Analytical Systems module with real problem, conceptual model, rigorous formalization, applied case, 3-level lab, and final checkpoint.

Prerequisites: 1

Go to lesson

Module connections

Natural prerequisiteInfrastructure & Ops for Data Systems7 lessons

Bring operational discipline from serious engineering to data workloads, deploy, observability, security, and incident response.

Same stage of the pathMathematics for Data Analysis10 lessons

Introduces the mathematical rigor that makes models, inference, forecasting, and applied regressions more readable.

Same stage of the pathAnalytics Engineering with dbt and Semantic Layer13 lessons

Transform raw data into reliable, reusable, and governed models with layering, tests, contracts, and semantic layer.

Same stage of the pathStatistical Significance, A/B Testing, and Experimentation Science10 lessons

Take experimentation to a serious level with causal design, power planning, variance reduction, and governance.

Previous moduleCourse Overview and Study Method for Data Work8 lessons

Transform the course from catalog to cognitive system, with study method, technical memory, and professional output plan.

Previous moduleData Collection & Tracking Systems8 lessons

Move from isolated tags and tools to reliable, governed, and observable measurement systems.