🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

🏆 धडा 14 — Advanced SQL: एकही row न गमावता rankings

📍 तुम्ही इथे आहात: 18 पैकी धडा 14 · मागील: lesson-13-security · पुढील: lesson-15-inside-the-engine

भाग 3 — अधिक खोलात. धडे 01–12 नी रेकॉर्ड रूम बांधली आणि चालवली. धडे 13–18 हे interviews आणि incidents मध्ये विचारले जाणारे विषय आहेत — प्रत्येक त्याच school.db वर प्रत्यक्ष चालतो.

📦 या ब्रँचमध्ये काय आहे

धडे 01–14, आणि db/demo.py मधले window(): श्रेणींचे गुणांमध्ये रूपांतर करणारा एक CTE, प्रत्येक वर्गात RANK() आणि running total, "शाळेच्या सरासरीपेक्षा जास्त" साठी एक subquery, आणि report_card नावाचा एक view.

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

GROUP BY म्हणजे प्रत्येक वर्गाकडे एकच आकडा मागणे — वर्गाची सरासरी — आणि प्रत्येक वर्गासाठी एक चिठ्ठी परत मिळणे. पण मुख्याध्यापिकेला यादीत प्रत्येक विद्यार्थिनी हवी आहे, आणि नावासमोर तिच्या वर्गातला तिचा क्रमांक लिहिलेला हवा. हीच window function: ती संबंधित rows ची एक "खिडकी" (विद्यार्थिनीचा वर्ग) पाहते आणि एकही row न दाबता प्रत्येक row वर एक जास्तीचा आकडा लिहिते.

CTE (WITH …) म्हणजे तुम्ही आधी बनवता आणि मग वापरता असा छोटा table, जसे पानाच्या कडेला कच्चे काम करणे. Subquery म्हणजे प्रश्नाच्या आत प्रश्न. View म्हणजे तुम्ही जतन करून नाव दिलेला प्रश्न.

🗺️ आकृती

flowchart LR
    g["grades<br/>A+=10 · A=9 · B+=8 · B=7 · C=6"]
    cte["WITH points AS (…)<br/>Katrina 20 · Meera 18 · Aishwarya 17 · Dipika 15 · Rohan 13"]
    rank["RANK() OVER (PARTITION BY class ORDER BY pts DESC)<br/>3A: Katrina 1 · Aishwarya 2 · Dipika 3 — 3B: Meera 1 · Rohan 2"]
    sub["WHERE pts > (SELECT AVG(pts)) = 16.6<br/>Katrina · Meera · Aishwarya"]
    g --> cte --> rank
    cte --> sub

🗺️ काढलेली आवृत्ती + एक lab: https://school-edh.pages.dev/database/lesson-diagrams.html#l14

❓ काय

Subquery — WHERE pts > (SELECT AVG(pts) FROM points); तसेच IN (SELECT …) आणि EXISTS (SELECT …).
CTE — WITH name AS (SELECT …) SELECT … FROM name: वाचायला सोप्या पायऱ्या, एका query मध्ये पुन्हा वापरता येतात; WITH RECURSIVE झाडांमधून फिरतो (वर्ग → त्याच्या तुकड्या → त्यांच्या विद्यार्थिनी).
Window functions — fn() OVER (PARTITION BY … ORDER BY …): ROW_NUMBER, RANK, DENSE_RANK, running SUM, शेवटच्या N rows वर AVG, LAG / LEAD (मागची / पुढची row), NTILE (quartiles). प्रत्येक row राहते.
View — CREATE VIEW report_card AS SELECT …: table सारखा वापरला जाणारा जतन केलेला प्रश्न. Materialized view (Postgres) उत्तर साठवतो आणि तो refresh करावा लागतो.
HAVING GROUP BY नंतर groups गाळतो (धडा 03), WHERE आधी rows गाळतो.

🧑‍🍳 खोलीत राहणारा code — functions आणि stored procedures

View प्रश्न साठवतो. Function SQL च्या आत वापरता येईल अशी value परत देतो (SELECT points(grade) …). Stored procedure एक काम करते — अनेक statements, नियम आणि writes — आणि ती तुम्ही CALL ने चालवता. Trigger (धडा 18) row बदलली की आपोआप चालतो.

