1934. Confirmation Rate

Problem¶

Given all signed-up users and their confirmation attempts, return each user's confirmation rate:

Rate = confirmed attempts / all attempts.
If a user has no attempts, rate = 0.00.
Round to 2 decimals.
Output can be in any order.

Input:

# Signups
--------+---------------------
user_id | time_stamp
--------+---------------------
3       | 2020-03-21 10:16:13
7       | 2020-01-04 13:57:59
6       | 2020-12-09 10:39:37

# Confirmations
--------+---------------------+-----------
user_id | time_stamp          | action
--------+---------------------+-----------
3       | 2021-01-06 03:30:46 | timeout
7       | 2021-06-12 11:57:29 | confirmed
7       | 2021-06-13 12:58:28 | confirmed

Output:

--------+------------------
user_id | confirmation_rate
--------+------------------
3       | 0.00
7       | 1.00
6       | 0.00

Key trick¶

Use a LEFT JOIN from Signups to Confirmations, then average a boolean-like value:

1 for confirmed
0 for timeout
NULL for no joined confirmation rows

Then replace the final NULL with 0.

Trap¶

Common mistakes:

Using an INNER JOIN, which drops users with no confirmations.
Dividing two integer counts without forcing decimal arithmetic in SQL.
Counting signup rows as confirmation attempts after a LEFT JOIN.
Forgetting to round to exactly 2 decimals.
In Pandas, using count after merge in a way that counts the artificial missing row.

Why is it interesting?¶

It tests whether you understand aggregation after outer joins, null handling, conditional aggregation, and the clean equivalence between SQL AVG(CASE ...) and Pandas boolean means.

SQL solution¶

SELECT
    s.user_id,
    ROUND(
        COALESCE(
            AVG(
                CASE
                    WHEN c.action = 'confirmed' THEN 1.0
                    WHEN c.action = 'timeout' THEN 0.0
                END
            ),
            0.0
        ),
        2
    ) AS confirmation_rate
FROM Signups AS s
LEFT JOIN Confirmations AS c
    ON c.user_id = s.user_id
GROUP BY s.user_id;

Pandas solution¶

import pandas as pd


def confirmation_rate(signups: pd.DataFrame, confirmations: pd.DataFrame) -> pd.DataFrame:
    # A confirmed action is 1, timeout is 0; the mean is the confirmation rate.
    rates = confirmations.assign(
        confirmation_rate=confirmations["action"].eq("confirmed").astype(float)
    )

    # Aggregate before joining to avoid counting users with no attempts.
    rates = (
        rates.groupby("user_id", as_index=False)["confirmation_rate"]
        .mean()
    )

    # Keep all signed-up users, fill missing rates with 0, then round.
    result = signups[["user_id"]].merge(rates, on="user_id", how="left")
    result["confirmation_rate"] = result["confirmation_rate"].fillna(0).round(2)

    return result[["user_id", "confirmation_rate"]]

Pytest test¶

import sqlite3

import pandas as pd
import pytest
from pandas.testing import assert_frame_equal


SQL_QUERY = """
SELECT
    s.user_id,
    ROUND(
        COALESCE(
            AVG(
                CASE
                    WHEN c.action = 'confirmed' THEN 1.0
                    WHEN c.action = 'timeout' THEN 0.0
                END
            ),
            0.0
        ),
        2
    ) AS confirmation_rate
FROM Signups AS s
LEFT JOIN Confirmations AS c
    ON c.user_id = s.user_id
GROUP BY s.user_id;
"""


def confirmation_rate(signups: pd.DataFrame, confirmations: pd.DataFrame) -> pd.DataFrame:
    rates = confirmations.assign(
        confirmation_rate=confirmations["action"].eq("confirmed").astype(float)
    )

    rates = (
        rates.groupby("user_id", as_index=False)["confirmation_rate"]
        .mean()
    )

    result = signups[["user_id"]].merge(rates, on="user_id", how="left")
    result["confirmation_rate"] = result["confirmation_rate"].fillna(0).round(2)

    return result[["user_id", "confirmation_rate"]]


def run_sql(signups_data, confirmations_data):
    con = sqlite3.connect(":memory:")

    con.execute("CREATE TABLE Signups (user_id INTEGER, time_stamp TEXT)")
    con.execute(
        "CREATE TABLE Confirmations (user_id INTEGER, time_stamp TEXT, action TEXT)"
    )

    con.executemany(
        "INSERT INTO Signups (user_id, time_stamp) VALUES (?, ?)",
        signups_data,
    )

    con.executemany(
        "INSERT INTO Confirmations (user_id, time_stamp, action) VALUES (?, ?, ?)",
        confirmations_data,
    )

    return pd.read_sql_query(SQL_QUERY, con)


