Day #2 [SQL Questions] 1934. Confirmation Rate

I will try to document, my solutions of the leetcode SQL 50 study plan.

The question for today is 1934. Confirmation Rate [https://leetcode.com/problems/confirmation-rate/description/?envType=study-plan-v2&envId=top-sql-50]

The Problem

Table: Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
user_id is the column of unique values for this table.        

?

Table: Confirmations

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
user_id is a foreign key (reference column) to the Signups table.        

Write a solution to find the confirmation rate of each user.

The Intuition:

• Use left join so we can generate confirmation rate for users who haven't created any request. As confirmation rate for such users is 0.
• 2 versions of join table are required one which counts total_requests made and total_confirmed_requests out of them.

The Solution:

• new table is the plain left join of both tables
• new_con holds the count of all the users who has confirmed requests. Note: doesn't have records for users who have no confirmed requests.
• new_total holds count of all requests made by an user. it's value is 1 for users who made no requests as we have taken the id from Signups table to count
• In the end we join both the latest table and generate a average. COALESCED for records that are missing in new_con

with new as (
    select s.user_id as user_id,c.user_id as c_id, action
    from Signups s
    left join
    Confirmations c
    on s.user_id=c.user_id
),
new_con as (
    select new.user_id,count(new.c_id) as cnt from new 
    where new.action='confirmed' 
    group by new.user_id
),
new_total as (
    select new.user_id,count(new.user_id) as cnt2 from new 
    group by new.user_id
)
select new_total.user_id, 
round(COALESCE( new_con.cnt/new_total.cnt2 , 0) , 2) as confirmation_rate
from new_total left join new_con 
on new_con.user_id=new_total.user_id;        

The Optimised Solution:

• Avg() function can sum only the confirmed cols, using an IF

SELECT s.user_id, 
  ROUND(AVG(IF(c.action='confirmed',1,0)),2) as confirmation_rate 
FROM Signups s
LEFT JOIN Confirmations c using (user_id)
GROUP BY s.user_id        

For more, Day 1 solution.