Unit 2: Advanced SQL Analytics: Window Functions & Common Table Expressions 35 mins study timeADVANCED

SQL Window Functions (ROW_NUMBER, RANK, DENSE_RANK) & Recursive CTEs

PARTITION BY, ORDER BY frame clauses, running totals, lead/lag temporal offsets, and recursive CTE graph traversal.

Verified: Faculty Peer Review Board

Learning Objectives

Essential Prerequisites

Layer 1: Intuition & Why It Matters

The Core Mental Model

“GROUP BY ka problem yeh hai ki wo saari rows ko ek me nichod (collapse) deta hai. Agar 10 rows hain to 1 row ban jati hai! Lekin agar hume chahiye: "Har employee ka naam bhi dikhao, uski salary bhi dikhao, aur uske department ka total expense bhi uske bagal me dikhao!" Tab hum use karte hain Window Function! Ek Window (Khidki) imagine kijiye jo partition ke upar slide karti hai: - ROW_NUMBER(): Har row ko 1, 2, 3, 4 number dega. - RANK(): Agar do logo ke 100 marks hain, to dono ko Rank 1 milega, agle bande ko Rank 3 milega (Rank 2 gayab!). - DENSE_RANK(): Koi gap nahi chhodega: Rank 1, Rank 1, agle bande ko Rank 2!”

Why This Exists

Financial reports (running balance), e-commerce analytics (Top 3 selling products per category), aur leaderboard rankings window functions ke bina likhna lagbhag namumkin hota hai.

Beginner Foundation

GROUP BY ka problem yeh hai ki wo saari rows ko ek me nichod (collapse) deta hai. Agar 10 rows hain to 1 row ban jati hai! Lekin agar hume chahiye: "Har employee ka naam bhi dikhao, uski salary bhi dikhao, aur uske department ka total expense bhi uske bagal me dikhao!" Tab hum use karte hain Window Function! Ek Window (Khidki) imagine kijiye jo p...

Micro Concepts Decomposition

MICRO CONCEPT 1Canonical Object

Window Framing & Partition Execution

OVER(PARTITION BY x ORDER BY y) performs analytical calculations across subsets without collapsing output row cardinality.

Key Takeaway: Unlike GROUP BY, window functions retain individual row identities while adding comparative aggregate attributes.

MICRO CONCEPT 2Canonical Object

Ranking Variants (ROW_NUMBER vs RANK vs DENSE_RANK)

ROW_NUMBER assigns unique sequential integers; RANK leaves gaps on ties (1, 2, 2, 4); DENSE_RANK eliminates gaps (1, 2, 2, 3).

Key Takeaway: Selecting top-N entities per category requires DENSE_RANK to handle identical scores deterministically.

Layer 3 & 4: Formal Specification & Mechanism

Hardware State Machine Architecture

Window Function General Syntax: function_name([args]) OVER ( [PARTITION BY partition_expression_list] [ORDER BY sort_expression_list [ASC | DESC]] [frame_clause] ) Default Frame Clause: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (when ORDER BY is specified). To include all rows in partition regardless of order: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Common Table Expressions (CTEs): WITH RankedSales AS ( SELECT id, category, sales, ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales DESC) as rn FROM products ) SELECT * FROM RankedSales WHERE rn <= 3;

1. SQL engine executes FROM, WHERE, GROUP BY, and HAVING first. 2. Qualifying rows are sorted according to PARTITION BY and ORDER BY clauses. 3. Window accumulator iterates over partition frame buffer. 4. Analytical function computes value for each row and appends to output projection.

Layer 7: Interactive Laboratory

Interactive Simulator

SQL • VISUALIZATIONVisual SQL Relational JOIN Laboratory

Launch Fullscreen Lab

SQL • RELATIONAL ALGEBRACartesian Product & Key Matching

Visual SQL Relational JOIN Laboratory

Executing SQL Query:

SELECT s.id, s.name, s.major, e.course, e.grade
FROM students s
INNER JOIN enrollments e
  ON s.id = e.student_id;

Left Table: students (s)

id (PK)	name	major
1	Aarav	CS
2	Diya	AI
3	Kabir	Data
4	Riya	Cyber

Right Table: enrollments (e)

student_id (FK)	course	grade
1	CS301	A
2	CS301	B+
2	CS304	A+
5	CS305	A

Relational Result Set (3 rows returned)Matched by s.id = e.student_id

s.id	s.name	s.major	e.course	e.grade
1	Aarav	CS	CS301	A
2	Diya	AI	CS301	B+
2	Diya	AI	CS304	A+

Relational Join Invariant:

In INNER JOIN: Only rows with matching keys in BOTH tables are preserved. Any unmatched student (like Kabir or Riya) or unmatched enrollment (like Student 5) is completely omitted.

Layer 5: Step-by-Step Worked Numerical Example

End-to-End Execution Trace

Monthly growth rate calculation with LAG: SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) as prev_month, ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 / LAG(revenue, 1) OVER (ORDER BY month), 2) as growth_pct FROM monthly_financials;

Layer 6: Active Runtime CodeLab

Step-by-Step Code Execution (SQL)

SQL Studio

AURXON Sandbox

AURXON Digital Engine

Font

main.pyGlacier Light

Ln 1 • Python 3.12

216 chars • 11 lines • Ln 1UTF-8 • 4 Spaces

Interactive Terminal Shell

Sandbox Terminal Ready

Click Run Code or press Ctrl+Enter to compile and execute.

⚡ AURXON Bitstream Runtime v4.8IDRAS Academic Virtual Node

Layer 8: Practice & Knowledge Verification

Active Assessment Quiz

No Practice Questions Configured

Questions for this topic are currently undergoing faculty review.

Academic Evaluation Preparation

Viva Examination & University Scoring Strategy

Standard Viva Examination Questions

How to Write High-Scoring University Exam Answers

Contrast GROUP BY with Window Functions in a table, provide complete syntax breakdown of the OVER clause including partitioning and framing, write a recursive CTE query traversing an employee manager organizational hierarchy, and explain ranking functions.