- 6 weeks ago
MySQL exploratory data analysis gets practical in this full project as the dataset moves from cleaned tables into real insights. Starting with total layoffs, company names, industries, countries, dates, and funding, the lesson shows how to investigate patterns instead of chasing one fixed question. The walkthrough compares the biggest layoffs, checks which companies were fully laid off, and breaks the data down by industry, country, year, and company stage.
From there, the analysis gets more advanced with month-by-month grouping, substring work on dates, and a rolling total that reveals how layoffs built over time from 2020 into early 2023. The project also uses grouping, ordering, window functions, dense rank, and multiple CTEs to rank the companies with the largest yearly layoffs. It is a strong MySQL tutorial for portfolio projects, SQL for data analytics, and exploratory analysis in a real-world style.
Created for anyone searching for MySQL exploratory data analysis, SQL portfolio project ideas, data cleaning and EDA workflow, window functions in MySQL, rolling total SQL, dense rank examples, and company layoffs analysis. It is useful for learners who want hands-on SQL practice, interview prep, or a clear example of how to turn raw business data into meaningful insights.
From there, the analysis gets more advanced with month-by-month grouping, substring work on dates, and a rolling total that reveals how layoffs built over time from 2020 into early 2023. The project also uses grouping, ordering, window functions, dense rank, and multiple CTEs to rank the companies with the largest yearly layoffs. It is a strong MySQL tutorial for portfolio projects, SQL for data analytics, and exploratory analysis in a real-world style.
Created for anyone searching for MySQL exploratory data analysis, SQL portfolio project ideas, data cleaning and EDA workflow, window functions in MySQL, rolling total SQL, dense rank examples, and company layoffs analysis. It is useful for learners who want hands-on SQL practice, interview prep, or a clear example of how to turn raw business data into meaningful insights.
Category
📚
LearningTranscript
00:00Hello everybody, in this project we're going to be focusing on exploratory data analysis.
00:04Now in the first project we worked with this exact data set and we cleaned up the entire
00:08thing and that was a really good project and it set us up to explore the data and with all
00:14that
00:14clean data we'll be able to look at our data much better and find better insights while we are using
00:19it. Now normally when you start the EDA process or the exploratory data analysis process you have
00:24some idea of what you're looking for sometimes not always and sometimes when you're exploring the
00:30data you also find issues with the data that you then have to clean. So even though I did a
00:34data
00:35cleaning video and then an exploratory data analysis video and they're kind of separate projects
00:39sometimes those coincide together where you're exploring it and cleaning it at the same time.
00:45Now what we're going to be doing here with this data set we're just going to be kind of exploring
00:49it. I don't have any agenda. I don't have any you know one thing that I want to look at.
00:54I
00:54just kind of want to look at everything and we'll kind of discover and go about things as we are
01:00learning and looking at this data set. We will however start off really simple with kind of the
01:04basics work a little bit more towards the tougher stuff and then the end we'll have some more advanced
01:09things that I think will be really fun. So with that being said let's start off with kind of more
01:14easier things. We'll kind of just ease our way into exploring this data set. Let's pull this down
01:20and let's copy this right down here. Now we're going to be working with this total laid off
01:25and percentage laid off or most likely this total laid off quite a bit. The percentage laid off isn't
01:31super helpful because we don't know how large the company is. We don't have another column here
01:35that says here's how many total employees they had and then okay they had a percentage laid off.
01:40You know we won't work as much with this one but we'll work quite a bit with this total laid
01:45off.
01:45Let's look real quick. We can look at something like the max
01:50total and I need to use a parentheses max total laid underscore off and let's look at this.
01:58So on one day there was somebody out there who had the max total laid off of 12,000 people.
02:06That's a lot of people to lay off in one you know one go. That's a lot. Let's also take
02:12a look
02:13at the max and I think it was percentage laid off. Let's run this and it looks like one. Now
02:22one
02:22represents 100. That means 100 percent of the company was laid off and that's you know that's
02:29not great. It just means an entire company went under essentially. We can actually take a look at that
02:34because I'm interested to see you know if there's any companies I recognize or can see where then
02:41you come right down here where the percentage laid off is equal to one. Let's go ahead and look at
02:49this
02:50and let's take a look. So we have this ahead. I'm just going to go through here and see if
02:56I recognize
02:56any of these. I'm in the crypto space. BlockFi. I feel like I recognize that one. I don't know.
03:05Let's keep going. Deliveroo. It's not good. They left a lack of 120 people. I'm just curious. I mean I'm
03:13just
03:13kind of scrolling through here trying to see if I recognize any. These are companies that like completely went
03:18under or lost all their employees. Volt Bank. Interesting. Just interesting to me. We're going
03:25to be taking a look at a lot of stuff but these are companies that completely went under and that's
03:30you know unfortunate. We can also order by total underscore late underscore off indescent. That's
03:37how you spell it. Indescending. We'll see which company went under had the largest. So this one had
03:432,000. Construction company had 2,400 people. They went under. It doesn't say what stage they were
03:49at but that's in the United States. We can also take a look at and there's another column over here
03:53called funds raised in millions. Let's look at that one. So I want to see. These are companies that had
03:59a lot of funding or potentially a ton of funding. Let's go over. This is like 2.4 billion dollars
04:08I
04:08believe. Like I think it's like a ton of money. Quibi. I believe I know this company. In BlockFi.
04:15I thought I'd heard of them. I'm pretty sure I know who that is. So Quibi is one that I'm
04:19definitely
04:20familiar with. It was like a short form media company. Yeah yeah. And then there's British Volt
04:26which looks like an electric company that went under. So you know some big companies that went under
04:32in 2023, 2020, 2022. So that's interesting. So we have a lot of companies here and we're just looking at
04:39that had total laid off. But let's take a look. Let's use group by real quick. I want to look
04:45at
04:45the company. And I also want to look at the sum of the total laid off. And for that we
04:53need to use
04:54a group by the company. And let's just start with this. And I'm sure we'll use an order by in
04:59a
05:00second. Yeah. Let's order by. Order by. Let's just do 2 for now. In descending. And 2 stands for
05:091. 2. This is the total laid off. So for the total for this table. And we don't know how
05:16far go back
05:17it goes. We haven't checked that yet. We'll check that in a second. But for this table you should
05:21recognize a lot of these companies. So I think it starts in like 2020 until like sometime in 2023.
05:28But this is Amazon let go of 1,800 people. Google 12,000. I'm guessing that's at one time
05:33because that was the max that we looked at earlier. This is Facebook or Meta. Salesforce,
05:39Microsoft, Philips, Uber, Dell, Cisco, Peloton. I mean these are a ton of big companies.
05:45Arvana. They let go of thousands and thousands and thousands of people. Twitter. That's not
05:50surprising given what's the change of things. Groupon. A ton of a ton of people or a ton of
05:56companies. And that's a lot of people that have been let go. Now let's really quickly before we
06:01keep going. I want to look at our date ranges real quick. So let's select everything. Whoops.
06:07We'll do from there. And how do we want to do this? Let's do minimum of date. And let me
06:13do it like
06:14this. Date. And then we'll do the max as well because I want to look at the date range that
06:20we
06:20have here. Let's run this. It looks like it starts in 2020 of 311. So right when like I believe
06:28the
06:28pandemic started or the COVID-19 started. I want to say that's like right when it hit at least us
06:34in
06:34the United States. And then this is almost exactly three years later. So early 2023. So just in those
06:42three years, you know, here's some of what we're looking at. These companies have let go of quite
06:46a few people or have had layoffs. We can also take this exact thing. Oops. What did I do here?
06:53Copy this again. We can also take this exact thing and look at quite a few other things. There was
06:59the industry. So we can look at industry. Like what industry got hit the most during this time or had
07:04the most layoffs. All we're looking at right now is total laid off. We can also look at percentage in
07:11a
07:11little bit, but it looks like consumer got hit really hard, retail really hard. That makes a lot
07:16of sense with shops closing down because people couldn't come in for the coronavirus. Now we're
07:21just making assumptions, right? But you know, during that time, it was mostly COVID that impacted a lot
07:27of stuff. Then we have transportation, finance, healthcare, food, real estate. Yeah, there's a lot,
07:34a lot of people. Let's look at the lowest ones. Manufacturing, fintech, aerospace, energy, legal,
07:41so low numbers on those, high numbers on these. So really, really interesting.
07:47Let's go back up. Just want to look at our whole table really quickly, see what we got
07:52while we're looking at this stuff. And let's run this. Now we looked at the company, looked at the
07:57industry. I would really be interested to look at the country as well, which countries, at least from
08:02this data set. And we can copy, or we can go right here, country, because I believe that United States
08:11had the most. Holy mackerel. They had by far the most. Then India, this is 256,000 people lost their
08:22jobs. We'll look, I think we'll look at the dates in a little while, like a kind of like time
08:26series,
08:26like how many per year, per month, per day, or whatever we want to look at. But goodness gracious,
08:32that's a lot of people within just three years in the United States. India, Netherlands, Sweden,
08:37Brazil, Germany, United Kingdom, then it goes down and down and down. But these are just reported
08:43from this data set that I had gotten. So really, really interesting. Good night. The United States
08:49had much more than most, for sure. Let's actually look at that date real quick, or we can look at
08:56it
08:56by year. So we have this date. And if we do it like this, and we can, by date real
09:06quick. So this is
09:07going to do it by individual date. And let's order by, let's do one. This is the most recent date.
09:14So it's literally by date that it's reported. We don't want that. Let's do it by the year. So 2020,
09:212021, 2022, 2023. We can do that fairly easily. We'll use this year function.
09:29And we'll group by the year as well. Let's try running this. There we go. It looks like in 2020,
09:3980,000 people, 2021, 16,000, 160,000 in 2022. This looks like the worst year.
09:48And then it's only, we only have three months of data in 2023. There's 125,000. Holy smokes.
09:55So in 2023, it looks like we're ramping up because I'm recording this in 2023, about a month after this
10:00data set that we got this data set. There's 125,000 people around the world, you know, but just in
10:07those
10:07first three months. So this is going to be a lot higher than even 2022. That's pretty wild.
10:15Very, very interesting. One other one, while we're looking at group by, there was a column and you can
10:23go back and look at it if you'd like, but it's called stage. And this shows the stage of the
10:28company.
10:29And if we run this and we're all just looking at totally. But if you look at the stage of
10:34the
10:34company, this is like the different series that they're in, A, B, C, D. A, I believe is like a
10:40series A funding. That's like a super, super starting. Oh, this is like a seed phase. Then
10:45there's series A and then it goes up, up, up, up until usually they go, um, like they do IPO
10:51or they
10:51get acquired or something. Now, if we go up here and we do two descending, I want to see which
10:55one
10:55had the most. So this is post IPO. This is the Amazon, the Googles of the world, the large,
11:00large companies that are post IPO or initial public offering. Then there's unknown. We don't
11:05know which that is. Um, a lot of, you know, layoffs from acquisitions, C, D, B, all the way down.
11:13So
11:13it looks like, um, most of it's coming from, you know, these ones right here. Really, really
11:18interesting. Let's go look at percentages. I'm just going to literally cut trying to say
11:23literally, I'm going to literally copy these. Um, and with percentages, I don't think, uh,
11:32let me look at percentage. I don't think the sum is going to be a good indicator. I don't know
11:36if
11:36this is a good one to even look at because, and then we're looking at company right now
11:40because percentages refer to a percent of the company, right? So we don't have hard numbers
11:47because we don't know how large these companies are. So now that we're actually looking at this,
11:51this percentage laid off, isn't super relevant. Um, really the one that's kind of more,
11:57you know, has better, this is a better use for what we're looking at is this total laid off.
12:01Cause again, we don't know these sums we could, we could look at like the average, right? Um,
12:06but again, that just doesn't help us that much. I don't think, um, I think we're going to really
12:14dive into that too much is my, uh, is my feeling. Now, one thing that I would be really interested
12:21in is to kind of look at the progression of layoff, right? Uh, you could call this a rolling
12:28sum. So start at the very earliest of layoffs and do a rolling sum until the very end of these
12:34layoffs. Um, and let's go to the bottom. This is where it's going to start getting a little
12:38tougher. Um, and there's, you know, we're just doing a little bit of exploratory data analysis,
12:44you know, do it, digging into this a little bit. You can go and dig into this as much as
12:49you'd like.
12:49You don't have to just do what I'm doing, but I'm just trying to show you some stuff.
12:52Now let's try to do rolling total layoffs. Um, we could do that on the day. Although I feel like
12:59that's going to be way too many rows. Let's do it based off the month. So right here in this
13:03month.
13:05Now, let's see if we do just the month, let's do something. I'll show you the month and that's
13:09going to be an issue. And I'll, I'm in my head. I already know, but let's look at it.
13:13We could do something like select, um, from, and let's get this.
13:21There we go. So if we do, um, we'll do substring. Let me, uh, let's do substring
13:30and we want to pull out this month right here. So we'll go one, two, three, four, five, six.
13:36So start at position six. Um, and this is of course in the date column, we'll start at position six
13:43and then we'll take two. Let's just run this really quickly. And there's our month. So this,
13:49we could do this as month, right? Um, we're like this. Is that correct? Yeah. So as month,
13:59this is our month that we're doing it. Now, if we group on this and we do like something like
14:04a sum
14:04of total, uh, laid off, I think that's the column. And then we do a group by on this month.
14:12So it'd be
14:13like this right here. We'll do group by this. Let's try running this. We should be able to do month
14:25as
14:25well. Let's try this real quick as well. Cause I don't want to have this if I don't have to
14:30run
14:31it. Perfect. So the months right here don't show us the year. So if we're trying to get a rolling
14:37total of just the month, it's actually good work fine when we actually implement the rolling total
14:44use, um, you know, a window function. But the issue with this is it's just going to show us month.
14:48So
14:48this is 2020, this is January of 2020, 2021, 2022, 2023, any other years we have it. This is not
14:57a
14:57great rolling total. What if we did one all the way to, I want to say it's seven, six, seven.
15:04Let's
15:04try this. Now this is going to give us a much better and let's order this order by a one
15:10sending.
15:12This is just our first column. So now, uh, well, we should do it where it's not. Give me a
15:16second.
15:16I'm, I'm, I'm figuring this out as we go. We'll do where, uh, the month, write that where the month
15:24is not. No, I'm just going to get rid of that one.
15:34And of course, uh, that doesn't work because we're looking at the substring. So let's try doing this.
15:41There we go. Um, it just wasn't reading in that month that I was trying to use.
15:45Let's go down. Now, here's what we're going to do is we want to take it from the very first
15:50month
15:50and we're grouping everything. So these are all the layoffs from 2020 of 03. So that's March of 2020.
15:57And we have April, May, and these are the layoffs. So this is really good. This is exactly what I
16:02was
16:02imagining in my head. So we want this, and this is just, you know, 12 months in a year. And
16:08we go all
16:08the way to the bottom and I want to do a rolling sum of this. So let's see how we
16:12can do that.
16:13And we'll use this logic in a little bit. Let's copy this. And let's do select everything. We'll do
16:21right here. Now what we actually want to do now that I'm thinking about it is when you want to
16:25take
16:25this data and we want to do the rolling sum based off this exact thing. So we actually need to
16:31take
16:32this. Let's get rid of this and we'll do it with a CTE. So we'll say with, and we'll do
16:39rolling
16:39underscore total that we'll say as, and then we'll put this in here just like that. So with rolling
16:49total as, now we're going to say select, and we'll just do from here. Now what we need to do
16:56is we
16:57need to select the month. So let's go ahead and select that month and we'll take it just like
17:01this. So we'll select the month and we need to do a rolling total. All we have to do for
17:05that is the
17:06sum of which column we're doing. Let's actually change this real quick. We're going to call this as
17:17total off. I'm just going to keep it simple. So the sum of total off. So now we're doing that,
17:24but we want to do it over. And all we need to add into here is an order by. We're
17:28not going to
17:28partition by anything because in here we already did a group by. So it's, you know, kind of like
17:34partitioning it. We just need to say order by. We just need to order by the month, I believe. So
17:40let's try
17:40that. And let's run it. Let's do that. And we actually need to, since we're doing this, we need
17:50this at the end. And we can rename this if we'd like. So we can do this as rolling underscore
17:55total
17:56lowercase. Let's try running this and let's see what we get. Okay. And this looks correct. So
18:07starting in 2020 of 03, we had 9,000 layoffs. Then the next total we added onto here. Now
18:14this visually isn't the best. I would like the month right here as well. So let me actually add,
18:22um, let me create its own row, put a comma here. Then right here, I want to keep this total
18:30off
18:32so we can visually see better, much better. Okay. So we have the month and as it goes down,
18:39we're having more laid off. Now this is our rolling total. Here's essentially how this works. It starts
18:45with 9,628. Then it adds on the next month, which is 26,000, which equals 36,000. Then it
18:52adds on the
18:53next month and we get 62 adds on the next month, 69, right? It keeps going all the way down.
18:59This just
18:59shows each month, how many were laid off. And this shows a month by month progression all the way down
19:05to the bottom. So let's keep, let's just, you know, take a look. Then 2020 of 03, we had 9
19:09,000.
19:10By the end of 2020, we had about an 81,000 or so. Then at the beginning right here, all
19:17the way down
19:18to 2021. By the end of 2021, we only had 96,000. So 2021 was a good year. It looks
19:25like, um, comparatively
19:27we had 90, 80, well, let me see, 91,000 people let go. And here we only have 96,000
19:35let go. So
19:35that's what, uh, 81, that's only like 15,000 people. That's like nothing. Um, comparatively
19:41then in 2022, uh, things start ramping up dramatically. It looks like we have, um, 12,000
19:49people, 17,000, 16,000, and they're adding up. It's going from 97 all the way up to good night.
19:56Right. Um, right before the holidays in 2022 of this past year, I mean, we had, uh, 247,000
20:03people. So that's like a hundred, uh, and 30 some thousand. No, my math, my math's really
20:08bad. It's like 150,000. And then we only have, oh, we have even more here actually. And then
20:14we only have the first three months of 2023. So these months right here were really devastating
20:22just around the world. Now we can also break this out potentially by countries. So we can see how
20:29many per country, but this is just around the world. That's a lot of people losing their jobs
20:33all the way up to 383,000. So in this range, 383,000 from March of 2023, all the way
20:40back to
20:40March of 2020 lost their jobs. And this is just reported. I'm sure there was, uh, you know, much
20:46more than that, but this is that like companies, larger companies that have like series a funding
20:51IPOs, et cetera. Um, but a lot of small businesses went out of business. Um, so we don't, we don't
20:56have that information in this data set. So I think that's what we're going to do next is
21:00kind of look at the company maybe, because I'm always interested in the company and actually
21:04earlier, let's not do that one earlier. We're looking at the company, the sum of totally loft.
21:11Let's, um, let's bring this down. Let's run that. Cause that's what rolling total is, by
21:16the way, rolling totals are great. Um, really good for visualizations as well. Um, let's
21:22see. Yeah. So I want to take a look at these companies, but I want to see how much they
21:26were
21:27laying off per year. So instead of just looking at it as a total, we'll break it out by the
21:31year. Now I'm just going to warn you, this is probably going to, this most likely be our
21:35last one in the, in the lesson. This is going to be probably our hardest one yet. Um, potentially
21:40we'll see. Maybe the other one was earlier, uh, was harder earlier. Now let's use this
21:47kind of as, um, a starting point, but what we're going to need to do is we want to take
21:53the company, but I also want the date. So I need to do a comma and then date. We need
21:59our date here and I'm going to do that. I need to group by the date as well. So we'll
22:04do date and let's run this. All right. Now this is just doing the, you know, company
22:11and the exact date. We don't want to do that. Let's actually do the year. Let's just look
22:15at the year. I think that'll be plenty. You can also do the exact same thing as we did
22:18above with the substring. Um, although I think that's going to get a little messier, um, stuff,
22:24you know, just a thought let's run this. Okay. So now we're looking at just the year we're
22:30grouping by year and let's order by, uh, let's say the company and we'll do that in
22:37ascending. There we go. And let's run this. So now we have it open and let's see who, you
22:43know, you can see people who made multiple layoffs. This is in 2020, they let go of 200
22:46and then in 2023, they let go of 155 is a company I've never heard of. So this is already
22:52looking
22:53really good. Now let's say we wanted to use this. And what we want to do is we want to
22:58rank
22:59which years they laid off the most employees. Now this is just a small, uh, sample. We'll
23:05look at more on just a little bit. We can actually look at, um, let's just do three, uh, three
23:13descending. It's like this should be large companies. So, you know, some of these companies
23:17like Microsoft, even Amazon right here and Amazon right there, they let go of multiple or thousands
23:23of people in different years. So I want to rank those. I want to say, you know, the highest
23:29one, uh, based off of the laid off should be ranked number one. That's the year that
23:34they laid off the most people. Well, let's go ahead and try to do that. And we need to
23:39do is, uh, do a CTE. We'll start with that. Let me, um, let me add some more things down
23:46here. So we're good to go. So let's do, we'll do with, let's do a company. So this is going
23:54to be the company year, underscore year. We'll do it as, and that's what this is going to
23:59be. This is our company year and we can do select everything from company year. It's going
24:06to be the exact query that we're looking at. Let's go ahead and run this. Okay. So this
24:11is good. Now I do want to change these columns and I can do that right here. We'll do company.
24:16Um, let's call this years and then we'll do, I'm going to do total laid off again. So total
24:24underscore laid underscore off. This does some, right? Total laid off per year. Let's go ahead
24:30and run this now. There we go. We have company years and total laid off. So this looks much
24:36better. And what we're going to do is select everything, but we want to partition it, uh,
24:41probably based off this years right here. And then we want to rank it based off how many
24:46they laid off in that year. So we'll get to see who laid off the most people per year.
24:52Cause some companies like Amazon, they let, they let off multiple people per year, but was
24:56it the highest per year? That's kind of what we're going to look at. Um, so we'll do dense
25:00underscore rank, and we're going to do that over. Now we're going to partition by oops. That's
25:07not how you spell partition partition by. We want to partition by the years. So all of
25:12the 2021 laughs will be in the same, uh, partition. All the 2022 will be in the same partition and
25:18we'll do years. And we want to also order by the total laid off. Now we want to do that
25:24in descending. So we'll do total laid off descending. And then we want to, um, add this dense rank
25:31to it. So let's try it. Let's run this. Good night. That's a big one. So let's take a look.
25:40So in 2021, it looks like, um, or 2020, it looks like Uber had the highest and we want to
25:48take
25:48out these nulls. So let's do, um, where years, let's say is not null. And let's run that.
25:59There we go. So in 2020, and that's what we're partitioning on first, it looks like this is one,
26:03two, three. These are the top ones. Um, and let's order by, and let's do the, let's order by the
26:11rank.
26:12Um, first let's call this as bring it down. What do we want to call this? We'll call this as
26:23ranking.
26:23There we go. The order by ranking ascending. There we go. Now we have our ranking. So in 2020,
26:36this is the biggest one of layoffs 2021. This is the biggest layoff, I guess we'll have to take a
26:43look in meta in 2022. They had the biggest layoff in Google had the biggest layoff total, uh, for 2023.
26:49So this looks correct, but I kind of want to filter on this ranking to be able to only filter
26:57maybe the top, like five, um, companies per year. And I think we can do that. Let's actually get rid
27:06of this. I think what we should do is we should add this as another CTE and query off of
27:11that.
27:12So now we'll call this company underscore year underscore rank. So now we have the year rank as
27:19we'll have our query. Oops. Have our query. So now this is our company year rank. So now if we
27:27do
27:28select everything from company year rank, this, we run it. Okay. So now we have our rankings come down.
27:40Now we have our rankings, but I just want to filter it based off of that ranking. We'll say, uh,
27:45where
27:46ranking is greater than or equal to, let's say five. We'll look at the top five rankings. Let's run this.
27:53And I said greater than I wanted, uh, less than on that. That's looking good. Okay. So really quickly
28:01we have in 2020, we had, these are the top five people who laid people off Uber, booking.com,
28:09Groupon, Swiggy, Airbnb. In 2021, the largest layoff was ByteDance, which is TikTok, right? Uh,
28:17Katerra, Zillow. Uh, yeah, these are the top five. So 2021 or 2022 and 2023 were definitely the largest
28:24as well. We have Meta, 11,000 people, Amazon, Cisco, Peloton, and Carvana, as well as Philips.
28:32They tied. That's why we have the dense ranking because some of these will be ties. And then we
28:36have Google, uh, in 2023, all the way down to Dell. And these are all ones I know, Microsoft, Ericsson,
28:44Amazon, Salesforce, and Dell. So this is really, really interesting. Just looking at a year by year
28:50snapshot, right? These are the total laid off for each company. And we could even go back and change
28:55this for like industry or, you know, really whatever we want to change this to. This isn't
29:01just an interesting query in general to look at, you know, per year. And we could go back and change
29:06per month or lots of stuff we can change in here, but this is really interesting to me. Um, it
29:12just
29:12looks like a lot of the large tech companies had some, took some big L's, took some big hits. Um,
29:18let's recap this query really quickly in case, you know, it's tough to follow,
29:21but we created this query up here and we were looking at the company by the year and how many
29:28people they let off. Then right over here, we said with the company year, we changed these columns.
29:35This is our CTE. So we created our first CTE. Then we went and we gave it a rank and
29:42we wanted to,
29:43you know, filter on that rank. So we did this rank as another CTE. We just did a comma, had
29:49a second
29:49CTE and we hit off the first CTE, the company year, which is right here. We hit off our first
29:56CTE
29:57to make this second CTE. And then finally we, um, queried off of the final CTE. Definitely not an easy
30:04query to kind of think through and walk through, but I hope, you know, you're able to follow, um,
30:08because, you know, that's a, a really good query. This is something I've definitely done in a real
30:13job when I was working with a lot of healthcare data. This is, um, a lot of stuff that I
30:18would do.
30:18And so this is a, you know, pretty good, um, pretty good query to know how to do. But with
30:23that being
30:23said, uh, we are done with this lesson. I hope this wasn't too short. I don't know how long I
30:28ran,
30:28but, um, you know, we looked at a lot of different stuff. Let's go back to the top.
30:32Again, we were just exploring the data. We looked at lay it off a lot. Um, looked a lot at
30:38the
30:38company, uh, when these dates actually started for these layoffs in this dataset. We looked at the
30:44country, the actual year of laid off. Uh, then we went to a little bit more difficult things.
30:50We looked at it per month. So per month, um, how many layoffs they had. And then we did a
30:55rolling
30:56total. And this one was a pretty good one using that substring. Um, I love substrings, man.
31:02They're awesome. Or lady, they're awesome. Uh, and then we came down here and we did the one we
31:07just did with multiple CTEs in the company. I think it was a, a really, really good solid project.
31:12Um, combine that with that data cleaning project and man, you got just a really good start with
31:17some MySQL projects. And this one can be expanded upon. Don't stop where I stopped, right? Let me go
31:23back to the top. Don't stop where I stopped, right? This dataset has so much data in it. You can
31:29do a lot
31:30of different things. And even if you want to, you could go and find these companies right over here,
31:37and you could try to, uh, get their total, uh, total company that they had, and you could use this
31:44column a lot more. That'd be really interesting with some calculations there. So with that being
31:49said, that is the end of our exploratory data analysis project. I hope you enjoyed it. I hope
31:53you learned something both in the data cleaning project and in this exploratory data analysis
31:58project. That's what this is all about and getting the confidence and gaining the experience to
32:03create these projects and add those to your portfolios. Speaking of which, if you haven't
32:07already check out my video on how to create a free portfolio website, uh, using GitHub. Awesome. I
32:14highly recommend it. And you can add these to your portfolio. So with that being said, thank you so
32:18much for watching. I really appreciate it. If you liked this video, if you learned anything at all,
32:23be sure to like, and subscribe below, check out my channel for tons of other videos,
32:26just like this one and more. I will see you in the next video.