LeetCode 1581 Interview SQL Question with Detailed Explanation | Practice SQL

[@GajjalaDeepthi]

We can do this in simple way SELECT customer_id,count(visit_id) as count_no_trans FROM Visits where visit_id not in (SELECT visit_id from Transactions) GROUP BY customer_id order by count_no_trans Desc

[@PIYUSH-lz1zq]

how r we getting customer_id 54 ? he has visited with id 5 and done 3 tranasction !!!

[@kavyabanka4482]

Exactly I too have same doubt

[@iit_motivation]

he visited 3 times but make transcation only 1 times@@kavyabanka4482

[@AsifAli-yt1vf]

that 54 belongs to 7 and 8, after adding (1+1) = 2

[@wishimaunicorn]

WOW, thank you for the super clear explaination!

[@asmitamhetre]

amazing

[@MdMohaiminulIslamKhan]

Good Videos. I have started my leetcode problem solving journey and your videos are well described and easy to follow. Keep up the good work

[@gradientO]

checking null for left joins is nice

[@sakshipathak1855]

fabulous explanation!

[@EverydayDataScience]

Glad that you found the video useful 😊

[@erichuang1863]

Hi, Can you explain the Count(v.visit_id) part please? I get everything before that, but what exactly are we counting using this expression, and are we counting from the original visit table, or the table we formed? (Like 4 30 Null Null Null )? Thanks!

[@EverydayDataScience]

Hi Eric, So once we join the Visits table with Transactions table and keep only those rows where transaction_id IS NULL, it means that now we have all the visits made by different customers where there was no transaction (think of it like people going to a shopping mall on different days, some people buy stuff and some don't, also same person can buy on one day and don't buy anything on another day etc). Once we have the list of customer_id and visits where there was no transaction made (remember we filtered WHERE transaction_id IS NULL), then when we group by customer_id and perform a count(visit_id), it is counting that for each customer, on how many visits there were no transactions made. We are counting from the new table we made (don't get confused by v.visit_id, I have written v.visit_id because visit_id column is in both the tables and if you don't specify which visit_id column you want to count, SQL will give an error. Then you might think why can't I do COUNT(t.vist_id) since visit_id is in transactions table as well. That's because after you perform the LEFT JOIN and use the WHERE clause, t.visit_id will only have NULL values and it doesn't make sense to count NULL values). Let me know if it's still not clear.

[@erichuang1863]

@@EverydayDataScience Got it! Thanks for your help! If I have other further questions regarding other leetcode problem, do you have an email or somewhere else that I can get some help?Thanks!!!!!!

[@EverydayDataScience]

@@erichuang1863 Sure. [e-mail]

[@kavyabanka4482]

​@@EverydayDataSciencecustomer id 54 had transaction .Then how come It is in output

[@kian69topgun1]

why use "Where" and not "Having" ?

[@yashwardhan5052]

😂😂

[@basavareddy8595]

Another level of explanation ❤

[@HariPrasath.P]

Thank you sir.

[@divyanshumnit]

select customer_id,count(customer_id) as count_no_trans from (select customer_id from Visits where visit_id NOT IN (select distinct(visit_id) from Transactions))a group by customer_id

[@mlvprasadofficial]

32