Stripe·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Stripe DS interview, technical screen format, one big SQL problem that looked manageable until it really wasn't. The kind of question where you think you understand it and then re-read it three times and realize you missed half the requirements.

Questions Asked (1)

Given a merchant table and a transaction table, write a single SQL query to find the top 10 merchants who have no succeeded Subscription transactions in the last 180 days but show recurring payment behavior. Define a recurring customer as someone with at least two succeeded payments to the same merchant in that window, same amount and card fingerprint, with gaps between 28 and 35 days. Output includes merchant ID, recurring customer count, a repeat transaction rate for the last 30 days, first seen date, and a flag for whether they currently use subscriptions. Count each customer at most once, use window functions where appropriate.

Data ModelingProduct Analytics & MetricsRoot Cause Analysis

Author's notes

This wrecked me a little.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into two parts: first, identify merchants with no succeeded subscription transactions in the last 180 days; second, among those, find merchants with recurring customers (at least two succeeded payments with same amount and card fingerprint, gaps 28-35 days) and compute the required metrics. Use CTEs and window functions to handle the logic cleanly and ensure each customer is counted once per merchant.

Pro tip: Clarify the definition of 'recurring payment behavior' and 'repeat transaction rate' upfront, and state your assumptions; this shows you think about ambiguity and business context, which is crucial for a data scientist at Stripe.

1. Filter merchants without subscriptions

Identify merchants that have no succeeded Subscription transactions in the last 180 days by left joining or using NOT EXISTS on the transaction table filtered by transaction type and status.

2. Identify recurring customers

For the remaining merchants, find customers with at least two succeeded payments in the last 180 days where the amount and card fingerprint match, and the gap between consecutive payments is between 28 and 35 days, using window functions like LAG to compare transactions.

3. Count recurring customers per merchant

Aggregate the recurring customers per merchant, ensuring each customer is counted only once per merchant, using DISTINCT or ROW_NUMBER to deduplicate.

4. Compute repeat transaction rate for last 30 days

Calculate the repeat transaction rate for the last 30 days as the number of customers with more than one transaction divided by total customers, or as specified, using conditional aggregation.

5. Assemble final output with flags

Combine the results to output merchant ID, recurring customer count, repeat transaction rate, first seen date (earliest transaction date for the merchant), and a flag indicating if the merchant currently uses subscriptions (based on the absence of subscription transactions in the last 180 days).

Key Points to Mention

Use of window functions (e.g., LAG, ROW_NUMBER) to identify recurring payment patterns and deduplicate customers.
Handling of date ranges: last 180 days for subscription exclusion and recurring definition, last 30 days for repeat rate.
Definition of 'recurring customer': at least two succeeded payments with same amount and card fingerprint, gaps 28-35 days.
Ensuring each customer is counted at most once per merchant, possibly using DISTINCT or ROW_NUMBER.
Calculation of repeat transaction rate: clarify numerator and denominator (e.g., customers with >1 transaction / total customers in last 30 days).
Use of CTEs for readability and modularity, and consideration of performance with large datasets.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.