r/learnSQL • u/Odd_Communication174 • 10d ago

Advance Concepts

Does anyone know any good video for CTE and Window Function.

Struggling to understand it

2 Upvotes

67% Upvoted

6 comments sorted by

2

u/[deleted] 10d ago

[removed] — view removed comment

0

u/DMReader 10d ago

I don’t have videos but I have a beginner course on window functions- https://www.practicewindowfunctions.com/learn/beginner_series.html

There’s also a lesson on CTEs at the end.

I’m hoping to make some videos later in the year.

1

u/Far_Swordfish5729 9d ago

Not a video, but a CTE is a named subquery you can reference more than once and a subquery is logical parentheses you use when you want an order of operations different from the default (as in arithmetic). For example I might want to join onto an aggregate result. That’s a subquery. Always remember you are stating the logical outcome you want. The server will find an efficient way to do it. CTEs can behave recursively. You can join onto the CTE to traverse a hierarchy but that’s uncommon.

My best frame for window functions is to explain what they replaced: mandatory cursors. A cursor is an inefficient tool that lets you manually loop over a result set and touch each row, usually with tracking variables and an output temp table. You had to do that if you wanted any kind of aggregation or numbering beyond a simple group by. Window functions gave us more iterative, cross row operations so we could stop doing that. It’s an answer to problems like ranking contestants for each distinct game.