SQL for fun and profit

Recursive CTEs (common table expressions) are really powerful, and actually useful!

[cooldude]

honey wake up the GOAT Aaron Francis has a new video

[juanpineda-montoya]

CTE (common table expression), also known as the WITH clause, is an SQL feature that returns a temporary data set that can be used by another query. As it’s a temporary result, it’s not stored anywhere, but it still can be referenced like you would reference any other table.

[cherkim]

Man I love an upload from Aaron Francis from programming videos.

[benlevy]

This recursive CTE can be replaced with a call to generate_series in both sqlite and postgres

[ahmad-murery]

CTE is great especially the Recursive ones.
The most useful use case for recursive CTE query is to build a multi-level tree structure.
The main problem was to order the result by the level then by the user-defined display order inside each level.

Also, using MariaDB there is a sequence storage engine which can generate a sequence table, something like this:
SELECT seq FROM seq_1_to_100_step_2

Thanks Aaron!

[raccoon_dad]

🤯and I thought I was good at SQL. I love watching you rip through CTE's like nobody's business.

[xtz_]

Wow, the video quality and content are both spot-on!
keep cooking man 👏👏👏👏

[jannismilz]

Awesome video! Maybe begin by telling what CTEs are (yes I know it says it in the description..)

[guacamoly]

Awesome. I think I would have resorted to writing a command line script and sort through the data like a dummy! This is so much better. Slowly making my way through the course and loving it! Learning new things is always fun, but your style and process takes it to the next level! Thanks so much!

[EduardR]

My brain is broke, man. 😅 Some day I understand this video.
Thanks for your videos, crack! 💪🏻

[lapulapucityrider]

Is there way to filter the null order_number then find the the previous order_number of each and add one right and use that to insert? But its also hard to handle if the increment was taken by the next row right. Hahaha forget this I always use uuid though. But thinking whats the best use case of cte and recursive

[liamoconnor]

I’m interested to know what your thought process would have been if lemonsqueezys order ID’s were random and not sequential?

[dharmaturtle]

Idea for a future video; the Gaps and Islands problem. SQL windows in general make my brain explode.

[Simrayz]

Which SQL client are you using? It looks really clean

[fatalglory]

Not sure if this is supported in SQLite, but in Postgres I would just create my CTE with generate_series(1, 741) then left join against that.

Seems simpler to understand and I would assume it’s faster to execute without all the recursion.

[cartercordingley]

I used this a while back to find missing loan applications in the database.

[stonebubbleprivat]

I sent two potential suites, well fwb, that intro 🤞

[MarkJaquith]

This is the best video about CTEs I've ever seen.

Also what is a CTE.

[btrazzini]

Dear Aaron, when should I use OPTIMIZE table? My “SELECT COUNT *” took 5 seconds on a table with only 500, 000 rows (happens only on the first run). Could there be something wrong with the disk? Thanks! ✌️

[zachariascreutznacher]

Haha nice! I doing the same for SimpleStats with dates to make sure there are no gaps: WITH RECURSIVE date_sequence AS (
SELECT ':startDateStart' AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 :intervalType)
FROM date_sequence
WHERE date < ':endDateStart'
) 👍