Business Problem

Flipkart's lifecycle marketing team is prepping the next win-back campaign and needs a clean view of who is actually still shopping. Using each customer's order history, tag every customer as 'Active' or 'Inactive' based on days since their last purchase — customers silent for more than 90 days should be flagged as churn risks for reactivation offers, while active customers get routed to a separate upsell track. Marketing wants a single query returning customer details alongside their last purchase date, total order count, and days since that last order, so the campaign tool can filter and segment in one pass.

Dataset & Schema

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)
);

SQL Solution

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;

Explanation