/Python & Data Analytics/Phase 8

Phase 8Days 85-96

Advanced SQL & Analytical Data Modeling

Express sequences, cohorts, windows, and governed metric-ready tables

Phase Goal

Build reusable SQL layers using windows, dimensional models, cohorts, and performance-aware design.

Progress

Notes

Open the written lectures for this course before checking off the phase topics.

Projects

Community Bicycle Share Reliability Mart

Day 85: Window Functions Mental Model

Build the first-principles mental model for Rows retained within partitions; name the question it answers and the unit of analysisPredict and trace a tiny example of Rows retained within partitions before inspecting the resultApply Rows retained within partitions to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Rows retained within partitions; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for OVER, PARTITION BY, ORDER BY, and frame; name the question it answers and the unit of analysisPredict and trace a tiny example of OVER, PARTITION BY, ORDER BY, and frame before inspecting the resultApply OVER, PARTITION BY, ORDER BY, and frame to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind OVER, PARTITION BY, ORDER BY, and frame; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for ROW_NUMBER, RANK, and DENSE_RANK; name the question it answers and the unit of analysisPredict and trace a tiny example of ROW_NUMBER, RANK, and DENSE_RANK before inspecting the resultApply ROW_NUMBER, RANK, and DENSE_RANK to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind ROW_NUMBER, RANK, and DENSE_RANK; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Default frames and unstable ties; name the question it answers and the unit of analysisPredict and trace a tiny example of Default frames and unstable ties before inspecting the resultApply Default frames and unstable ties to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Default frames and unstable ties; diagnose the visible failure and perform the corrective check

Retrieve one recent mechanism and one older prerequisite before consulting notesComplete a guided example, then reconstruct its critical path from a blank artifactSolve a changed task that requires choosing among already-taught toolsRecord the decision changed, failure tested, help used, and adaptive 1/3/7/14/30-day review cue

Day 86: Running & Rolling Measures

Build the first-principles mental model for Running totals; name the question it answers and the unit of analysisPredict and trace a tiny example of Running totals before inspecting the resultApply Running totals to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Running totals; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for ROWS versus RANGE; name the question it answers and the unit of analysisPredict and trace a tiny example of ROWS versus RANGE before inspecting the resultApply ROWS versus RANGE to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind ROWS versus RANGE; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Moving averages and incomplete windows; name the question it answers and the unit of analysisPredict and trace a tiny example of Moving averages and incomplete windows before inspecting the resultApply Moving averages and incomplete windows to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Moving averages and incomplete windows; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Irregular time and false smoothing; name the question it answers and the unit of analysisPredict and trace a tiny example of Irregular time and false smoothing before inspecting the resultApply Irregular time and false smoothing to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Irregular time and false smoothing; diagnose the visible failure and perform the corrective check

Day 87: Lag, Lead & Change

Build the first-principles mental model for LAG and LEAD; name the question it answers and the unit of analysisPredict and trace a tiny example of LAG and LEAD before inspecting the resultApply LAG and LEAD to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind LAG and LEAD; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Absolute, percentage, and point change; name the question it answers and the unit of analysisPredict and trace a tiny example of Absolute, percentage, and point change before inspecting the resultApply Absolute, percentage, and point change to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Absolute, percentage, and point change; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for First-row handling; name the question it answers and the unit of analysisPredict and trace a tiny example of First-row handling before inspecting the resultApply First-row handling to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind First-row handling; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Zero division and incompatible periods; name the question it answers and the unit of analysisPredict and trace a tiny example of Zero division and incompatible periods before inspecting the resultApply Zero division and incompatible periods to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Zero division and incompatible periods; diagnose the visible failure and perform the corrective check

Day 88: Cohorts & Retention

