Write SQL to detect recurring non-subscription users

Q: Write SQL to detect recurring non-subscription users

This is a Data Manipulation (SQL/Python) interview question from Stripe for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Write SQL to detect recurring non-subscription users

Comments (0)