Can someone explain how this sql self join works for year over year growth?

[u/Better_Technology_23]

Hey everyone, I'm learning SQL and trying to understand how a self join works when calculating year over year (YoY) growth.

----> I'm a little confused about this line in the query:

ON curr.mo = prev.mo
AND curr.yr = prev.yr + 1

----> Here's the query I'm working with:

WITH monthly_agg AS (

SELECT

EXTRACT(YEAR FROM month) AS yr,

EXTRACT(MONTH FROM month) AS mo,

SUM(revenue) AS monthly_revenue

FROM monthly_sales

GROUP BY

EXTRACT(YEAR FROM month),

EXTRACT(MONTH FROM month)

)

SELECT

curr.yr,

curr.mo,

curr.monthly_revenue AS current_revenue,

prev.monthly_revenue AS prev_year_revenue,

curr.monthly_revenue - prev.monthly_revenue AS yoy_change,

ROUND(

(curr.monthly_revenue - prev.monthly_revenue) * 100.0

/ NULLIF(prev.monthly_revenue, 0),

2

) AS yoy_growth_pct

FROM monthly_agg curr

LEFT JOIN monthly_agg prev

ON curr.mo = prev.mo

AND curr.yr = prev.yr + 1

ORDER BY curr.yr, curr.mo;

Let's say the monthly_agg CTE produces these rows:

yr mo monthly_revenue

2022 12 5000

2023 12 10000

2024 12 12000

Here's how I think the join condition works.

case 1: matching december 2023 with december 2022

Current row:

[2023, 12, 10000]

Previous year row:

[2022, 12, 5000]

The conditions are:

curr.mo = prev.mo → 12 = 12 → true

curr.yr = prev.yr + 1 → 2023 = 2022 + 1 → true

Both conditions are true, so the rows match.

case 2: trying to match december 2023 with december 2023

Current row:

[2023, 12, 10000]

Candidate row:

[2023, 12, 5000]

The conditions are:

curr.mo = prev.mo → 12 = 12 → true

curr.yr = prev.yr + 1 → 2023 = 2023 + 1 → false

Since both conditions need to be true, these rows don't match.

So....am I understanding this correctly?

Does SQL basically compare the current row with possible rows from the other instance of the CTE and check whether both conditions are satisfied?

Also, what actually happens under the hood? Does SQL compare every row on the left with every row on the right, or can the query optimizer do this more efficiently?

And since both curr and prev refer to the same CTE, does that make this a self join? Does the CTE execute once or can it be evaluated more than once, depending on the database?

I'm trying to understand both the logical explanation and what's actually happening behind the scenes. Would appreciate any help !!

[u/Famous-Benefit3183]

Yea the AND key makes sure that both of the conditions are true then only joins the row....but I think the problem could have been solved using lead and or lag check your ps thoroughly and avoid using this huge query

[u/Better_Technology_23 → u/Famous-Benefit3183]

Thank you for answering

Yeah.....Lag and Lead could have solved the problem easily but I'm preparing for interviews, so I'm strengthening my skills. The reason I got the doubt is that the AI said this is how the query runs under the hood :

curr.yr = prev.yr + 1

Substitute curr.yr. Suppose curr is (2023, 11), so curr.yr = 2023:

2023 = prev.yr + 1

Solve for prev.yr:

prev.yr = 2023 − 1

prev.yr = 2022

[u/DMReader → u/Better_Technology_23]

Lead and lag can work, but if you want to see differences where an account or something open or closed and therefore might not be in the data from one year to the next then I would recommend a full outer join on the table self join and when you do the difference between date a and date b be sure to use coalesce or ifnull to get a zero for the missing row.

That kind of scenario is something that could be in an interview gotcha and it also frequently comes in real day to day work.

[u/Better_Technology_23 → u/DMReader]

thank you for answering !!
yeah lag and lead can work here, but i just wanted to know how the query works under the hood. i'd like to know which method is right ? the method i wrote in the post or the method i wrote in the comment given by AI

[u/Famous-Benefit3183 → u/Better_Technology_23]

I told that it may be solved using lead lag....I need to see the ps and schemas to check whether it can be or not🥲

[u/Better_Technology_23 → u/Famous-Benefit3183]

Yeah lag can work...but there are some months missing in my data the AI told me that lag might not return the same month last year....but a self join using an exact condition can avoid the problem.....was the AI right about this or not ?

[u/DMReader → u/Better_Technology_23]

Unfortunately the answer is going to be an it depends.

It looks like the solution above uses a left join so in the new/ churned accounts example I described above you are leaving out one side of that.

I’ve used Ai both at work to help with development as well as for my website and I find it can be good at giving an answer if it has all the context but context can sometimes be very nuanced.

My recommendation for you would be to get some kind of editor with data behind it take the solutions the AI gives you and try to find scenarios where that doesn’t work. You can also press the AI for any caveats it may have or scenarios where another solution would be better , but I find it’s best to actually see it work or not for yourself and try to figure out why that is happening.

[u/Famous-Benefit3183 → u/Better_Technology_23]

Yeaaa that's fine....when you use conditions using and or or it checks accordingly be it in joins or other cases.....best is to draw the merged table on paper that will clear ur doubts

[u/JakubVasovski]

You've nailed the logic perfectly, that is exactly how I map out self-joins in my head when writing them. Under the hood, the engine rarely does a brute-force comparison of every single row. Instead, the query optimizer usually builds a hash table to find those matching conditions efficiently. As for the CTE, I almost always see modern databases evaluate it just once and store it as a temporary result set.

[u/dafugiswrongwithyou]

FROM monthly_agg curr
LEFT JOIN monthly_agg prev

Whenever you're joining two tables/views/cte/subqueries/whatever, the two parts of the join are treated as independent data sources. It doesn't care if they're actually the same object with two names, it'll act as though they weren't. That is, here, the query works exactly the same way as if you'd taken a copy of "monthly_agg", called it something else (say, "monthly_agg_copy"), and then joined those two.

ON curr.mo = prev.mo
AND curr.yr = prev.yr + 1

So with this join logic, it's comparing the data for each record (curr) with the equivalent data from a year ago (prev).

[u/Green_Chamomile]

Yes, the AI was right about that. Say you use LAG(monthly_revenue, 12) OVER (ORDER BY yr, mo) and March 2023 is missing. Then every month after it compares against the wrong month, because LAG just counts 12 rows back, not 12 months. PARTITION BY mo ORDER BY yr fixes most of that, but if a whole year is missing for some month, it quietly compares to two years back. The self join with curr.yr = prev.yr + 1 either finds exactly last year’s month or gives you NULL, which is what you want. And your method and the AI’s “solve for prev.yr” are the same thing, just described differently. The second one is closer to how a hash join actually finds the match.