LEFT JOIN between the Signups and Confirmations tables on the user_id column.AVG function with a conditional statement inside to calculate the confirmation rate. The condition checks if the action is 'confirmed' and assigns a value of 1, otherwise 0.user_id to calculate the confirmation rate for each user.ROUND function to round the confirmation rate to two decimal places.user_id and the calculated confirmation_rate for the final output.
erDiagram
Signups {
int user_id PK
datetime time_stamp
}
Confirmations {
int user_id PK
datetime time_stamp PK
ENUM action
Signups_user_id FK
}