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