Build the first-principles mental model for Cohort definition and first event; name the question it answers and the unit of analysisPredict and trace a tiny example of Cohort definition and first event before inspecting the resultApply Cohort definition and first event to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Cohort definition and first event; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Activity periods and cohort age; name the question it answers and the unit of analysisPredict and trace a tiny example of Activity periods and cohort age before inspecting the resultApply Activity periods and cohort age to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Activity periods and cohort age; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Retention denominators and censoring; name the question it answers and the unit of analysisPredict and trace a tiny example of Retention denominators and censoring before inspecting the resultApply Retention denominators and censoring to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Retention denominators and censoring; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Reactivation and incomplete cohorts; name the question it answers and the unit of analysisPredict and trace a tiny example of Reactivation and incomplete cohorts before inspecting the resultApply Reactivation and incomplete cohorts to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Reactivation and incomplete cohorts; diagnose the visible failure and perform the corrective check

Day 89: Funnels & Event Sequences

Build the first-principles mental model for Eligibility and ordered steps; name the question it answers and the unit of analysisPredict and trace a tiny example of Eligibility and ordered steps before inspecting the resultApply Eligibility and ordered steps to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Eligibility and ordered steps; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Conditional stage counts; name the question it answers and the unit of analysisPredict and trace a tiny example of Conditional stage counts before inspecting the resultApply Conditional stage counts to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Conditional stage counts; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Timing and repeated events; name the question it answers and the unit of analysisPredict and trace a tiny example of Timing and repeated events before inspecting the resultApply Timing and repeated events to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Timing and repeated events; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Changing denominators; name the question it answers and the unit of analysisPredict and trace a tiny example of Changing denominators before inspecting the resultApply Changing denominators to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Changing denominators; diagnose the visible failure and perform the corrective check

Day 90: Recursive CTEs & Hierarchies

Build the first-principles mental model for Anchor and recursion; name the question it answers and the unit of analysisPredict and trace a tiny example of Anchor and recursion before inspecting the resultApply Anchor and recursion to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Anchor and recursion; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Parent-child traversal; name the question it answers and the unit of analysisPredict and trace a tiny example of Parent-child traversal before inspecting the resultApply Parent-child traversal to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Parent-child traversal; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Calendar generation; name the question it answers and the unit of analysisPredict and trace a tiny example of Calendar generation before inspecting the resultApply Calendar generation to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Calendar generation; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Cycles and runaway recursion; name the question it answers and the unit of analysisPredict and trace a tiny example of Cycles and runaway recursion before inspecting the resultApply Cycles and runaway recursion to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Cycles and runaway recursion; diagnose the visible failure and perform the corrective check

Day 91: Facts, Dimensions & Grain

Build the first-principles mental model for Fact events and declared grain; name the question it answers and the unit of analysisPredict and trace a tiny example of Fact events and declared grain before inspecting the resultApply Fact events and declared grain to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Fact events and declared grain; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Dimensions and conformed meaning; name the question it answers and the unit of analysisPredict and trace a tiny example of Dimensions and conformed meaning before inspecting the resultApply Dimensions and conformed meaning to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Dimensions and conformed meaning; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Star versus normalized source; name the question it answers and the unit of analysisPredict and trace a tiny example of Star versus normalized source before inspecting the resultApply Star versus normalized source to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Star versus normalized source; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Mixed-grain facts; name the question it answers and the unit of analysisPredict and trace a tiny example of Mixed-grain facts before inspecting the resultApply Mixed-grain facts to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Mixed-grain facts; diagnose the visible failure and perform the corrective check

Day 92: Slowly Changing Dimensions

Build the first-principles mental model for Current versus historical questions; name the question it answers and the unit of analysisPredict and trace a tiny example of Current versus historical questions before inspecting the resultApply Current versus historical questions to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Current versus historical questions; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Type 1 and Type 2; name the question it answers and the unit of analysisPredict and trace a tiny example of Type 1 and Type 2 before inspecting the resultApply Type 1 and Type 2 to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Type 1 and Type 2; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Effective dates and as-of joins; name the question it answers and the unit of analysisPredict and trace a tiny example of Effective dates and as-of joins before inspecting the resultApply Effective dates and as-of joins to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Effective dates and as-of joins; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Overlaps and late changes; name the question it answers and the unit of analysisPredict and trace a tiny example of Overlaps and late changes before inspecting the resultApply Overlaps and late changes to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Overlaps and late changes; diagnose the visible failure and perform the corrective check

