Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Stripe
Segment customers into RFM buckets (High-Value, At-Risk, Loyal, etc.) based on purchase recency, transaction frequency, and total spending.
Stripe's lifecycle team needs every customer filed into an actionable segment — not by gut feel, but by the classic RFM model: Recency (days since last purchase — are they still warm?), Frequency (transaction count — habit or one-off?), Monetary (total spent — how much do they matter?).
Each axis scores 1–3 (recency ≤30 days scores 3; frequency ≥4 scores 3; monetary ≥₹10K scores 3), summed into a combined score out of 9 and mapped to segments: Champions (3/3/3 — the perfect customer), Loyal-Customers, Potential-Loyalists, At-Risk (gone quiet but bought repeatedly), Lost, and Need-Engagement.
The segments are call lists, not labels: Champions get early access, At-Risk gets win-back, Potential-Loyalists get the nudge toward a second order. And press on the model's conveniences — equal weights are arbitrary (should a rupee equal a day?), and with no 9-pointer in the data, the nearest-Champion analysis (who is one order away?) is where the money is.
Customer data with customer_id, signup_date; transactions with customer_id, transaction_date, transaction_amount; need to calculate recency (days since last purchase), frequency (transaction count), monetary (total spent).
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name TEXT,
signup_date DATE
);
CREATE TABLE transactions (
transaction_id INTEGER PRIMARY KEY,
customer_id INTEGER,
transaction_date DATE,
transaction_amount DECIMAL(10,2),
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);Your target
Segment customers into RFM buckets (High-Value, At-Risk, Loyal, etc.) based on purchase recency, transaction frequency, and total spending.
Loading the expected output…
WITH customer_metrics AS (
SELECT
c.customer_id,
c.customer_name,
MAX(t.transaction_date) as last_transaction_date,
ROUND((julianday('2024-03-31') - julianday(MAX(t.transaction_date))) / 1.0, 0) as recency_days,
COUNT(DISTINCT t.transaction_id) as frequency_count,
ROUND(SUM(t.transaction_amount), 2) as monetary_value
FROM customers c
LEFT JOIN transactions t ON c.customer_id = t.customer_id
GROUP BY c.customer_id, c.customer_name
),
rfm_scores AS (
SELECT
customer_id,
customer_name,
last_transaction_date,
recency_days,
frequency_count,
monetary_value,
CASE
WHEN recency_days <= 30 THEN 3
WHEN recency_days <= 90 THEN 2
ELSE 1
END as recency_score,
CASE
WHEN frequency_count >= 4 THEN 3
WHEN frequency_count >= 2 THEN 2
ELSE 1
END as frequency_score,
CASE
WHEN monetary_value >= 10000 THEN 3
WHEN monetary_value >= 5000 THEN 2
ELSE 1
END as monetary_score
FROM customer_metrics
)
SELECT
customer_id,
customer_name,
recency_days,
frequency_count,
monetary_value,
recency_score,
frequency_score,
monetary_score,
(recency_score + frequency_score + monetary_score) as combined_rfm_score,
CASE
WHEN recency_score = 3 AND frequency_score = 3 AND monetary_score = 3 THEN 'Champions'
WHEN recency_score >= 2 AND frequency_score >= 2 AND monetary_score >= 2 THEN 'Loyal-Customers'
WHEN recency_score >= 2 AND frequency_score < 2 THEN 'Potential-Loyalists'
WHEN recency_score = 1 AND frequency_score >= 2 THEN 'At-Risk'
WHEN recency_score = 1 AND frequency_score = 1 THEN 'Lost'
ELSE 'Need-Engagement'
END as customer_segment
FROM rfm_scores
ORDER BY combined_rfm_score DESC, monetary_value DESC;Step-by-step Solution:
Create customer_metrics CTE by joining customers with transactions. For each customer, calculate: last_transaction_date (MAX), recency_days (days since last transaction using julianday), frequency_count (COUNT of transactions), monetary_value (SUM of amounts). Use GROUP BY to aggregate per customer.
In rfm_scores CTE, assign numerical scores: Recency (3=within 30 days, 2=30-90 days, 1=90+ days), Frequency (3=4+ transactions, 2=2-3, 1=1), Monetary (3=₹10K+, 2=₹5-10K, 1=<₹5K).
Calculate combined_rfm_score as sum of all three scores (range 3-9).
Segment customers using CASE: Champions (all 3), Loyal (all >=2), Potential (recent but infrequent), At-Risk (inactive but frequent), Lost (inactive and infrequent), Need-Engagement (other).
Order by combined score descending for easy prioritization. This enables targeted strategies: retain Champions, convert Potential to Loyal, win-back At-Risk.
Computing the reference output…
No Champions exist yet — the three Loyal-Customers (Acme Corp, TechStart, Digital Labs, all scoring 8) are one big recent order away from it. Act now on Global Solutions (112 days dormant, At-Risk), grow DataHub's tiny Rs.3,100 basket despite frequent buying, and convert CloudFirst's recent single purchase into a second order.
What the interviewer asks next
“Nobody scores Champion (3/3/3) — which customer is one order away, and what exactly is that order?”
Strong answer covers: Nearest-segment analysis: Acme (8, needs frequency), TechStart (8, needs recency depth) — name the concrete next purchase that promotes each, turning segments into call lists.
“Global Solutions went 112 days silent after 2 buys — what offer wins them back, and what proves it worked?”
Strong answer covers: Win-back design: time-bound incentive tied to their past basket size, with reactivation rate and second-order rate as the success metrics, not open rates.
“Recency, Frequency, Monetary weigh equally here — should a rupee count the same as a day?”
Strong answer covers: Weighting critique: equal weights are a convenience, not a truth — argue for overweighting recency (churn predicts) or monetary (revenue matters) from the business goal.
“DataHub buys often (3 txns) and recently (26 days) but tiny (Rs.3,100 total) — Need-Engagement, or a mis-scored minnow that needs a different play?”
Strong answer covers: Segment-model limits: frequency says nurture, money says ignore — the resolution is basket-building (bundles, thresholds) rather than engagement emails, which the segment label alone would misprescribe.
“TechStart's recency is 32 days — two days past the 30-day R=3 cutoff — with the highest frequency (4 txns). One line of threshold, one lost point. Fair?”
Strong answer covers: Cliff effects in scoring: a 2-day difference flips R from 3 to 2 and the combined score from 9 to 8 — argue for continuous recency or grace bands where cliffs decide budgets.
“Acme (Rs.12.7K) and Digital Labs (Rs.11.5K) tie at 8 with one Champions nudge left in the budget — who gets it?”
Strong answer covers: Tie-breaking under scarcity: Acme's edge is recency depth (11 days, 3 buys) vs Digital's velocity (2 buys in 14 days) — pick using trajectory (who is accelerating?) and defend the criterion.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.