Introduction
Question: leetcode.com/problems/market-analysis-i/
In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.
LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.
If you found this helpful, Like and Subscribe to the channel for more content.
#LeetCodeSQL #FAANG #SQLinterviewQuestions
Content
Hey guys welcome back to the channel, so this channel every data science is all about trying to learn the different concepts involved in data science.
By practicing a lot of questions in this video, I am going to solve this question on lead code regarding market analysis, part one and try to walk you through how we can develop solutions to such problems.
The difficulty level of this question is medium okay, so, let's jump right in we are given a table called users with three different columns: user id join date and favorite brand user id is the primary key of this table.
This this table has info of the users of an online shopping website where users can sell and buy items.
Okay, we are also given a second table called orders with five different columns order, id order date, item id buyer id and seller id order.
Id is the primary key of this table item id is a foreign key to the items table.
So basically, there is a third table as well: buyer id and seller id are foreign keys to the users table right, so buyer id and seller id are foreign gives to the users table right basically to the user id.
They are referencing to the user, id column of the users table.
Okay, there also a third table called items with two different columns item id and item brand item id being the primary key of this table.
We are asked to write a sql query to find for each user, the join date and the number of orders they made as a buyer in 2019, so they made as a buyer how many times they ordered right as a buyer.
The order of the result doesn't matter.
Let's go through this example, so this is the users table four different users.
This is the orders table and this is the items table right.
So what we need to do is so, as a buyer, you know a user id one.
So basically, buyer id is one means this person with user id one bought and and who sold it.
The person with user id 2 right user id is this one okay.
So the first thing that we can do is we can join these two tables using the user id column and the buyer id column of the orders table.
Right so remember, the question said: buyer id as well as seller id are the foreign keys of users table.
So when we, you know, join this users table with a user id column on buyer id column right.
So basically, what we are trying to do is we are trying to see.
Okay, when were these different users, actors are acting as a buyer right.
So the first thing we should do is from this table called users aliased as you, we can left to join right.
The orders table aliased as o on: u dot user id is equal to o dot buyer id right.
So once we have this, so basically, how will this work so user id1? It will go to orders, table and see okay, so there is first match.
So this row will have you know this information.
Then there will be a second match, so this user id will have two rows for two.
It will have two rows for three and four: it will have one and one rows right.
So whenever a user acts as a as a buyer right, so it will have all those rows now, once we have this, we need to find that in 2019 how many times these users acted as a buyer right.
So that is once we have this.
If we group by the user id right and try to find out that for each of the user id what is the number of times, they acted as a buyer in 2019 right so group by u dot user id right.
So we can group by user id and then return, u dot user id, and then we need to only count the order date when the year is in 2019 right.
So when year is equal to 2019, so we are going to apply the same case when statement as we did in our just previous video right.
So what we do is, if case when right so case when the year in order date, column is in which table orders table right, so o dot order date is equal to 2019.
Then you return 1 else.
You return 0 right, so we grouped by the user id right.
So after performing this join, it will have.
You know two rows this this row with this data right and this row with this data right this entire data, because there are two matches right: buyer id one, one right! So now what will happen so for first row? It will find out that yes year of order date is indeed equal to 2019.
So it will return one and for this row it will return zero and once you sum this right, so if you perform a sum right and since we started a case when statement, we need to end it as well, and then we perform a sum right.
So basically, what this is going to do for user id 1, it will return 1 and 0., so 1 plus 0 is 1 right.
So- and this is basically what the count when the user id acted as a buyer in 2019 right and what is the alias of the column, so orders in 2019 right so as orders in 2019 right.
Let me go ahead and run this.
You know.
Obviously this is going to give a wrong answer, because it is not complete because we still need to add this join date.
But let's see if we are getting the correct values for buyer id and the orders in 2019 column right.
Okay.
So if you see for one user id one, we are getting the orders one for two two for three: we are getting zero for four.
We are getting zero and it is exactly the same thing right so now we have this information.
The only thing we need to do is we need to get the join date column right.
So we can save this as a common table.
Expression right and this common table expression has two different columns: user id, as well as the orders in 2019.
Now, from this common table- expression, aliased sc- let's join the users table aliased as you on c dot, user id user id is the column of commentable expression and users table also has a user's user id column.
So u dot user id and then what we need to keep is we need to keep three columns right: the user id column, which should be aliased as buyer id then the join date column and then the orders in 2019 column so return the user id column right and we can keep user id from both.
But let's keep it from the common table expression, because that is why we calculated common table expressions so c dot user id and we need to aliase it as buyer id right.
Then.
The join date join date is in users table.
So u dot join date and then the orders in 2019 column.
So it is in common table expression, so or c dot orders in 2019, okay.
So this looks good.
We don't need to order by any column.
So let me go ahead and run this to see what happens.
So this is accepted.
Our output is same as expected, output.
Let me go ahead and submit it to see if it passes all the test cases.
So this passes all the test cases, and this is how we do it again seems kind of tricky but like if you follow the logic that yes for each of the users, we first calculate you know the count when they acted as a buyer in 2019 and how do we get the 20 90 thing? We are using case when statements returning ones and zeros and calculating the sum- and if you remember from the very previous video we can use sum to basically do a count and we can use this logic of returning ones and zeros right.
So once we have that we have for each user id the number of times they acted as a buyer in 2019, and then we use the users table to join it so that we can get the join date right.
Let me know if you can think of a better or a more efficient solution than this comment down below and until then I will see you guys in the next video.