Bronx.Starling.NY

Hello

Nice

There is a gap between knowing SQL and knowing enough SQL to answer the questions a business actually asks.

"Show me each customer's rank within their segment."
"Give me a running total of revenue by month."
"Flag anyone earning above their department average."

These are not advanced requests.
They are standard business questions.

And you cannot answer them cleanly without window functions, CTEs, and subqueries.

I put together a cheatsheet covering exactly these 3, with the expected output shown alongside each query so you can see what it does without running it.

𝗪𝗶𝗻𝗱𝗼𝘄 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
RANK, running totals with SUM OVER, and LAG to compare a row to the previous one. These three alone will handle the majority of analytical questions that come up in real business data work.

𝗖𝗧𝗘𝘀
The single biggest improvement you can make to a complex query is breaking it into named steps. Easier to read, easier to debug, easier for the next person who has to maintain it.

𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀
Including the correlated version, where the inner query references the outer one row by row. The hardest to read at first, and one of the most useful once it clicks.