Advanced SQL: window functions at performance

01

Bago ka magsimula, at mga CTE: pagpapangalan sa mga hakbang ng query

In simple words

Kapag nagluluto ka ng malaking handaan, naghahanda ka muna ng maliliit na mangkok: tinadtad na sibuyas, hinugasang bigas, hinalong sarsa. May pangalan ang bawat mangkok. Tapos pagsasamahin mo. Ang CTE ay maliit na mangkok na may pangalan sa loob ng isang query.

Bago ang course na ito: SQL Joins & Analysis (course 7.4.2). Dapat alam mo na ang mga join, LEFT JOIN, GROUP BY na may HAVING, CASE, mga subquery at mga view. Ginagamit ng course na ito ang parehong apat-na-table na shop at ang order_lines view mula sa course na iyon, kaya buuin muna ang mga iyon (course 7.4.2, Lessons 1 at 6). Dito, mas malalim ang tatalakayin natin: window functions, CTE, mga index at query plan.

Key facts

Ang CTE (common table expression) ay isang SELECT na may pangalan, nakasulat sa itaas ng query gamit ang WITH name AS (...). Magagamit na ng pangunahing query ang name na parang table.
Puwede kang magsulat ng ilang CTE sa isang WITH, pinaghihiwalay ng kuwit. Magagamit ng sumunod na CTE ang nauna.
Umiiral lang ang CTE habang tumatakbo ang isang query na iyon. Ang view (course 7.4.2) ay naka-save sa database; ang CTE ay hindi.
Ginagawang nababasa ng mga CTE ang mahahabang query: may pangalan ang bawat hakbang at masusubukan mo ang bawat hakbang nang mag-isa.
Ang recursive CTE ay tumutukoy sa sarili nito. May dalawa itong bahaging pinagsama ng UNION ALL: isang panimulang SELECT (ang mga unang row) at isang recursive SELECT na nagdaragdag ng mga bagong row mula sa mga row na nahanap na. Humihinto ito kapag walang bagong row na ibinalik ang recursive na bahagi.
Laging bigyan ang recursive CTE ng malinaw na kondisyon ng paghinto (isang WHERE tulad ng x < 5). Iminumungkahi rin ng dokumentasyon ng SQLite na magdagdag ng LIMIT bilang pananggalang kapag alam mo ang pinakamataas na laki.
Nagkakaiba sa pagitan ng mga database:

Database	Recursive CTE keyword	Note
SQLite	`WITH RECURSIVE` (hindi pinipilit ng SQLite ang keyword)	Mga MATERIALIZED / NOT MATERIALIZED hint mula sa bersyon 3.35.0
PostgreSQL	`WITH RECURSIVE`	Ang CTE na minsan lang ginamit ay karaniwang isinasama sa pangunahing query; ang `MATERIALIZED` ay pumipilit na kuwentahin ito nang hiwalay
SQL Server	karaniwang `WITH` (walang RECURSIVE keyword)	Ang default na limitasyon ng recursion ay 100 antas; baguhin ito gamit ang `OPTION (MAXRECURSION n)`

Example (ang mahabang query mula sa course 7.4.2, maikli na ngayon)

Mga customer na mas malaki ang ginastos kaysa sa karaniwang customer (lahat ng order, tulad sa course 7.4.2):

WITH customer_totals AS (
 SELECT customer_id, customer, SUM(line_total) AS total
 FROM order_lines
 GROUP BY customer_id, customer
)
SELECT customer, total
FROM customer_totals
WHERE total > (SELECT AVG(total) FROM customer_totals)
ORDER BY total DESC;

Resulta: Ana 132.0, Dev 98.0. Isang beses isinulat ang CTE at dalawang beses ginamit.

Example (dalawang CTE sa isang kadena: bayad na revenue kada buwan)

WITH paid AS (
 SELECT * FROM order_lines WHERE status = 'paid'
),
by_month AS (
 SELECT strftime('%Y-%m', order_date) AS month,
     COUNT(DISTINCT order_id) AS orders,
     SUM(line_total) AS revenue
 FROM paid
 GROUP BY month
)
SELECT month, orders, revenue, ROUND(revenue / orders, 2) AS avg_order
FROM by_month
ORDER BY month;

