SQL for fun and profit 

Prefer to read instead of watching? Check out the article version of this video: aaronfrancis.com/2024/finding-missing-orders-with-a-recursive-cte-bf59e6de

honey wake up the GOAT Aaron Francis has a new video

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.

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

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

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!

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

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

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

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!

SQL is for making friends :D Sharing Quality Lessons

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

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

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

This is the best video about CTEs I've ever seen. Also what is a CTE.

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.

Which SQL client are you using? It looks really clean

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! ✌️

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

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

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' ) 👍

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

I LOVE YOU AARON 💓

Do you have a course for sql/mysql?

what is this tool/app being he's using?

how to handle multi user concurrency in sqlite ?

what gui is that?

🐐

order #458 watching this video like: 👁👄👁

Is the site down?

I set my RU-vid to dark mode but the video stayed white 🤔

What... I just learned SQL and I couldnt understand this lol

sql is not just for making friends ?!

SQL is so important, but for me, it's the most boring part of being a Software Developerrr 😭😭

My ears bleed every time you say sequel 😅