Confirmation Rate | Leetcode 1934 | Crack SQL Interviews in 50 Qs

[@learnwithchirag]

Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on
Instagram Handles :-
@createwithchirag - instagram.com/createwithchirag/
@learn.with.chirag - instagram.com/learn.with.chirag/
LinkedIn: www.linkedin.com/in/chirag-sehgal-9200111b8/
Let's stay connected and keep the creativity flowing! 💡

[@dataman17]

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!

[@learnwithchirag]

Glad you enjoyed it!...Do share with your friends too 🎉💐

[@srushtikale7455]

Great Explanation
I finally understood aggregate function for confirmation_rate

[@CHIRAGARORA-gj4cr]

Informative video

[@shashibhagat490]

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

[@civilizedmonster]

Great, Thank you!

[@learnwithchirag]

Glad it was helpful to you 😄. Keep watching :-)

[@arpitadeshmukh210]

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

[@akhilpratapsingh3223]

Great Explanation Bhaiya !!!!

[@learnwithchirag]

Your comments keep me motivated! Keep Learning & Supporting:-)

[@seematripathy]

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

[@Ayan_Pradh07]

Amazingly Explained!!

[@learnwithchirag]

Glad it was helpful! Keep Learning 💯💐

[@apoorvpradhan5125]

Thank you bhaiya.

[@Meme_-883]

nicely explained

[@learnwithchirag]

Thanks for liking. Keep Learning 💯

[@rishavraj7318]

bhai best explanation

[@learnwithchirag]

Thanks a lot. Keep Learning 💯🎉

[@xgaming8485]

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

[@learnwithchirag]

Using AVG(c.action='confirmed') wouldn't give you the desired result because AVG() calculates the average of numeric values. The expression c.action='confirmed' evaluates to either true or false (1 or 0 in MySQL's boolean context), not a numeric value that AVG() can operate on.

[@AnuragSingh-jh2ot]

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

[@learnwithchirag]

The 'COUNT()' function in SQL counts the number of rows in a result set, and it does not work with conditional expressions like action = 'confirmed' directly inside it.
If you want to count the number of rows where action is 'confirmed', you typically need to use a conditional statement within the 'SUM()' function.

[@ninadthawait4458]

I had the same doubt, amazing explanation of the problem and doubt indeed! Keep going Chirag!

[@ayushtibrewal4535]

@@learnwithchirag THANKS FOR THE EXPLANATION

[@vaibhav5269]

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

[@nikhildoye9671]

why did it work? since action is unique column?