db/postgres/procedures.sql ही खरी PostgreSQL आवृत्ती आहे (PostgreSQL च्या स्वतःच्या parser ने तपासलेली; कोणत्याही Postgres 11+ मध्ये चालवा):

CREATE PROCEDURE transfer_pupil(p_student int, p_class int)
LANGUAGE plpgsql AS $$
BEGIN
  IF (SELECT count(*) FROM students WHERE class_id = p_class) >= 3 THEN
    RAISE EXCEPTION 'class % is full (3 pupils)', p_class;
  END IF;
  UPDATE students SET class_id = p_class WHERE id = p_student;
  INSERT INTO audit_log (what) VALUES ('pupil ' || p_student || ' moved to class ' || p_class);
END
$$;

CALL transfer_pupil(4, 1);   -- ERROR:  class 1 is full (3 pupils)
CALL transfer_pupil(1, 2);   -- CALL  (and one audit_log row)
GRANT EXECUTE ON PROCEDURE transfer_pupil(int, int) TO school_api;   -- the API may CALL it, not touch the tables

SQLite मध्ये stored procedures नाहीत — ते तुमच्या app च्या आत चालते, म्हणून app हीच procedure आहे. python3 db/demo.py procs SQLite मधले सर्वात जवळचे तुकडे चालवते: conn.create_function("points", …) ने नोंदवलेले आणि SQL च्या आत वापरलेले Python function, आणि RAISE(ABORT, 'class is full (3 pupils)') असलेला BEFORE UPDATE trigger, जो कोणत्याही app साठी मीराचे भरलेल्या 3A मध्ये जाणे नाकारतो.

✅ प्रत्येक app आणि script साठी एकच नियम, data शेजारीच तपासलेला, एकाच round trip मध्ये
✅ tables न देता GRANT EXECUTE (धडा 13)
❌ app च्या code review, tests आणि debugger पासून लपलेले logic; ते migrations सह deploy करा (धडा 08)
❌ vendor-specific (PL/pgSQL, T-SQL, MySQL ची बोली) — procedures लहान आणि कमी ठेवा

🤔 का

Reports, leaderboards, "प्रत्येक वर्गातले पहिले 3", महिन्या-महिन्याचा बदल — हे app मधल्या loop code च्या पानांऐवजी खोलीतली एक query असते. SQL interview मधले हेच सर्वात सामान्य प्रश्नही आहेत.

🔧 कसे (या repo मध्ये)

db/demo.py मधले window() — गुण CTE च्या आत CASE ने मोजले जातात, मग प्रत्येक वर्गात rank केले जातात, सरासरीशी तुलना करून गाळले जातात, आणि view म्हणून जतन होतात.

🧪 करून पाहा

python3 db/demo.py window procs
python3 - <<'EOF'
import sqlite3
c = sqlite3.connect("db/school.db")
P = "CASE grade WHEN 'A+' THEN 10 WHEN 'A' THEN 9 WHEN 'B+' THEN 8 WHEN 'B' THEN 7 ELSE 6 END"
# every pupil's maths grade next to the one before it in the list (LAG) — change maths to science
for row in c.execute(f"""SELECT s.name, g.grade, LAG(g.grade) OVER (ORDER BY {P} DESC) AS previous
                         FROM grades g JOIN students s ON s.id = g.student_id WHERE g.subject = 'maths'"""):
    print(row)
EOF

✅ तपासा — तुम्हाला काय दिसायला हवे

('3A', 'Katrina', 20, 1, 20), ('3A', 'Aishwarya', 17, 2, 37), ('3A', 'Dipika', 15, 3, 52), मग 3B पुन्हा rank 1 पासून मीरासह सुरू; सरासरी 16.6 आहे आणि फक्त कतरिना, मीरा आणि ऐश्वर्या तिच्यापेक्षा वर आहेत; view तेच totals परत देतो. तुमच्या snippet मध्ये पहिला previous None आहे. procs points() ने कतरिना, मीरा, ऐश्वर्या यांना rank करते, Meera → 3A class is full (3 pupils) म्हणून नाकारते आणि ऐश्वर्याला 3B मध्ये हलवते.

🏁 तुम्ही आत्ताच काय सिद्ध केले

