Skip to content
Ephizen logo ephizen / wiki
§ cs 402 · 7 guides

CS 402 — SQL II — Analytical

Window functions, ranking, recursive CTEs, time-series queries, JSON, star schemas — SQL beyond SELECT and JOIN.

Level
7 guides
# Code Name Level Updated
1 —
JSON in SQL (jsonb in Postgres) Postgres' jsonb stores semi-structured data with GIN indexes; querying it well is the bridge between schemaless ingestion and relational analytics.
intermediate
2 —
LAG / LEAD / FIRST_VALUE / LAST_VALUE Reach across rows in the same partition — compute deltas, sessionize events, and forward-fill missing data without self-joins.
intermediate
3 —
Ranking — ROW_NUMBER, RANK, DENSE_RANK Three ways to number rows within a partition — and the one you almost always want for "top-K per group".
intermediate
4 —
Recursive CTEs WITH RECURSIVE walks trees and graphs in pure SQL — org charts, bill-of-materials, transitive closures, and the occasional Fibonacci.
advanced
5 —
Star Schema and ML Feature Stores Dimensional modeling for analytics, and why ML feature stores are essentially star schemas with point-in-time correctness bolted on.
advanced
6 —
Time and Date Functions Bucket events by hour/day/week, compute durations, handle time zones, and write rolling windows over time-series.
intermediate
7 —
Window Functions — Fundamentals Compute aggregates without collapsing rows — OVER, PARTITION BY, and the frame clause, the moment SQL gets serious.
intermediate