Days From Signup to First Captured Payment — SQL Problem
Finance measures how long a new account takes to start paying.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #406
Problem statement
Finance measures how long a new account takes to start paying. Only a payment with status captured counts — a failed charge never reached the bank and a refunded one was given back.
For every account with at least one captured payment, return account_id, company_name, first_paid_on (the date of its earliest captured payment) and days_to_first_payment (whole days from signed_up_on to first_paid_on). Accounts that never paid are left out. Order by days_to_first_payment, then account_id.
Tables
Table: accounts
| Column | Type |
|---|---|
| account_id | int |
| company_name | varchar |
| signed_up_on | date |
Primary key: account_id.
One row per customer account.
Table: payments
| Column | Type |
|---|---|
| payment_id | int |
| account_id | int |
| paid_on | date |
| amount | int |
| status | enum(captured, failed, refunded) |
Primary key: payment_id.
Every charge attempt made through the payment gateway, in rupees.
Examples
Example 1
accounts
| account_id | company_name | signed_up_on |
|---|---|---|
| 1 | Zentrix Labs | 2024-03-01 |
| 2 | Quillpad | 2024-03-05 |
| 3 | Ledgerly | 2024-03-10 |
| 4 | Orbitpay | 2024-03-12 |
| 5 | Vaani AI | 2024-03-15 |
payments
| payment_id | account_id | paid_on | amount | status |
|---|---|---|---|---|
| 1 | 1 | 2024-03-15 | 4999 | failed |
| 2 | 1 | 2024-03-16 | 4999 | captured |
| 3 | 1 | 2024-04-16 | 4999 | captured |
| 4 | 2 | 2024-03-05 | 24999 | captured |
| 5 | 3 | 2024-03-20 | 4999 | refunded |
| 6 | 4 | 2024-04-01 | 4999 | failed |
| 7 | 5 | 2024-03-29 | 39999 | captured |
| 8 | 3 | 2024-04-02 | 4999 | captured |
Output
| account_id | company_name | first_paid_on | days_to_first_payment |
|---|---|---|---|
| 2 | Quillpad | 2024-03-05 | 0 |
| 5 | Vaani AI | 2024-03-29 | 14 |
| 1 | Zentrix Labs | 2024-03-16 | 15 |
| 3 | Ledgerly | 2024-04-02 | 23 |
How to solve Days From Signup to First Captured Payment
Join each account to its captured payments, group by account, and take MIN(paid_on) — the first time money actually arrived. DATEDIFF(MIN(p.paid_on), a.signed_up_on) turns that into days; an account that paid on its signup day scores 0.
Where the status filter sits matters. In the join condition (or WHERE — with an inner join they are the same), it removes failed and refunded attempts before the minimum is taken, so Ledgerly's refunded charge on 20 March does not count and its first payment is 2 April. Without it, a failed card on day 14 would look like a conversion.
An inner join is what leaves out accounts that never paid: Orbitpay has only a failed charge, so no captured row joins and it has no group. signed_up_on is listed in GROUP BY because the select list uses it outside an aggregate — it is functionally determined by the key, but MySQL's ONLY_FULL_GROUP_BY wants it named.
A ROW_NUMBER() per account picks the first captured payment row (useful if you also want its amount), and a correlated MIN subquery per account does the same lookup row by row. With an index on (account_id, status, paid_on) each is a short range scan per account.
Reference solution (MySQL)
SELECT a.account_id, a.company_name,
MIN(p.paid_on) AS first_paid_on,
DATEDIFF(MIN(p.paid_on), a.signed_up_on) AS days_to_first_payment
FROM accounts a
JOIN payments p ON p.account_id = a.account_id AND p.status = 'captured'
GROUP BY a.account_id, a.company_name, a.signed_up_on
ORDER BY days_to_first_payment, a.account_idAnother way
WITH firsts AS (
SELECT account_id, paid_on, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY paid_on, payment_id) AS rn
FROM payments WHERE status = 'captured'
)
SELECT a.account_id, a.company_name, f.paid_on AS first_paid_on, DATEDIFF(f.paid_on, a.signed_up_on) AS days_to_first_payment
FROM accounts a JOIN firsts f ON f.account_id = a.account_id AND f.rn = 1
ORDER BY days_to_first_payment, a.account_idAnother way
SELECT account_id, company_name, first_paid_on, DATEDIFF(first_paid_on, signed_up_on) AS days_to_first_payment
FROM (SELECT a.*, (SELECT MIN(paid_on) FROM payments p WHERE p.account_id = a.account_id AND p.status = 'captured') AS first_paid_on FROM accounts a) x
WHERE first_paid_on IS NOT NULL
ORDER BY days_to_first_payment, account_id← Feature Adoption, Including Features Nobody Used · Pricing Page A/B Test Conversion by Variant →