LeetCode Medium 1934 Interview SQL Question with Detailed Explanation

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

[xzlog]

00:01 Solving LeetCode medium question on confirmation rate
01:28 Calculating confirmation rate using SQL query
02:58 Using GROUP BY and SUM to count confirmed actions for each user
04:25 Calculating confirmation rate for user requests
05:55 Perform a left join between the signups and confirmations table on the user ID.
07:26 Handling null values in the confirmation rate calculation
08:56 Calculating confirmation rate by grouping user IDs
10:30 Performing calculation and handling confirmation rates using SQL
Thank you so much

[afiqaiman]

Thank you, love this easy answer with best explanation 😊. I hope you never stop make leetcode questions tutorial like this

[vijaysakthivel]

Great explanation sir. Thank you so much

[minimumeffort]

This is actually a very good solution. Thanks.

[xuyiang]

Thank you so much, great explaination ever by showing output inbetween really help me a lot in understanding the logic of this shit.

[randb]

Could you have used coalesce instead of case when?

[muddassirnazar]

Easy Solution:/* Write your T-SQL query statement below */
select s.user_id, round(cast(sum(case when action='confirmed' then 1 else 0 end) as float)/count(*), 2) as confirmation_rate
from signups s
left join confirmations c
on s.user_id=c.user_id
group by s.user_id

[lucyqin]

Nice solution! Can I please get some understanding here why we do not need to use IF or case here to tell if it is true for the condition c.action='confirmed'? Thanks a lot!

[nakulmehta]

can anyone help me i am not getting when to apply which join i know the concepts of join but face difficulty while solving questions

[kushaleregowda]

Thank you so much for your patience and detailed expectations. I’m at intermediate level in sql but I need to get into advanced level. Hence can you please suggest the best way/ approach/resources to follow..

[mickyman]

select user_id, round(ifnull(sum(action='confirmed'), 0)/count(*), 2)
as confirmation_rate
from Signups s
left join
Confirmations c
using(user_id)
group by s.user_id;