Resulta: 2026-09: 1 order, 33.0, 33.0; 2026-10: 3, 156.5, 52.17; 2026-11: 2, 113.0, 56.5.

Example (recursive: sino ang sumali sa pamamagitan ng referral ni Ana?)

WITH RECURSIVE chain(id, name, level) AS (
 SELECT id, name, 0 FROM customers WHERE id = 1
 UNION ALL
 SELECT c.id, c.name, chain.level + 1
 FROM customers c
 JOIN chain ON c.referred_by = chain.id
)
SELECT name, level FROM chain ORDER BY level, name;

Resulta: Ana 0, Ben 1, Chloe 1, Dev 2. Si Dev ay inirekomenda ni Ben, na inirekomenda ni Ana.

Example (recursive: listahan ng mga buwan, para magpakita ng 0 ang mga walang lamang buwan)

WITH RECURSIVE months(m) AS (
 SELECT '2026-08-01'
 UNION ALL
 SELECT date(m, '+1 month') FROM months WHERE m < '2026-12-01'
)
SELECT strftime('%Y-%m', months.m) AS month,
    COALESCE(SUM(ol.line_total), 0) AS revenue
FROM months
LEFT JOIN order_lines ol
 ON strftime('%Y-%m', ol.order_date) = strftime('%Y-%m', months.m)
 AND ol.status = 'paid'
GROUP BY months.m
ORDER BY months.m;

Resulta: 2026-08: 0, 2026-09: 33.0, 2026-10: 156.5, 2026-11: 113.0, 2026-12: 0. Kung walang listahan ng buwan, mawawala ang Agosto at Disyembre, at itatago ng chart ang mga walang lamang buwan.

Step by step

Buksan ang shop2.db mo mula sa course 7.4.2 (o buuin muli ang apat na table at ang order_lines view).
Patakbuhin muna ang panloob na SELECT lang ng customer_totals. Check: 4 na row (Ana, Ben, Chloe, Dev).
Balutin ito ng WITH customer_totals AS (...) at idagdag ang pangunahing SELECT. Check: Ana at Dev.
Patakbuhin ang dalawang-CTE na buwanang query. Tapos palitan ang huling linya ng SELECT * FROM paid; para tingnan ang loob ng unang hakbang.
Patakbuhin ang WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5) SELECT x FROM n; at tiyaking 1 hanggang 5 ang makukuha mo.
Patakbuhin ang referral chain query at iguhit ang kadena sa papel: Ana, tapos Ben at Chloe, tapos Dev.
Patakbuhin ang month-list query. Check: limang buwan, na may 0 para sa Agosto at Disyembre.

Watch on YouTube

A recursive CTE explained with a simple exampleThe Code Samples · YouTube · youtube.com

Videos by other creators on YouTube, for reference. They are not made by or affiliated with The Future Market.

LESSON 01Bago ka magsimula, at mga CTE: pagpapangalan sa mga hakbang ng query

LESSON 01 / ANIMATED LESSON / ENGLISH VOICE00:00 / 01:15

Voice

Read the video script

[Host] "Mahirap basahin ang mahahabang query. Hinahayaan ka ng mga CTE na pangalanan ang bawat hakbang."
[Screen: naka-highlight ang WITH customer_totals AS ( ... )] "Isulat ang hakbang nang minsan, bigyan ng pangalan, gamitin na parang table. Dito, dalawang beses natin itong ginagamit."
[Screen: referral chain na Ana papuntang Ben papuntang Dev] "Inuulit ng recursive CTE ang isang hakbang hanggang wala nang bagong makita. Ganyan mo susundan ang kadena ng mga referral."
[Text on screen: laging magdagdag ng kondisyon ng paghinto] "Laging bigyan ito ng paraan para huminto, tulad ng x na mas mababa sa 5."

Try it now

Sumulat ng CTE na city_revenue na may bayad na revenue kada city (mula sa order_lines), tapos ipakita lang ang mga city na mas mataas sa average na revenue ng city. Check: ang Toronto 230.0 ay mas mataas sa average; ang Hamilton 72.5 ay hindi.
Sumulat ng recursive CTE na naglilista ng bawat araw mula 2026-11-01 hanggang 2026-11-07. Check: 7 na row.

