Write SQL to detect recurring non-subscription users
Company: Stripe
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Take-home Project
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.
Quick Answer: 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.