तुम्ही groups च्या आत एकही row न गमावता rows ना rank करू शकता, बेरीज करू शकता आणि तुलना करू शकता, आणि एखादा प्रश्न जतन करू शकता म्हणजे प्रत्येक app तो एकाच प्रकारे विचारते.

⚠️ नेहमीच्या चुका

प्रत्येक row हवी असताना GROUP BY वापरणे — window वापरा
PARTITION BY विसरणे, त्यामुळे rank संपूर्ण शाळेत चालतो
बरोबरीच्या वेळी RANK विरुद्ध ROW_NUMBER: RANK देतो 1, 1, 3; ROW_NUMBER देतो 1, 2, 3
मोठ्या table वर प्रत्येक row साठी एकदा चालणारी correlated subquery — EXPLAIN तपासा
view ला वेग वाढवणारा समजणे: साधा view प्रश्न साठवतो, उत्तर नाही
एखादा business नियम फक्त एकाच app मध्ये, तर scripts थेट tables मध्ये लिहितात
सगळे business logic procedures मध्ये टाकणे — test, review आणि deploy करायला कठीण

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: analytics pages, rankings आणि billing reports CTEs आणि windows ने लिहिले जातात; reviewers app-side loops ऐवजी हेच अपेक्षित धरतात.

⏭️ पुढे

git checkout lesson-15-inside-the-engine — commit झालेली row वीज गेल्यावरही का टिकते.

🏆 Lesson 14 — Advanced SQL: rankings without losing a row

📍 You are here: Lesson 14 of 18 · Previous: lesson-13-security · Next: lesson-15-inside-the-engine

Part 3 — going deeper. Lessons 01–12 built and ran the record room. Lessons 13–18 are the topics interviews and incidents ask about — each one runs for real on the same school.db.

📦 What's in this branch

Lessons 01–14, plus window() in db/demo.py: a CTE that turns grades into points, RANK() and a running total inside each class, a subquery for "above the school average", and a view called report_card.

🧒 Explain like I'm 5

