Days From Signup to First Captured Payment

Medium
406.

Days From Signup to First Captured Payment

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

ColumnType
account_idint
company_namevarchar
signed_up_ondate

Primary key: account_id.

One row per customer account.

Table: payments

ColumnTypeValues
payment_idint
account_idint
paid_ondate
amountint
statusenumcaptured, failed, refunded

Primary key: payment_id.

Every charge attempt made through the payment gateway, in rupees.

Example

The rows must come back in the order shown.

accounts

account_idcompany_namesigned_up_on
1Zentrix Labs2024-03-01
2Quillpad2024-03-05
3Ledgerly2024-03-10
4Orbitpay2024-03-12
5Vaani AI2024-03-15

payments

payment_idaccount_idpaid_onamountstatus
112024-03-154999failed
212024-03-164999captured
312024-04-164999captured
422024-03-0524999captured
532024-03-204999refunded
642024-04-014999failed
752024-03-2939999captured
832024-04-024999captured

Output

account_idcompany_namefirst_paid_ondays_to_first_payment
2Quillpad2024-03-050
5Vaani AI2024-03-2914
1Zentrix Labs2024-03-1615
3Ledgerly2024-04-0223
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
account_idint
company_namevarchar
signed_up_ondate

Primary key: account_id.

One row per customer account.

Table: payments

ColumnType
payment_idint
account_idint
paid_ondate
amountint
statusenum(captured, failed, refunded)

Primary key: payment_id.

Every charge attempt made through the payment gateway, in rupees.

Examples

Example 1

accounts

account_idcompany_namesigned_up_on
1Zentrix Labs2024-03-01
2Quillpad2024-03-05
3Ledgerly2024-03-10
4Orbitpay2024-03-12
5Vaani AI2024-03-15

payments

payment_idaccount_idpaid_onamountstatus
112024-03-154999failed
212024-03-164999captured
312024-04-164999captured
422024-03-0524999captured
532024-03-204999refunded
642024-04-014999failed
752024-03-2939999captured
832024-04-024999captured

Output

account_idcompany_namefirst_paid_ondays_to_first_payment
2Quillpad2024-03-050
5Vaani AI2024-03-2914
1Zentrix Labs2024-03-1615
3Ledgerly2024-04-0223

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_id

Another 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_id

Another 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 →