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! 💡
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!
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
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
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.
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! 💡
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!
Glad you enjoyed it!...Do share with your friends too 🎉💐
Great Explanation
I finally understood aggregate function for confirmation_rate
Informative video
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
Great, Thank you!
Glad it was helpful to you 😄. Keep watching :-)
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
Great Explanation Bhaiya !!!!
Your comments keep me motivated! Keep Learning & Supporting:-)
Thanks for the solution. Can you please solve this problem in postgressql ? I am getting divided by zero error.
Amazingly Explained!!
Glad it was helpful! Keep Learning 💯💐
Thank you bhaiya.
nicely explained
Thanks for liking. Keep Learning 💯
bhai best explanation
Thanks a lot. Keep Learning 💯🎉
Can we use AVG(c.action='confirmed')?
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.
why we can't use count() instead of sum() in the numerator
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.
I had the same doubt, amazing explanation of the problem and doubt indeed! Keep going Chirag!
@@learnwithchirag THANKS FOR THE EXPLANATION
you didn't write c.action,but it still worked in 1st line
why did it work? since action is unique column?