Day 93: Metric SQL & Semantic Contracts

Build the first-principles mental model for Name, definition, grain, filters, and owner; name the question it answers and the unit of analysisPredict and trace a tiny example of Name, definition, grain, filters, and owner before inspecting the resultApply Name, definition, grain, filters, and owner to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Name, definition, grain, filters, and owner; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Base measures and ratios; name the question it answers and the unit of analysisPredict and trace a tiny example of Base measures and ratios before inspecting the resultApply Base measures and ratios to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Base measures and ratios; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Reusable tested views; name the question it answers and the unit of analysisPredict and trace a tiny example of Reusable tested views before inspecting the resultApply Reusable tested views to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Reusable tested views; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Conflicting denominators; name the question it answers and the unit of analysisPredict and trace a tiny example of Conflicting denominators before inspecting the resultApply Conflicting denominators to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Conflicting denominators; diagnose the visible failure and perform the corrective check

Day 94: Indexes & Performance

Build the first-principles mental model for B-tree and composite order; name the question it answers and the unit of analysisPredict and trace a tiny example of B-tree and composite order before inspecting the resultApply B-tree and composite order to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind B-tree and composite order; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Scan, seek, sort, join, and aggregate signals; name the question it answers and the unit of analysisPredict and trace a tiny example of Scan, seek, sort, join, and aggregate signals before inspecting the resultApply Scan, seek, sort, join, and aggregate signals to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Scan, seek, sort, join, and aggregate signals; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Sargable predicates; name the question it answers and the unit of analysisPredict and trace a tiny example of Sargable predicates before inspecting the resultApply Sargable predicates to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Sargable predicates; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Wrong-query optimization and write cost; name the question it answers and the unit of analysisPredict and trace a tiny example of Wrong-query optimization and write cost before inspecting the resultApply Wrong-query optimization and write cost to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Wrong-query optimization and write cost; diagnose the visible failure and perform the corrective check

Day 95: Transactions & Safe Writes

Build the first-principles mental model for ACID intuition; name the question it answers and the unit of analysisPredict and trace a tiny example of ACID intuition before inspecting the resultApply ACID intuition to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind ACID intuition; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for BEGIN, COMMIT, ROLLBACK, and staging; name the question it answers and the unit of analysisPredict and trace a tiny example of BEGIN, COMMIT, ROLLBACK, and staging before inspecting the resultApply BEGIN, COMMIT, ROLLBACK, and staging to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind BEGIN, COMMIT, ROLLBACK, and staging; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Upsert and idempotent loads; name the question it answers and the unit of analysisPredict and trace a tiny example of Upsert and idempotent loads before inspecting the resultApply Upsert and idempotent loads to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Upsert and idempotent loads; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Partial and duplicate batches; name the question it answers and the unit of analysisPredict and trace a tiny example of Partial and duplicate batches before inspecting the resultApply Partial and duplicate batches to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Partial and duplicate batches; diagnose the visible failure and perform the corrective check

Day 96: Phase Project — Bicycle Reliability Mart

Build the first-principles mental model for Trip, station, maintenance, and weather grains; name the question it answers and the unit of analysisPredict and trace a tiny example of Trip, station, maintenance, and weather grains before inspecting the resultApply Trip, station, maintenance, and weather grains to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Trip, station, maintenance, and weather grains; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Dimensional model and governed metrics; name the question it answers and the unit of analysisPredict and trace a tiny example of Dimensional model and governed metrics before inspecting the resultApply Dimensional model and governed metrics to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Dimensional model and governed metrics; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Windowed shortage and recovery analysis; name the question it answers and the unit of analysisPredict and trace a tiny example of Windowed shortage and recovery analysis before inspecting the resultApply Windowed shortage and recovery analysis to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Windowed shortage and recovery analysis; diagnose the visible failure and perform the corrective check

