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! 💡

[@srushtikale7455]

Great Explanation I finally understood aggregate function for confirmation_rate

[@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

[@seematripathy]

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

[@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 🎉💐

[@akhilpratapsingh3223]

Great Explanation Bhaiya !!!!

[@learnwithchirag]

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

[@vaibhav5269]

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

[@nikhildoye9671]

why did it work? since action is unique column?

[@Ayan_Pradh07]

Amazingly Explained!!

[@learnwithchirag]

Glad it was helpful! Keep Learning 💯💐

[@Meme_-883]

nicely explained

[@learnwithchirag]

Thanks for liking. Keep Learning 💯

[@civilizedmonster]

Great, Thank you!

[@learnwithchirag]

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

[@apoorvpradhan5125]

Thank you bhaiya.

[@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

[@rishavraj7318]

bhai best explanation

[@learnwithchirag]

Thanks a lot. Keep Learning 💯🎉