commit 7eb314699d624efe9643cc6cd675503932745f71 parent 2376d6f51a7d306c27626a75effb2c13398b92be Author: AndrewLockVI <andrewlaack1@gmail.com> Date: Fri, 14 Jul 2023 19:12:40 -0500 Completed confirmation rate using mySQL Diffstat:
| A | confirmation-rate/confirmation-rate.sql | | | 11 | +++++++++++ |
1 file changed, 11 insertions(+), 0 deletions(-)
diff --git a/confirmation-rate/confirmation-rate.sql b/confirmation-rate/confirmation-rate.sql @@ -0,0 +1,11 @@ +--Given a list of users return the fraction of times +--that the user was successfully confirmed +--if they have no auth attempts return 0. +SELECT Signups.user_id , ROUND(COALESCE(AVG( + CASE WHEN Confirmations.action = 'confirmed' THEN 1 + WHEN Confirmations.action <> 'confirmed' THEN 0 END +), 0),2) AS confirmation_rate +FROM Signups +LEFT JOIN Confirmations +ON Confirmations.user_id = Signups.user_id +GROUP BY Signups.user_id