Write SQL to detect recurring non-subscription users
Company: Stripe
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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.
Overview: 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 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.
Tables
merchant(merchant_id INT, merchant_name VARCHAR(100), country VARCHAR(10), created_at DATE, vertical VARCHAR(50))
transaction(txn_id INT, merchant_id INT, customer_id INT, amount_cents INT, currency VARCHAR(10), product_type VARCHAR(20), created_at TIMESTAMP, status VARCHAR(20), card_fingerprint VARCHAR(50))
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.