Coding questions · Stripe
Write SQL to detect recurring non-subscription users
This question evaluates a candidate's ability to perform advanced SQL data manipulation and temporal pattern detection in transactional datasets, including attribute-based matching (amount and card fingerprint) and filtering by transaction status.
You have two tables: merchant and transaction. Assume 'today' is 2025-09-01. Schema:
merchant(merchant_id INT PK, merchant_name TEXT, country TEXT, created_at DATE, vertical TEXT)
transaction(txn_id INT PK, merchant_id INT FK, customer_id INT, amount_cents INT, currency TEXT, product_type ENUM('Subscription','Checkout','PaymentLink'), created_at TIMESTAMP, status ENUM('succeeded','refunded','failed'), card_fingerprint TEXT)
Sample data (small, illustrative):
merchant
+-------------+---------------+---------+------------+----------+
| merchant_id | merchant_name | country | created_at | vertical |
+-------------+---------------+---------+------------+----------+
| 1 | Alpha Co | US | 2025-01-10 | SaaS |
| 2 | Beta Shop | US | 2025-03-05 | Retail |
| 3 | Gamma Apps | CA | 2025-02-20 | SaaS |
| 4 | Delta Goods | US | 2025-06-01 | Retail |
+-------------+---------------+---------+------------+----------+
transaction
+--------+------------+-------------+--------------+----------+---------------+---------------------+-----------+------------------+
| txn_id | merchant_id| customer_id | amount_cents | currency | product_type | created_at | status | card_fingerprint |
+--------+------------+-------------+--------------+----------+---------------+---------------------+-----------+------------------+
| 102 | 1 | 1001 | 9900 | USD | Subscription | 2025-07-15 10:00:00 | succeeded | fp_a |
| 138 | 1 | 1001 | 9900 | USD | Subscription | 2025-08-15 10:00:00 | succeeded | fp_a |
| 101 | 2 | 2001 | 1999 | USD | Checkout | 2025-04-30 09:00:00 | succeeded | fp_b |
| 135 | 2 | 2001 | 1999 | USD | Checkout | 2025-05-30 09:00:00 | succeeded | fp_b |
| 170 | 2 | 2001 | 1999 | USD | Checkout | 2025-06-29 09:00:00 | succeeded | fp_b |
| 205 | 2 | 2002 | 999 | USD | Checkout | 2025-05-01 08:00:00 | succeeded | fp_c |
| 240 | 2 | 2002 | 999 | USD | Checkout | 2025-05-30 08:00:00 | succeeded | fp_c |
| 275 | 2 | 2003 | 499 | USD | Checkout | 2025-07-01 12:00:00 | succeeded | fp_d |
| 310 | 2 | 2003 | 499 | USD | Checkout | 2025-07-30 12:00:00 | succeeded | fp_d |
| 411 | 3 | 3001 | 2500 | USD | PaymentLink | 2025-07-10 11:00:00 | succeeded | fp_e |
| 512 | 4 | 4001 | 7000 | USD | Checkout | 2025-08-05 15:00:00 | refunded | fp_f |
+--------+------------+-------------+--------------+----------+---------------+---------------------+-----------+------------------+
Task: Write a single SQL query that returns the top 10 merchants who do NOT currently use product_type='Subscription' (no succeeded Subscription transactions in the last 180 days before 2025-09-01) but exhibit recurring behavior indicative of subscriptions. Define a "recurring customer" for a merchant as a customer_id with at least two succeeded payments in the last 180 days with the same amount_cents and same card_fingerprint where the inter-payment gap is between 28 and 35 days (inclusive). Exclude refunded/failed transactions and ignore currency mismatches. Output columns: merchant_id, recurring_customer_count_last_180d, repeat_txn_rate_30d (percentage of succeeded transactions in the last 30 days that are part of a 28–35 day repeat pair), first_seen_date (MIN(created_at::date) for that merchant), and currently_uses_subscription (0/1). Filter to currently_uses_subscription=0 and order by recurring_customer_count_last_180d desc, then repeat_txn_rate_30d desc. Be careful about multiple qualifying gaps per customer—count each customer at most once. Use window functions where appropriate.
Detect recurring non-subscription users
You are given two tables, merchant and transaction, that store merchant metadata and payment transactions. Assume today's date is 2025-06-01. For this question, define the 180-day analysis window as the period from 2024-12-04 through 2025-06-01 (inclusive), and the "last 30 days" as the period from 2025-05-03 through 2025-06-01 (inclusive). A transaction is considered only if status = 'succeeded'. Refunded or failed transactions must be ignored. Currency differences should be ignored when deciding if payments are "the same". Define a "recurring customer" for a merchant as a customer_id that, within the 180-day window (2024-12-04 to 2025-06-01), has at least two succeeded transactions with the same amount_cents and the same card_fingerprint, where the gap in days between consecutive such payments is between 28 and 35 days (inclusive). A customer who has multiple qualifying 28–35 day gaps should still be counted at most once per merchant. A merchant is considered to "currently use subscriptions" if it has at least one succeeded transaction with product_type = 'Subscription' in the 180-day window. For each merchant, compute: (1) recurring_customer_count_last_180d: the number of distinct recurring customers for that merchant in the 180-day window; (2) repeat_txn_rate_30d: the percentage of succeeded transactions in the last 30 days (2025-05-03 to 2025-06-01) that are part of at least one 28–35 day repeat pair (as defined above); (3) first_seen_date: the earliest transaction date (MIN(DATE(created_at))) for that merchant across all transactions; and (4) currently_uses_subscription: a flag (0 or 1) indicating whether the merchant has any succeeded Subscription transactions in the 180-day window. Write a single SQL query that returns the top 10 merchants who do not currently use subscriptions (currently_uses_subscription = 0) but do exhibit recurring behavior (recurring_customer_count_last_180d > 0). Output columns should be: merchant_id, recurring_customer_count_last_180d, repeat_txn_rate_30d, first_seen_date, currently_uses_subscription. Order the results by recurring_customer_count_last_180d in descending order, then by repeat_txn_rate_30d in descending order, and then by merchant_id. Use window functions where appropriate to detect the inter-payment gaps.
Table merchant
| column | type | constraints |
|---|---|---|
| merchant_id | INT | PRIMARY KEY, NOT NULL |
| merchant_name | VARCHAR(100) | NOT NULL |
| country | VARCHAR(10) | NOT NULL |
| created_at | DATE | NOT NULL |
| vertical | VARCHAR(50) | NOT NULL |
Sample data:
| country | created_at | merchant_id | merchant_name | vertical |
|---|---|---|---|---|
| US | 2025-01-10 | 1 | Alpha Co | SaaS |
| US | 2025-03-05 | 2 | Beta Shop | Retail |
| CA | 2025-02-20 | 3 | Gamma Apps | SaaS |
| US | 2025-06-01 | 4 | Delta Goods | Retail |
Table transaction
| column | type | constraints |
|---|---|---|
| txn_id | INT | PRIMARY KEY, NOT NULL |
| merchant_id | INT | NOT NULL |
| customer_id | INT | NOT NULL |
| amount_cents | INT | NOT NULL |
| currency | VARCHAR(10) | NOT NULL |
| product_type | VARCHAR(20) | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| card_fingerprint | VARCHAR(50) | NOT NULL |
Sample data:
| amount_cents | card_fingerprint | created_at | currency | customer_id | merchant_id | product_type | status | txn_id |
|---|---|---|---|---|---|---|---|---|
| 9900 | fp_a | 2025-01-15 10:00:00 | USD | 1001 | 1 | Subscription | succeeded | 1001 |
| 9900 | fp_a | 2025-02-15 10:00:00 | USD | 1001 | 1 | Subscription | succeeded | 1002 |
| 2500 | fp_z | 2025-05-10 09:00:00 | USD | 1002 | 1 | Checkout | succeeded | 1003 |
| 1999 | fp_b | 2025-03-15 09:00:00 | USD | 2001 | 2 | Checkout | succeeded | 2001 |
| 1999 | fp_b | 2025-04-15 09:00:00 | USD | 2001 | 2 | Checkout | succeeded | 2002 |
| 1999 | fp_b | 2025-05-15 09:00:00 | USD | 2001 | 2 | Checkout | succeeded | 2003 |
| 999 | fp_c | 2025-05-20 08:00:00 | USD | 2002 | 2 | Checkout | succeeded | 2004 |
| 999 | fp_c | 2025-05-25 08:00:00 | USD | 2002 | 2 | Checkout | refunded | 2005 |
| 499 | fp_d | 2025-04-01 12:00:00 | USD | 2003 | 2 | Checkout | succeeded | 2006 |
| 499 | fp_d | 2025-04-29 12:00:00 | USD | 2003 | 2 | Checkout | succeeded | 2007 |
| 2500 | fp_e | 2025-01-10 11:00:00 | USD | 3001 | 3 | PaymentLink | succeeded | 3001 |
| 2500 | fp_e | 2025-02-10 11:00:00 | USD | 3001 | 3 | PaymentLink | succeeded | 3002 |
| 2500 | fp_e | 2025-03-15 11:00:00 | USD | 3001 | 3 | PaymentLink | succeeded | 3003 |
| 1500 | fp_f | 2025-05-05 11:00:00 | USD | 3002 | 3 | PaymentLink | succeeded | 3004 |
| 1500 | fp_f | 2025-05-20 11:00:00 | USD | 3002 | 3 | PaymentLink | succeeded | 3005 |
| 7000 | fp_g | 2025-05-10 15:00:00 | USD | 4001 | 4 | Checkout | refunded | 4001 |
| 7000 | fp_g | 2025-05-12 15:00:00 | USD | 4001 | 4 | Checkout | failed | 4002 |
Expected output
Each row represents a merchant that does not currently use subscriptions (no succeeded Subscription transactions between 2024-12-04 and 2025-06-01) but has at least one recurring customer in that 180-day window. recurring_customer_count_last_180d is the count of distinct customers with at least one qualifying 28–35 day gap for same amount_cents and card_fingerprint. repeat_txn_rate_30d is the percentage of the merchant's succeeded transactions in the last 30 days (2025-05-03 to 2025-06-01) that are part of such a recurring pair. first_seen_date is the earliest transaction date for that merchant. currently_uses_subscription is 0 for all returned merchants. Based on the sample data, only merchants 2 and 3 qualify.
| currently_uses_subscription | first_seen_date | merchant_id | recurring_customer_count_last_180d | repeat_txn_rate_30d |
|---|---|---|---|---|
| 0 | 2025-03-15 | 2 | 2 | 50 |
| 0 | 2025-01-10 | 3 | 1 | 0 |
Hints
- Use LAG() over (merchant_id, customer_id, amount_cents, card_fingerprint) to compute day gaps between consecutive succeeded payments and flag the 28–35 day repeats.
- Compute the recurring flags in a CTE, then aggregate once for the 180-day recurring_customer_count and again for the last-30-day transaction and repeat counts before filtering out merchants with Subscription usage.