Confirmation Rate | Leetcode 1934 | Crack SQL Interviews in 50 Qs #mysql #leetcode

Want to crack SQL interviews? Check out our latest video!!!

A 50-questions SQL study plan to ace any interview.
This tutorial will walk you through the solution in easy steps. This is an easy-level question from Leetcode.

Consistency is what transforms average into excellence.
So be consistent & keep Coding💻!!

Timecodes
0:00 - Introduction
0:16 - Question Explanation
2:22 - Understanding Concept with the help of Example
4:42 - Writing & Explanation of SQL Query
9:06 - Outro
Connect With Me -

#coding
#leetcode
#mysql
#sql
#interview
#sqlinterview
#sqlinterviewquestionsandanswers
#sql_server
#article
#learnwithchirag
#confirmation
#rate
#shorts
#shortvideo

[learnwithchirag]

Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on


Instagram Handles :-



Let's stay connected and keep the creativity flowing! 💡

[dataman]

loved the way how you broke it down step by step and showed us output and also pointed out the errors. Signs of a great teacher who are hard to find. Thank you!

[shashibhagat]

we can do using avg() also


select s.user_id, ROUND(AVG(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END), 2) as confirmation_rate
from signups as s
left join
confirmations as c
on s.user_id=c.user_id
group by s.user_id

[srushtikale]

Great Explanation
I finally understood aggregate function for confirmation_rate

[seematripathy]

Thanks for the solution. Can you please solve this problem in postgressql ? I am getting divided by zero error.

[arpitadeshmukh]

Any idea why my round function doesn't work in MS SQL server
with cte as(
select s.user_id, isnull(count(c.user_id), 0) as total
from signups s
left join confirmations c
on s.user_id = c.user_id
group by s.user_id)

select c.user_id,
confirmation_rate =
case when total = 0 then 0
else round((select count(user_id) from confirmations where action = 'confirmed')/total, 2)
end
from cte c

[vaibhav]

you didn't write c.action, but it still worked in 1st line

[AnuragSingh-jhot]

why we can't use count() instead of sum() in the numerator

[xgaming]

Can we use AVG(c.action='confirmed')?