Objective

Write a SQL query to calculate the activation rate of users who signed up.

Table Schema:

• emails

• texts

Task Requirements:

• Determine the total number of users who signed up via the emails table
• Identify users who confirmed their signup via the texts table
• Calculate the activation rate as a percentage
• Round the result to 2 decimal places
  
  

Output columns: activation_rate

SELECT
    ROUND(
        COUNT(DISTINCT t.user_id)::DECIMAL /
        COUNT(DISTINCT e.user_id) * 100,
        2
    ) AS activation_rate
FROM emails e
LEFT JOIN texts t
    ON e.user_id = t.user_id
    AND t.signup_action = 'Confirmed'
WHERE e.signup_action = 'Signup';

Explanation

Step 1: Filtering Signed-Up Users

FROM emails e
...
WHERE e.signup_action = 'Signup'

The WHERE clause filters the emails table to include only users who performed the 'Signup' action. This gives us our base pool of users who initiated the signup process.

Step 2: Matching Confirmed Users with LEFT JOIN

LEFT JOIN texts t
    ON e.user_id = t.user_id
    AND t.signup_action = 'Confirmed'

A LEFT JOIN connects the emails table to the texts table. The confirmation filter is placed in the ON clause (not WHERE) so that unconfirmed users are preserved as NULL rather than being excluded entirely. This is critical for an accurate denominator.

Step 3: Counting Distinct Users

COUNT(DISTINCT t.user_id)  -- confirmed users
COUNT(DISTINCT e.user_id)  -- all signed-up users

DISTINCT ensures each user is counted only once, even if they have multiple rows in either table. Users without a confirmation match will have NULL for t.user_id, which COUNT automatically ignores.

Step 4: Calculating the Percentage

COUNT(DISTINCT t.user_id)::DECIMAL / COUNT(DISTINCT e.user_id) * 100

The ::DECIMAL cast converts the numerator to a decimal type before division, preventing integer truncation. Multiplying by 100 converts the ratio to a percentage.

Step 5: Rounding the Result

ROUND(..., 2)

The ROUND function with 2 as the second parameter ensures the activation rate is displayed to exactly 2 decimal places.