OTP Verification Rate of Every Account — SQL Medium Problem

Problem statement

A payments app sends a one-time password whenever someone signs in from a new device. Each request ends either verified (the code was typed in time) or expired.

For every account in Account, return account_id and verify_rate: the number of its verified requests divided by the number of all its requests, rounded to 2 decimal places. An account that has never requested an OTP has a verify_rate of 0. Return the rows in any order.

Tables

Table: Account

Primary key: account_id.

One row per account.

Table: OtpRequest

Primary key: account_id, requested_at.

One row per OTP sent. Every account_id here is in Account.

Examples

Example 1

Account

OtpRequest

Output

How to solve OTP Verification Rate of Every Account

Every account must appear, including those that never requested a code, so the query starts from Account and LEFT JOINs the requests. Grouping by account then gives one group per account: its requests, or a single row of NULLs if there were none.

The rate is the share of requests that were verified, and a share is the average of a flag: IF(result = 'verified', 1, 0) is 1 for a verified request and 0 otherwise, so its average is verified ÷ total. The flag also settles the empty case neatly — for an account with no requests the one padded row has result NULL, the IF yields 0, and the average is 0, exactly what the statement asks for.

Counting explicitly works too: the verified count divided by COUNT(o.account_id), the number of real requests. For an account with none that is 0 ÷ 0, which SQL answers with NULL rather than an error, so the expression needs IFNULL(…, 0). An aggregate that skips NULLs, such as AVG(o.result = 'verified'), has the same gap: over a group holding only the padded row it averages nothing and returns NULL. A correlated subquery per account computes the same average and needs the same IFNULL.

Round only the final ratio. Both forms read each request once, so the cost is one pass over OtpRequest plus the grouping.

Reference solution (MySQL)

SELECT a.account_id,
       ROUND(AVG(IF(o.result = 'verified', 1, 0)), 2) AS verify_rate
FROM Account a
LEFT JOIN OtpRequest o ON o.account_id = a.account_id
GROUP BY a.account_id

Another way

SELECT a.account_id, IFNULL(ROUND(SUM(CASE WHEN o.result = 'verified' THEN 1 ELSE 0 END) / COUNT(o.account_id), 2), 0) AS verify_rate FROM Account a LEFT JOIN OtpRequest o ON o.account_id = a.account_id GROUP BY a.account_id

Another way

SELECT a.account_id, ROUND(IFNULL((SELECT AVG(o.result = 'verified') FROM OtpRequest o WHERE o.account_id = a.account_id), 0), 2) AS verify_rate FROM Account a