SQL Programming Resources on Telegram: “🚀 SQL Project Series #30: E-Commerce Customer R…”

🚀 SQL Project Series #30: E-Commerce Customer Retention & Cohort Analysis 📊

Perfect project for Data, Product, and CRM Analysts. This one hits both SQL fundamentals + advanced customer analytics.

🎯 Business Objectives

✅ Analyze customer retention

✅ Build monthly customer cohorts

✅ Measure repeat purchases

✅ Calculate customer lifetime value

✅ Identify high-value customers

✅ Analyze customer churn

✅ Compare cohort performance

✅ Measure revenue retention

📂 Step 1: Create Database

CREATE DATABASE cohort_analysis_db;
USE cohort_analysis_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    signup_date DATE,
    city VARCHAR(50)
);

📂 Step 3: Create Orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10,2),
    order_status VARCHAR(20),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

📂 Step 4: Insert Sample Customers

INSERT INTO customers VALUES
(1,'Rahul Sharma','2025-01-05','Mumbai'),
(2,'Priya Verma','2025-01-10','Delhi'),
(3,'Amit Patel','2025-02-15','Pune'),
(4,'Sneha Joshi','2025-02-20','Bangalore'),
(5,'Rohan Gupta','2025-03-05','Hyderabad'),
(6,'Anjali Shah','2025-03-15','Mumbai');

📂 Step 5: Insert Sample Orders

INSERT INTO orders VALUES
(1001,1,'2025-01-10',2500,'Completed'),
(1002,1,'2025-02-15',3200,'Completed'),
(1003,1,'2025-03-20',1800,'Completed'),
(1004,2,'2025-01-20',1500,'Completed'),
(1005,2,'2025-03-05',2800,'Completed'),
(1006,3,'2025-02-20',4200,'Completed'),
(1007,3,'2025-03-25',3500,'Completed'),
(1008,4,'2025-02-25',1800,'Completed'),
(1009,5,'2025-03-10',2200,'Completed'),
(1010,5,'2025-04-15',3100,'Completed'),
(1011,6,'2025-03-20',2700,'Completed');

🧠 SQL Concepts You'll Practice

✔ Date Functions

✔ GROUP BY, Aggregate Functions, JOINs

✔ CTEs, CASE WHEN, Subqueries

✔ Window Functions, LAG(), DENSE_RANK()

✔ COUNT DISTINCT, Cohort Analysis

📊 Business KPIs You Can Build

📈 Monthly Active Customers

📈 New Customers by Month

📈 Repeat Customers, Repeat Purchase Rate

📈 Customer Retention Rate, Churn Rate

📈 Customer Lifetime Value (CLV), AOV

📈 Revenue per Cohort, Cohort Retention Rate

📈 RFM Segmentation, High-Value Customer %

💡 Example Queries

1. Find Customer Cohort

WITH customer_cohort AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(signup_date)) AS cohort_month
    FROM customers
    GROUP BY customer_id
)
SELECT * FROM customer_cohort ORDER BY cohort_month;

2. Monthly Customer Activity

SELECT
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(DISTINCT customer_id) AS active_customers
FROM orders
WHERE order_status = 'Completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

3. Repeat Customers

SELECT
    customer_id,
    COUNT(order_id) AS total_orders
FROM orders
WHERE order_status = 'Completed'
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

4. Customer Lifetime Value

SELECT
    customer_id,
    SUM(order_amount) AS lifetime_value
FROM orders
WHERE order_status = 'Completed'
GROUP BY customer_id
ORDER BY lifetime_value DESC;

5. Rank Customers by Revenue

WITH customer_revenue AS (
    SELECT customer_id, SUM(order_amount) AS revenue
    FROM orders WHERE order_status = 'Completed' GROUP BY customer_id
)
SELECT
    customer_id,
    revenue,
    DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_revenue;