GROUP BY is like asking each class for one number — the class average — and getting one slip per class back. But the head teacher wants every pupil on the list, with their place in their class written next to their name. That is a window function: it looks at a "window" of related rows (the pupil's class) and writes an extra number on every row, without squashing any.

A CTE (WITH …) is a little table you build first and then use, like doing the rough work on the side of the page. A subquery is a question inside a question. A view is a question you save and give a name.

🗺️ Diagram

flowchart LR
    g["grades<br/>A+=10 · A=9 · B+=8 · B=7 · C=6"]
    cte["WITH points AS (…)<br/>Katrina 20 · Meera 18 · Aishwarya 17 · Dipika 15 · Rohan 13"]
    rank["RANK() OVER (PARTITION BY class ORDER BY pts DESC)<br/>3A: Katrina 1 · Aishwarya 2 · Dipika 3 — 3B: Meera 1 · Rohan 2"]
    sub["WHERE pts > (SELECT AVG(pts)) = 16.6<br/>Katrina · Meera · Aishwarya"]
    g --> cte --> rank
    cte --> sub

🗺️ Drawn version + a lab: https://school-edh.pages.dev/database/lesson-diagrams.html#l14

❓ What

Subquery — WHERE pts > (SELECT AVG(pts) FROM points); also IN (SELECT …) and EXISTS (SELECT …).
CTE — WITH name AS (SELECT …) SELECT … FROM name: readable steps, reusable in one query; WITH RECURSIVE walks trees (a class → its sections → their pupils).
Window functions — fn() OVER (PARTITION BY … ORDER BY …): ROW_NUMBER, RANK, DENSE_RANK, running SUM, AVG over the last N rows, LAG / LEAD (the previous / next row), NTILE (quartiles). Every row is kept.
View — CREATE VIEW report_card AS SELECT …: a saved question used like a table. A materialized view (Postgres) stores the answer and must be refreshed.
HAVING filters groups after GROUP BY (lesson 03), WHERE filters rows before.

🧑‍🍳 Code that lives in the room — functions and stored procedures

A view stores a question. A function returns a value you can use inside SQL (SELECT points(grade) …). A stored procedure does a job — several statements, rules and writes — and you run it with CALL. A trigger (lesson 18) runs by itself when a row changes.

db/postgres/procedures.sql is the real PostgreSQL version (checked with PostgreSQL's own parser; run it in any Postgres 11+):

CREATE PROCEDURE transfer_pupil(p_student int, p_class int)
LANGUAGE plpgsql AS $$
BEGIN
  IF (SELECT count(*) FROM students WHERE class_id = p_class) >= 3 THEN
    RAISE EXCEPTION 'class % is full (3 pupils)', p_class;
  END IF;
  UPDATE students SET class_id = p_class WHERE id = p_student;
  INSERT INTO audit_log (what) VALUES ('pupil ' || p_student || ' moved to class ' || p_class);
END
$$;

CALL transfer_pupil(4, 1);   -- ERROR:  class 1 is full (3 pupils)
CALL transfer_pupil(1, 2);   -- CALL  (and one audit_log row)
GRANT EXECUTE ON PROCEDURE transfer_pupil(int, int) TO school_api;   -- the API may CALL it, not touch the tables

SQLite has no stored procedures — it runs inside your app, so the app is the procedure. python3 db/demo.py procs runs the closest SQLite pieces: a Python function registered with conn.create_function("points", …) and used inside SQL, and a BEFORE UPDATE trigger with RAISE(ABORT, 'class is full (3 pupils)') that refuses Meera's move into a full 3A for any app.

✅ one rule for every app and script, checked next to the data, in one round trip
✅ GRANT EXECUTE without granting the tables (lesson 13)
❌ logic hidden from the app's code review, tests and debugger; deploy it with migrations (lesson 08)
❌ vendor-specific (PL/pgSQL, T-SQL, MySQL's dialect) — keep procedures small and few

🤔 Why

Reports, leaderboards, "top 3 per class", month-over-month change — these are one query in the room instead of pages of loop code in the app. They are also the most common SQL interview questions.

🔧 How (in this repo)

window() in db/demo.py — the points are computed with a CASE inside a CTE, then ranked per class, filtered against the average, and saved as a view.

🧪 Try it

python3 db/demo.py window procs
python3 - <<'EOF'
import sqlite3
c = sqlite3.connect("db/school.db")
P = "CASE grade WHEN 'A+' THEN 10 WHEN 'A' THEN 9 WHEN 'B+' THEN 8 WHEN 'B' THEN 7 ELSE 6 END"
# every pupil's maths grade next to the one before it in the list (LAG) — change maths to science
for row in c.execute(f"""SELECT s.name, g.grade, LAG(g.grade) OVER (ORDER BY {P} DESC) AS previous
                         FROM grades g JOIN students s ON s.id = g.student_id WHERE g.subject = 'maths'"""):
    print(row)
EOF

✅ Verify — what you should see

('3A', 'Katrina', 20, 1, 20), ('3A', 'Aishwarya', 17, 2, 37), ('3A', 'Dipika', 15, 3, 52), then 3B starting again at rank 1 with Meera; the average is 16.6 and only Katrina, Meera and Aishwarya are above it; the view returns the same totals. In your snippet the first previous is None. procs ranks Katrina, Meera, Aishwarya with points(), refuses Meera → 3A with class is full (3 pupils) and moves Aishwarya to 3B.

🏁 What you just proved

You can rank, total and compare rows inside groups without losing any, and save a question so every app asks it the same way.

⚠️ Common mistakes

using GROUP BY when you need every row — reach for a window
forgetting PARTITION BY, so the rank runs across the whole school
RANK vs ROW_NUMBER with ties: RANK gives 1, 1, 3; ROW_NUMBER gives 1, 2, 3
a correlated subquery that runs once per row on a big table — check EXPLAIN
treating a view as a speed-up: a plain view stores the question, not the answer
a business rule that lives only in one app while scripts write to the tables directly
putting ALL business logic into procedures — hard to test, review and deploy

🏭 Why this matters in production: analytics pages, rankings and billing reports are written with CTEs and windows; reviewers expect them instead of app-side loops.

⏭️ Next

git checkout lesson-15-inside-the-engine — why a committed row survives a power cut.

← Previoussecurity Next →inside the engine

This page is the lesson's README from the lesson-14-advanced-sql branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.