Watch Me Analyze Data with SQL | Window Functions | RUNNING SUM

Added: 2026-05-06

[00:00:00]Hi, I'm going to show you how to use SQL to calculate when our marketing campaigns become profitable. So essentially, we want to know when revenue exceeds our marketing spend. And the way we're going to do that is use a window function running sum and row number and then we'll be able to get that difference. So let's look at our data. Here we have the date, we have the channel and here we have uh three channels. We have paid search, paid social and uh display. We have the marketing spin and then we have the revenue generated. So we essentially want to know when our revenue exceeds the spin. Now you can see in this first row it already exceeds the spin, but we're looking at something just that day. So we need to look at the running sum to see if our campaigns are profitable over their lifespan. So let's do that. So we just have a select here where we're selecting everything from this table called campaigns. [snorts] And the first thing we want to do is just isolate the channels or the columns that we want. So let's get the date and we can get the channel. And now that we have that, let's use a running sum for the spend. And the way we're going to do that is with a window function. So let me walk you through that. We [snorts] are just going to use sum. That is the first part. So we have sum. Now what do we want to sum? We want to sum that spend. And once we have that, we We need to indicate how we want that to be broken up because as you see here we have different channels. So I want a running sum over each one of those channels. So we do a partition. So let's do over and then in that part of the function we're going to do partition and then partition by and let's capitalize that partition by and then we want to partition by the channel. So we have the running sum over each channel. Now the important thing here is that there are different order to date. So we need to order this date so we can get a running sum over each date. So now we order order by and then we're going to order by the date. And now let's call this our running spend.

[00:02:55]And now we have that part of the query.

[00:02:59]So if I execute that, you can see now we have this running sum. Now we also need to have the running revenue. So I am going to just copy this. Put a comma then hit uh copy paste there. And now we need to change we need the running sum of the revenue. So let's just change this to revenue and then change the name here to revenue.

[00:03:33]And now we have the running revenue and the ready running sum. Now you can see that for this particular campaign we spent more than we brought in. So now we need to find the date that these campaigns became profitable or in a sense where we broke even. Um now we have everything we need for this part of our query. So that'll be our base query, right? So let's change that into a uh CTL or common table expression. So, we're going to say width base. That's going to be the name of my table as. And then all I need to do is encapsulate that with our um parentheses there and then I can select from it. So, select everything from and then we call that base. So, if I run that, you'll get the same thing as we did that because we're just selecting from it. Now I want all of this to come back. But now I need to find the row number where the revenue is greater than spend. So now what we want to do is we are bringing in everything. So we leave that as our wild card. And next we use row number. So let's say row number is going to count the rows for me where things are meeting our condition. So row number is our window function and our function always has these uh parentheses over and then I want to partition by so I'm going to say partition by the channel right and now I'm going order by the date and let's keep that capital order by date and let's call that as a room number I guess from base uh all right so I've selected that and then let's uh let's see if oh and then we need to set our condition so where the running revenue is greater than the running spend. So this is going to give us um where this happens. So we have running rev greater than or equal to uh greater than or equal to and just for right now let's go ahead and run this.

[00:06:32]So you can see what happens is we have now we have ordered this by the date and based on this condition. So we can see here display is that's when the running spend and the running revenue are greater than the running spend.

[00:06:55]So now we have that one. And next if we go down to paid social we can see that here and we should have three ones. Now all we need to do is create a condition.

[00:07:07]Now I don't want to create another table function. So, I'm going to turn this into a subquery. And the way we do that is just capital uh not capitalize, put a parenthesis here. And then I'm going to select everything from and then this sub query where and I'm going to say row number which I have there equals one.

[00:07:35]And then you know one other thing we can do here is say okay this is we can create a column let me just organize this a little bit.

[00:07:47]All right. Where I'm going to create another column here and I'm just going to use break even.

[00:07:56]Break even and put that as let's say status.

[00:08:06]All right.

[00:08:09]And now let's run that.

[00:08:13]Let's see. Let's do this. This doesn't now.

[00:08:21]Oh, so here we are pull pulling this in.

[00:08:26]But we also need to for a subquery, we need to have an alias. So for this to work, we need to give that subquery an alias for that table. So I'm just going to call this T. And this should fix it.

[00:08:41]Okay, there we go. Uh, and let me make sure I spell break even correctly.

[00:08:49]Just going to put it like that. And then now we have the date in which we broke even. And we can see that paid search was definitely the first one. Then we became uh even with paid social and then display. So, let's just quickly go over what we did. Let me pull this down so we can quickly go over it. And then if you have another way you would do this, please leave it in the comments. So, what we did, we created a Windows function window function where we brought in spend in revenue as running sums by the channel and we ordered by the date.

[00:09:36]Then all we did was find the condition where the revenue was greater than the running revenue was greater than running spend and we indicated by the row number by ordering by the date in which that happened.

[00:09:51]And now we brought back this as a status. So we can go back to our management and say we found the exact moment or day when the marketing campaigns became profitable using a data that is sitting in a SQL database. I hope that helps.

[00:10:14]Like and subscribe.