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 · Flipkart
Categorize customers as Active or Inactive based on purchase recency to identify engagement levels.
Flipkart's lifecycle marketing team is prepping the next win-back campaign and needs a clean list of who is actually still shopping. Using each customer's order history, every customer gets tagged by days since last purchase (measured against the reference date 2024-03-31): Active (within 30 days — the upsell track), At-Risk (31–90 days — needs engagement nudges before lapsing), or Inactive (over 90 days — the win-back cohort).
The output carries everything the campaign tool needs in one pass: customer details, last purchase date, total order count, and days-since-purchase, most-active first. A LEFT JOIN keeps customers visible even with sparse histories — an INNER JOIN would silently drop exactly the people a win-back campaign exists for.
Three edge cases complete the answer: customers with zero orders (NULL dates — give them an explicit bucket, don't let comparisons misclassify them), threshold changes (if Active becomes 60 days, one constant reshuffles every list), and tenure × recency (a September signup silent since December is an onboarding failure, not ordinary dormancy).
Customer data with customer_id, customer_name, signup_date; orders with order_id, customer_id, order_date.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name TEXT,
signup_date DATE
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);Your target
Categorize customers as Active or Inactive based on purchase recency to identify engagement levels.
Loading the expected output…
SELECT
c.customer_id,
c.customer_name,
c.signup_date,
MAX(o.order_date) as last_purchase_date,
COUNT(o.order_id) as total_purchases,
ROUND((julianday('2024-03-31') - julianday(MAX(o.order_date))) / 1.0, 0) as days_since_purchase,
CASE
WHEN ROUND((julianday('2024-03-31') - julianday(MAX(o.order_date))) / 1.0, 0) <= 30 THEN 'Active'
WHEN ROUND((julianday('2024-03-31') - julianday(MAX(o.order_date))) / 1.0, 0) <= 90 THEN 'At-Risk'
ELSE 'Inactive'
END as customer_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.signup_date
ORDER BY days_since_purchase ASC;Step-by-step Solution:
SELECT customer details: customer_id, customer_name, signup_date.
Use MAX(o.order_date) to find the most recent purchase date for each customer.
COUNT(o.order_id) counts total purchases per customer.
Calculate days_since_purchase using julianday: julianday('2024-03-31') - julianday(MAX(o.order_date)). This calculates the number of days between today and the last purchase.
Use CASE to classify customers: Active (<=30 days since purchase = bought recently), At-Risk (31-90 days = hasn't bought in a while), Inactive (>90 days = dormant).
LEFT JOIN ensures all customers appear even without recent orders. GROUP BY customer_id to aggregate per customer.
ORDER BY days_since_purchase ASC to show most active customers first. This enables targeted campaigns: win-back offers for Inactive, engagement email for At-Risk, loyalty rewards for Active.
Computing the reference output…
As of 31 Mar 2024 the base splits evenly: 2 Active customers (Alice Johnson, Frank Miller) go to the upsell track, 2 At-Risk customers (Bob Smith, Charlie Brown) get engagement nudges before they lapse, and 2 Inactive customers (Diana Prince, Eve Wilson — both silent 100+ days) form the win-back cohort for reactivation offers.
What the interviewer asks next
“A customer with zero orders — Active, Inactive, or something else? What does your query do with them?”
Strong answer covers: NULL-join trap: LEFT JOIN keeps them with NULL dates, and NULL comparisons silently misclassify — the fix is an explicit 'Never ordered' bucket, volunteered before the interviewer asks.
“Marketing redefines Active as 60 days instead of 30 — what breaks downstream?”
Strong answer covers: Blast-radius thinking: one constant reshuffles every segment, the win-back list, and the upsell list — parameterize thresholds and show which customers move buckets.
“Diana signed in September but last bought in December — new-customer churn or ordinary dormancy?”
Strong answer covers: Tenure × recency: early-life silence means onboarding/activation failure, late-life silence means fading loyalty — same bucket, different plays, so argue for splitting them.
“Alice Johnson has 2 orders; every other customer has exactly 1 — yet the model sees only recency. Does Alice deserve the same Active label as Frank?”
Strong answer covers: Recency-only blindness: equal labels hide unequal value — argue for a frequency-aware tier (or RFM upgrade) so the upsell track prioritizes proven repeat buyers over one-time recents.
“Bob is 32 days out, Charlie 76 — same At-Risk bucket, wildly different urgency. Bucket or continuous score?”
Strong answer covers: Bucket coarseness: 44 days of decay compressed into one label wastes the best signal in the table — propose days-since-purchase as a continuous priority score with buckets as mere presentation.
“Eve signed in October, bought once in November, silent 142 days — is she win-back material or was she never really a customer?”
Strong answer covers: Activation vs retention: single early purchases that never repeat are failed activations, not lapses — exclude never-activated users from win-back ROI math or the campaign's denominator lies.
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.