ClaudeStuck? Copy a ready-made prompt for Claude

Copy this, open Claude, paste it, and Claude will walk you through the lesson step by step.

For a grown-up: Claude is for adults (18+). Copy this, paste it into Claude, and use the answer to explain the lesson to your child.

I'm learning "Advanced SQL: window functions at performance" on The Future Market. Lesson 1: "Bago ka magsimula, at mga CTE: pagpapangalan sa mga hakbang ng query".

What the lesson teaches:
- Ang CTE (common table expression) ay isang SELECT na may pangalan, nakasulat sa itaas ng query gamit ang WITH name AS (...). Magagamit na ng pangunahing query ang name na parang table.
- Puwede kang magsulat ng ilang CTE sa isang WITH, pinaghihiwalay ng kuwit. Magagamit ng sumunod na CTE ang nauna.
- Umiiral lang ang CTE habang tumatakbo ang isang query na iyon. Ang view (course 7.4.2) ay naka-save sa database; ang CTE ay hindi.
- Ginagawang nababasa ng mga CTE ang mahahabang query: may pangalan ang bawat hakbang at masusubukan mo ang bawat hakbang nang mag-isa.
- Ang recursive CTE ay tumutukoy sa sarili nito. May dalawa itong bahaging pinagsama ng UNION ALL: isang panimulang SELECT (ang mga unang row) at isang recursive SELECT na nagdaragdag ng mga bagong row mula sa mga row na nahanap na. Humihinto ito kapag walang bagong row na ibinalik ang recursive na bahagi.
- Laging bigyan ang recursive CTE ng malinaw na kondisyon ng paghinto (isang WHERE tulad ng x < 5). Iminumungkahi rin ng dokumentasyon ng SQLite na magdagdag ng LIMIT bilang pananggalang kapag alam mo ang pinakamataas na laki.
- Nagkakaiba sa pagitan ng mga database:

The exercise I'm working on:
1. Sumulat ng CTE na city_revenue na may bayad na revenue kada city (mula sa order_lines), tapos ipakita lang ang mga city na mas mataas sa average na revenue ng city. Check: ang Toronto 230.0 ay mas mataas sa average; ang Hamilton 72.5 ay hindi. 2. Sumulat ng recursive CTE na naglilista ng bawat araw mula 2026-11-01 hanggang 2026-11-07. Check: 7 na row.

I'm stuck. Please:
1. Explain the main idea in plain, simple words with one everyday example.
2. Walk me through the exercise one small step at a time. Ask me what I've done so far before giving the next step.
3. Don't just give me the final answer. Help me do it myself, and check my work when I share it.

I'm a parent helping my child (about 8 years old) with a lesson called "Bago ka magsimula, at mga CTE: pagpapangalan sa mga hakbang ng query" from the course "Advanced SQL: window functions at performance".

Here's what the lesson says:
Kapag nagluluto ka ng malaking handaan, naghahanda ka muna ng maliliit na mangkok: tinadtad na sibuyas, hinugasang bigas, hinalong sarsa. May pangalan ang bawat mangkok. Tapos pagsasamahin mo. Ang CTE ay maliit na mangkok na may pangalan sa loob ng isang query.

The activity:
1. Sumulat ng CTE na city_revenue na may bayad na revenue kada city (mula sa order_lines), tapos ipakita lang ang mga city na mas mataas sa average na revenue ng city. Check: ang Toronto 230.0 ay mas mataas sa average; ang Hamilton 72.5 ay hindi. 2. Sumulat ng recursive CTE na naglilista ng bawat araw mula 2026-11-01 hanggang 2026-11-07. Check: 7 na row.

Please explain this so an 8-year-old understands: short sentences, a fun everyday example, and no hard words.
Then give me 3 simple questions I can ask my child to check they understood, and a way to do the activity together, one tiny step at a time.

Open Claude

Sources

SQLite, the WITH clause (CTEs, recursive CTEs, MATERIALIZED hints, LIMIT advice): www.sqlite.org/lang_with.html
PostgreSQL documentation, WITH queries: www.postgresql.org/docs/current/queries-with.html
Microsoft Learn, WITH common_table_expression (Transact-SQL): learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expressi

My notes

I learned this

02