confirmation-rate.sql (426B)
1 --Given a list of users return the fraction of times 2 --that the user was successfully confirmed 3 --if they have no auth attempts return 0. 4 SELECT Signups.user_id , ROUND(COALESCE(AVG( 5 CASE WHEN Confirmations.action = 'confirmed' THEN 1 6 WHEN Confirmations.action <> 'confirmed' THEN 0 END 7 ), 0),2) AS confirmation_rate 8 FROM Signups 9 LEFT JOIN Confirmations 10 ON Confirmations.user_id = Signups.user_id 11 GROUP BY Signups.user_id