LeetCode Medium 1193 Interview SQL Question with Detailed Explanation

Question: leetcode.com/p...
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

[@gajendragada_dynamites]

This is the most underrated channel of you tube in linkedin also everyone is busy in praising Sumit mittal sir only ,i will proudly say i have learnt SQL from this channel

[@EverydayDataScience]

Thanks for such kind words 😊 Glad that this channel has been helpful for you.

[@rohitbatra8396]

This is really underrated channel, I wish you get huge amount of subscribers in coming days. I am solving a lot of SQL questions now days and If I get stuck somewhere I type that leetcode number and in that search list I always choose your video to watch. Hats off :)

[@tejaswaniguttula5961]

Thanks a lot sir.
When i solved it by myself. I failed in test case bur with your explanation it was done ✅ sir. Thank you so much sir

[@EverydayDataScience]

Glad that you found the video useful 😊

[@alfabinomial6183]

in your video u have taught us how to think and how to solve the problem !! best tutorial ever on Database management system !

[@saisushanthdurvasulabitsian]

thank you @Everyday Data Science for the video , very useful, one small change we could do to make it compact is that we could use the month alias in the group by clause instead of repeating the year month extraction logic as follows:
select DATE_FORMAT(trans_date,'%Y-%m') as month
, country
, count(id) as trans_count
, sum(case
when state = "approved" then 1
else 0 end) as approved_count
, sum(amount) as trans_total_amount
, sum(case
when state = "approved" then amount
else 0 end) as approved_total_amount
from Transactions
group by month,country
please review and let me know your thoughts , thanks

[@ekosetiawan_indo]

hmm, what if there is data from different year but same month, will this query working?

[@saisushanthdurvasulabitsian]

@@ekosetiawan_indo thanks for the question , yes we are asked to group by year_month in the question which is unique for a given month and year and we are extracting that part here and the query works! pls let me know if it dint work for you or any issue you faced , thanks

[@rishikaverma9846]

why does my code show this error? You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as approved_total_amount
from Transactions
group by date_format(trans_date, '' at line 2
select date_format(trans_date, '%Y-%m') as month, country,count(id) as trans_count, sum (case when state='approved' then 1 else 0 end) as approved_count, sum(amount) as trans_total_amount, sum(case when state='approved' then anount else 0) as approved_total_amount
from Transactions
group by date_format(trans_date, '%Y-%m'), country

[@EverydayDataScience]

I guess this is because you haven’t ended the case when statement. It should be:
…, sum(case when state=‘approved’ then amount else 0 END) as approved_total_amount…..
Try it and let me know if this works 😊

[@manojyadav6967]

count() only count the non-null values only.just for info

[@EverydayDataScience]

Agreed. Thanks for sharing that, Manoj 😊

[@sumans6033]

Year is not getting