def make_pandas_inputs(signups_data, confirmations_data):
    signups = pd.DataFrame(signups_data, columns=["user_id", "time_stamp"])
    confirmations = pd.DataFrame(
        confirmations_data,
        columns=["user_id", "time_stamp", "action"],
    )

    return signups, confirmations


def normalize(df):
    return (
        df[["user_id", "confirmation_rate"]]
        .sort_values("user_id")
        .reset_index(drop=True)
        .astype({"user_id": "int64", "confirmation_rate": "float64"})
    )


@pytest.mark.parametrize(
    "signups_data, confirmations_data, expected_data",
    [
        (
            [
                (3, "2020-03-21 10:16:13"),
                (7, "2020-01-04 13:57:59"),
                (2, "2020-07-29 23:09:44"),
                (6, "2020-12-09 10:39:37"),
            ],
            [
                (3, "2021-01-06 03:30:46", "timeout"),
                (3, "2021-07-14 14:00:00", "timeout"),
                (7, "2021-06-12 11:57:29", "confirmed"),
                (7, "2021-06-13 12:58:28", "confirmed"),
                (7, "2021-06-14 13:59:27", "confirmed"),
                (2, "2021-01-22 00:00:00", "confirmed"),
                (2, "2021-02-28 23:59:59", "timeout"),
            ],
            [
                (2, 0.50),
                (3, 0.00),
                (6, 0.00),
                (7, 1.00),
            ],
        ),
        (
            [
                (1, "2020-01-01 00:00:00"),
                (2, "2020-01-02 00:00:00"),
            ],
            [],
            [
                (1, 0.00),
                (2, 0.00),
            ],
        ),
        (
            [
                (1, "2020-01-01 00:00:00"),
                (2, "2020-01-02 00:00:00"),
            ],
            [
                (1, "2021-01-01 00:00:00", "confirmed"),
                (1, "2021-01-02 00:00:00", "confirmed"),
                (1, "2021-01-03 00:00:00", "timeout"),
                (2, "2021-01-04 00:00:00", "timeout"),
            ],
            [
                (1, 0.67),
                (2, 0.00),
            ],
        ),
    ],
)
def test_confirmation_rate_sql_and_pandas(
    signups_data,
    confirmations_data,
    expected_data,
):
    expected = pd.DataFrame(
        expected_data,
        columns=["user_id", "confirmation_rate"],
    )

    sql_result = run_sql(signups_data, confirmations_data)

    signups, confirmations = make_pandas_inputs(signups_data, confirmations_data)
    pandas_result = confirmation_rate(signups, confirmations)

    assert_frame_equal(normalize(sql_result), normalize(expected))
    assert_frame_equal(normalize(pandas_result), normalize(expected))

Comment on my solution¶

Your solution is correct and interview-ready.

The SQL approach is good because it aggregates first, then LEFT JOINs to keep users with no confirmations.
The Pandas approach mirrors the SQL logic clearly.
A slightly shorter SQL alternative is to aggregate directly after the LEFT JOIN, but your CTE version is often more readable.
In Pandas, naming the temporary boolean column count is a bit misleading because it stores 0 or 1, not a count.

WITH confirmation_rates AS (
    SELECT
        user_id,
        ROUND(1.0 * AVG(
            CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END
        ), 2) AS confirmation_rate
    FROM Confirmations
    GROUP BY user_id
)
SELECT
    s.user_id,
    COALESCE(c.confirmation_rate, 0.00) AS confirmation_rate
FROM Signups AS s
LEFT JOIN confirmation_rates AS c
    ON c.user_id = s.user_id;

import pandas as pd

def confirmation_rate(signups: pd.DataFrame, confirmations: pd.DataFrame) -> pd.DataFrame:
    confirmation_rates = confirmations.copy()
    confirmation_rates["count"] = (confirmation_rates["action"].eq("confirmed")).astype(int)
    confirmation_rates = (
        confirmation_rates.groupby("user_id", as_index=False)
        .agg(confirmation_rate=("count","mean"))
    )
    result = (
        signups.merge(
            confirmation_rates,
            on="user_id",
            how="left"
        )
    )
    result["confirmation_rate"] = result["confirmation_rate"].fillna(0).round(2)
    return result[["user_id", "confirmation_rate"]]

Extra¶

CASE returns NULL when no ELSE clause¶

In the following SQL snippet, does the CASE return NULL value when c.action is neither one of 'confirmed' nor 'timeout'?
CASE
    WHEN c.action = 'confirmed' THEN 1.0
    WHEN c.action = 'timeout' THEN 0.0
END

Yes.

If no WHEN condition matches and there is no ELSE clause, the CASE expression returns NULL.