# Watch Me Use SQL to Find Where We’re Losing Money

## Метаданные

- **Канал:** Absent Data
- **YouTube:** https://www.youtube.com/watch?v=QLZwlsXF6Xg
- **Дата:** 03.05.2026
- **Длительность:** 10:15
- **Просмотры:** 1,634

## Описание

#SQL #SQLTutorial #DataAnalytics #DataAnalysis #DataScience #LearnSQL
In this video, we use SQL running totals to track marketing spend and revenue over time. We identify the exact moment campaigns break even — when total revenue finally covers total spend. This is the point where you stop losing money and start generating real profit.


Find the Dataset here:
https://github.com/Gaelim/youtube/blob/master/campaigns.csv

## Содержание

### [0:00](https://www.youtube.com/watch?v=QLZwlsXF6Xg) Segment 1 (00:00 - 05: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 spend and then we have the revenue generated. So, we essentially want to know when our revenue exceeds the spend. Now, you can see in this first row it already exceeds the spend. 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 dates. 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. par tition 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 by and then we're going to order by the date. And now, let's call this our running spend. And now we have that part of the query. 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 that. 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. And now we have the running revenue and the red 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 CTE or common table expression. So, we're going to say with 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

### [5:00](https://www.youtube.com/watch?v=QLZwlsXF6Xg&t=300s) Segment 2 (05:00 - 10:00)

that as our wildcard. 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 to order by the date. And let's keep that capital. Order by date and let's call that as row 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 revenue greater than or equal to uh to. And just for right now, uh let's go ahead and run this. 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 if that's when the running spend and the running revenue are greater than the running spend. 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. 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 subquery where and I'm going to say row number, which I have there, equals one. 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. All right, where I'm going to create another column here and I'm just going to use break even. And put that as let's say status. All right. And now let's run that. Uh let's see. Let's do this. This doesn't Now Oh. So, here we are pull pulling this in, 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. Okay, there we go. Uh and let me make sure I spell break even correctly. even I'm 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. And then if you have another way you would do this, please leave it in the comments. So, what we did, we created windows function, window function where we brought in spend and revenue as running sums by the channel and we ordered by the date. Then all we did find the condition where the revenue was greater than the running running spend. And we indicated by the row number by ordering by the date in which that happened. And now we brought back this as our status so we can go back to our management and say we found the exact

### [10:00](https://www.youtube.com/watch?v=QLZwlsXF6Xg&t=600s) Segment 3 (10:00 - 10:00)

moment or day when the marketing campaign became profitable using our data that is sitting at a SQL database. I hope that helps. Like and subscribe.

---
*Источник: https://ekstraktznaniy.ru/video/52914*