Build the first-principles mental model for Crew brief and changed-city transfer; name the question it answers and the unit of analysisPredict and trace a tiny example of Crew brief and changed-city transfer before inspecting the resultApply Crew brief and changed-city transfer to a realistic analyst decision while stating assumptions, units, and boundariesBreak one assumption behind Crew brief and changed-city transfer; diagnose the visible failure and perform the corrective check

Deliver Community Bicycle Share Reliability Mart for a municipal mobility operations team; support the decision: where and when should rebalancing crews be repositioned?Reconstruct the critical workflow without the example open and disclose help usedFind and repair a planted data, logic, or communication failure with evidenceRespond to a changed requirement without being told which technique to useState limitations, non-goals, uncertainty, and what evidence would change the recommendation

Capstone

Community Bicycle Share Reliability Mart

Build for a municipal mobility operations team. The result must support this decision: where and when should rebalancing crews be repositioned? Include reproducible inputs, validation evidence, one tested failure, a changed requirement, and an explicit non-goal.

Reproducible source-to-output workflow
Documented quality checks and assumptions
Decision-ready output for a named user
Independent reconstruction and transfer evidence

Phase Complete!

After this phase, you'll be able to:

Window Functions Mental Model
Running & Rolling Measures
Lag, Lead & Change
Cohorts & Retention
Funnels & Event Sequences
Recursive CTEs & Hierarchies
Facts, Dimensions & Grain
Slowly Changing Dimensions

Independently deliver Community Bicycle Share Reliability Mart, diagnose a planted failure, explain the decision logic, and transfer the workflow to a changed requirement.

Back to all phases

Phase Goal

Day 85: Window Functions Mental Model

Rows retained within partitions

OVER, PARTITION BY, ORDER BY, and frame

ROW_NUMBER, RANK, and DENSE_RANK

Default frames and unstable ties

Practice, retrieval, and analyst judgment

Day 86: Running & Rolling Measures

Running totals

ROWS versus RANGE

Moving averages and incomplete windows

Irregular time and false smoothing

Practice, retrieval, and analyst judgment

Day 87: Lag, Lead & Change

LAG and LEAD

Absolute, percentage, and point change

First-row handling

Zero division and incompatible periods

Practice, retrieval, and analyst judgment

Day 88: Cohorts & Retention

Cohort definition and first event

Activity periods and cohort age

Retention denominators and censoring

Reactivation and incomplete cohorts

Practice, retrieval, and analyst judgment

Day 89: Funnels & Event Sequences

Eligibility and ordered steps

Conditional stage counts

Timing and repeated events

Changing denominators

Practice, retrieval, and analyst judgment

Day 90: Recursive CTEs & Hierarchies

Anchor and recursion

Parent-child traversal

Calendar generation

Cycles and runaway recursion

Practice, retrieval, and analyst judgment

Day 91: Facts, Dimensions & Grain

Fact events and declared grain

Dimensions and conformed meaning

Star versus normalized source

Mixed-grain facts

Practice, retrieval, and analyst judgment

Day 92: Slowly Changing Dimensions

Current versus historical questions

Type 1 and Type 2

Effective dates and as-of joins

Overlaps and late changes

Practice, retrieval, and analyst judgment

Day 93: Metric SQL & Semantic Contracts

Name, definition, grain, filters, and owner

Base measures and ratios

Reusable tested views

Conflicting denominators

Practice, retrieval, and analyst judgment

Day 94: Indexes & Performance

B-tree and composite order

Scan, seek, sort, join, and aggregate signals

Sargable predicates

Wrong-query optimization and write cost

Practice, retrieval, and analyst judgment

Day 95: Transactions & Safe Writes

ACID intuition

BEGIN, COMMIT, ROLLBACK, and staging

Upsert and idempotent loads

Partial and duplicate batches

Practice, retrieval, and analyst judgment

Day 96: Phase Project — Bicycle Reliability Mart

Trip, station, maintenance, and weather grains

Dimensional model and governed metrics

Windowed shortage and recovery analysis

Crew brief and changed-city transfer

Phase gate: independent delivery and transfer

Community Bicycle Share Reliability Mart

Phase Complete!