SQL Project: E-commerce Sales Analysis

-- sqatools.in SQL project 1: e-commerce sales analysis (generated by gen_projects.py)CREATE TABLE customers (  customer_id INT PRIMARY KEY,  full_name   VARCHAR(50) NOT NULL,  city        VARCHAR(30) NOT NULL,  signup_date DATE NOT NULL);CREATE TABLE products (  product_id   INT PRIMARY KEY,  product_name VARCHAR(50) NOT NULL,  category     VARCHAR(20) NOT NULL,  list_price   INT NOT NULL,  unit_cost    INT NOT NULL);CREATE TABLE orders (  order_id    INT PRIMARY KEY,  customer_id INT NOT NULL,  order_date  DATE NOT NULL,  status      VARCHAR(12) NOT NULL,  channel     VARCHAR(12) NOT NULL,  FOREIGN KEY (customer_id) REFERENCES customers (customer_id));CREATE TABLE order_items (  order_id   INT,  product_id INT,  quantity   INT NOT NULL,  unit_price INT NOT NULL,  PRIMARY KEY (order_id, product_id),  FOREIGN KEY (order_id) REFERENCES orders (order_id),  FOREIGN KEY (product_id) REFERENCES products (product_id));INSERT INTO customers VALUES(1, 'Aarav Shah', 'Pune', '2024-10-24'),(2, 'Diya Menon', 'Kolkata', '2024-04-09'),(3, 'Kabir Ali', 'Delhi', '2024-10-03'),(4, 'Isha Reddy', 'Hyderabad', '2024-07-02'),(5, 'Vihaan Gupta', 'Jaipur', '2024-08-27'),(6, 'Anaya Iyer', 'Bengaluru', '2024-01-25'),(7, 'Reyansh Das', 'Chennai', '2024-12-05'),(8, 'Myra Kapoor', 'Mumbai', '2024-09-14'),(9, 'Arjun Nair', 'Pune', '2024-04-19'),(10, 'Saanvi Joshi', 'Kolkata', '2024-11-04'),(11, 'Ayaan Khan', 'Delhi', '2024-04-23'),(12, 'Kiara Bose', 'Hyderabad', '2024-05-27'),(13, 'Rudra Patil', 'Jaipur', '2024-09-05'),(14, 'Zara Thomas', 'Bengaluru', '2024-12-20');INSERT INTO products VALUES(1, 'Wireless Earbuds', 'Electronics', 2499, 1400),(2, 'Smart Watch', 'Electronics', 4999, 3100),(3, 'Bluetooth Speaker', 'Electronics', 1799, 950),(4, 'Power Bank', 'Electronics', 1299, 700),(5, 'Running Shoes', 'Fashion', 3299, 1700),(6, 'Denim Jacket', 'Fashion', 2599, 1300),(7, 'Cotton T-Shirt', 'Fashion', 599, 250),(8, 'Yoga Mat', 'Sports', 899, 400),(9, 'Dumbbell Set', 'Sports', 2199, 1250),(10, 'Cricket Bat', 'Sports', 3499, 2000),(11, 'Coffee Maker', 'Home', 3999, 2400),(12, 'Table Lamp', 'Home', 1199, 600),(13, 'Water Bottle', 'Home', 499, 180);INSERT INTO orders VALUES(5001, 4, '2025-01-02', 'Delivered', 'Web'),(5002, 1, '2025-01-04', 'Returned', 'Web'),(5003, 7, '2025-01-17', 'Delivered', 'Web'),(5004, 6, '2025-01-20', 'Delivered', 'Marketplace'),(5005, 2, '2025-01-21', 'Delivered', 'Marketplace'),(5006, 8, '2025-01-30', 'Delivered', 'App'),(5007, 11, '2025-01-30', 'Delivered', 'Web'),(5008, 7, '2025-02-01', 'Delivered', 'App'),(5009, 3, '2025-02-14', 'Delivered', 'Marketplace'),(5010, 1, '2025-02-19', 'Delivered', 'App'),(5011, 4, '2025-02-23', 'Delivered', 'Web'),(5012, 3, '2025-02-26', 'Delivered', 'App'),(5013, 8, '2025-03-13', 'Delivered', 'App'),(5014, 4, '2025-03-13', 'Delivered', 'App'),(5015, 10, '2025-03-18', 'Delivered', 'Marketplace'),(5016, 7, '2025-03-19', 'Delivered', 'App'),(5017, 5, '2025-03-23', 'Delivered', 'Marketplace'),(5018, 7, '2025-04-03', 'Delivered', 'Web'),(5019, 1, '2025-04-04', 'Delivered', 'App'),(5020, 3, '2025-04-06', 'Delivered', 'Web'),(5021, 8, '2025-04-15', 'Delivered', 'Marketplace'),(5022, 6, '2025-05-03', 'Delivered', 'Web'),(5023, 8, '2025-05-05', 'Delivered', 'App'),(5024, 7, '2025-05-06', 'Delivered', 'App'),(5025, 11, '2025-05-26', 'Cancelled', 'App'),(5026, 8, '2025-05-27', 'Delivered', 'App'),(5027, 5, '2025-05-31', 'Returned', 'Marketplace'),(5028, 7, '2025-06-03', 'Delivered', 'App'),(5029, 1, '2025-06-05', 'Delivered', 'App'),(5030, 9, '2025-06-12', 'Delivered', 'App'),(5031, 4, '2025-06-18', 'Returned', 'Web'),(5032, 6, '2025-06-21', 'Delivered', 'App'),(5033, 7, '2025-06-21', 'Delivered', 'Web'),(5034, 4, '2025-07-02', 'Delivered', 'Marketplace'),(5035, 4, '2025-08-04', 'Cancelled', 'Marketplace'),(5036, 1, '2025-08-10', 'Delivered', 'Web'),(5037, 1, '2025-08-18', 'Cancelled', 'App'),(5038, 11, '2025-08-23', 'Delivered', 'Web'),(5039, 6, '2025-08-23', 'Delivered', 'App'),(5040, 1, '2025-08-26', 'Delivered', 'Web'),(5041, 11, '2025-09-01', 'Delivered', 'Marketplace'),(5042, 6, '2025-09-07', 'Delivered', 'Web'),(5043, 9, '2025-09-21', 'Delivered', 'Marketplace'),(5044, 4, '2025-09-22', 'Delivered', 'App'),(5045, 7, '2025-09-27', 'Delivered', 'Marketplace'),(5046, 12, '2025-09-27', 'Delivered', 'Marketplace'),(5047, 1, '2025-09-28', 'Delivered', 'Marketplace'),(5048, 4, '2025-10-03', 'Delivered', 'Web'),(5049, 1, '2025-10-22', 'Delivered', 'Web'),(5050, 1, '2025-10-24', 'Returned', 'Web'),(5051, 3, '2025-10-28', 'Delivered', 'App'),(5052, 2, '2025-11-15', 'Delivered', 'Web'),(5053, 8, '2025-11-22', 'Delivered', 'App'),(5054, 8, '2025-11-23', 'Delivered', 'App'),(5055, 11, '2025-11-23', 'Delivered', 'Web'),(5056, 4, '2025-11-25', 'Cancelled', 'Marketplace'),(5057, 3, '2025-12-05', 'Delivered', 'Web'),(5058, 6, '2025-12-07', 'Cancelled', 'App'),(5059, 9, '2025-12-11', 'Delivered', 'Web'),(5060, 2, '2025-12-16', 'Cancelled', 'Web');INSERT INTO order_items VALUES(5001, 2, 2, 4999),(5001, 3, 1, 1799),(5002, 5, 1, 3299),(5003, 1, 2, 2499),(5003, 9, 1, 1980),(5003, 12, 1, 1199),(5004, 1, 3, 2250),(5004, 7, 3, 599),(5005, 2, 1, 4999),(5005, 5, 3, 3299),(5005, 10, 1, 3499),(5006, 5, 1, 3299),(5007, 3, 1, 1620),(5007, 10, 1, 3499),(5008, 4, 1, 1299),(5009, 1, 3, 2499),(5009, 3, 1, 1799),(5010, 12, 3, 1199),(5011, 3, 1, 1799),(5011, 9, 2, 2199),(5012, 4, 1, 1299),(5012, 5, 1, 2970),(5012, 10, 2, 3499),(5013, 6, 3, 2340),(5013, 7, 1, 599),(5014, 2, 2, 4500),(5014, 10, 1, 3499),(5015, 11, 1, 3999),(5015, 12, 1, 1199),(5016, 4, 1, 1299),(5017, 9, 1, 2199),(5017, 10, 1, 3499),(5018, 4, 2, 1299),(5018, 10, 1, 3150),(5019, 5, 1, 3299),(5019, 6, 3, 2599),(5019, 10, 1, 3499),(5020, 1, 1, 2499),(5020, 3, 1, 1799),(5021, 2, 2, 4500),(5021, 4, 1, 1299),(5022, 2, 3, 4999),(5022, 4, 1, 1299),(5023, 3, 1, 1799),(5023, 8, 1, 899),(5023, 11, 1, 3999),(5024, 3, 2, 1799),(5024, 10, 3, 3499),(5025, 9, 1, 2199),(5026, 3, 2, 1799),(5026, 5, 1, 3299),(5026, 6, 2, 2599),(5027, 3, 1, 1799),(5027, 11, 3, 3999),(5028, 9, 1, 2199),(5029, 1, 1, 2499),(5029, 7, 1, 599),(5029, 10, 3, 3499),(5030, 4, 1, 1299),(5030, 7, 1, 599),(5031, 3, 2, 1799),(5031, 4, 3, 1170),(5032, 6, 2, 2599),(5032, 11, 1, 3999),(5033, 2, 1, 4999),(5033, 5, 1, 3299),(5033, 11, 2, 3600),(5034, 4, 1, 1299),(5035, 1, 1, 2499),(5036, 7, 1, 599),(5037, 9, 1, 2199),(5037, 11, 3, 3600),(5038, 4, 1, 1299),(5038, 5, 3, 3299),(5039, 8, 1, 899),(5040, 1, 1, 2499),(5040, 6, 1, 2599),(5041, 3, 1, 1799),(5041, 9, 1, 2199),(5042, 6, 1, 2599),(5042, 10, 2, 3150),(5043, 12, 3, 1080),(5044, 3, 1, 1799),(5045, 2, 2, 4999),(5046, 2, 1, 4500),(5046, 3, 1, 1799),(5046, 8, 2, 810),(5047, 2, 1, 4999),(5047, 8, 2, 899),(5048, 1, 1, 2250),(5048, 3, 3, 1799),(5049, 11, 3, 3999),(5050, 1, 2, 2499),(5050, 11, 1, 3999),(5051, 1, 3, 2499),(5051, 3, 3, 1799),(5052, 1, 1, 2499),(5052, 8, 3, 899),(5052, 11, 1, 3999),(5053, 2, 1, 4999),(5053, 6, 2, 2599),(5054, 12, 3, 1199),(5055, 6, 2, 2599),(5055, 8, 1, 899),(5055, 9, 1, 1980),(5056, 3, 2, 1620),(5056, 12, 1, 1199),(5057, 7, 1, 540),(5058, 4, 2, 1299),(5058, 7, 2, 599),(5059, 1, 3, 2499),(5060, 5, 1, 3299),(5060, 9, 1, 1980);

SELECT COUNT(DISTINCT o.order_id)                        AS delivered_orders,       SUM(oi.quantity * oi.unit_price)                  AS revenue,       SUM(oi.quantity * (oi.unit_price - p.unit_cost))  AS gross_profit,       ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id)) AS avg_order_valueFROM orders oJOIN order_items oi ON oi.order_id = o.order_idJOIN products p     ON p.product_id = oi.product_idWHERE o.status = 'Delivered';

SELECT channel,       COUNT(*) AS orders,       SUM(status = 'Cancelled') AS cancelled,       SUM(status = 'Returned')  AS returned,       ROUND(100 * SUM(status <> 'Delivered') / COUNT(*), 1) AS problem_pctFROM ordersGROUP BY channelORDER BY problem_pct DESC;

WITH monthly AS (  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,         COUNT(DISTINCT o.order_id)          AS orders,         SUM(oi.quantity * oi.unit_price)    AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  WHERE o.status = 'Delivered'  GROUP BY month)SELECT month, orders, revenue,       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))             / LAG(revenue) OVER (ORDER BY month), 1) AS mom_growth_pct,       SUM(revenue) OVER (ORDER BY month) AS revenue_ytdFROM monthlyORDER BY month;

SELECT p.product_name, p.category,       SUM(oi.quantity)                 AS units,       SUM(oi.quantity * oi.unit_price) AS revenueFROM order_items oiJOIN orders o   ON o.order_id = oi.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.status = 'Delivered'GROUP BY p.product_id, p.product_name, p.categoryORDER BY revenue DESCLIMIT 5;

SELECT p.category,       SUM(oi.quantity * oi.unit_price)                       AS revenue,       SUM(oi.quantity * (oi.unit_price - p.unit_cost))       AS profit,       ROUND(100 * SUM(oi.quantity * (oi.unit_price - p.unit_cost))                 / SUM(oi.quantity * oi.unit_price), 1)        AS margin_pctFROM order_items oiJOIN orders o   ON o.order_id = oi.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.status = 'Delivered'GROUP BY p.categoryORDER BY profit DESC;

SELECT p.product_name,       SUM(oi.quantity) AS units,       SUM(oi.quantity * (p.list_price - oi.unit_price)) AS discount_givenFROM order_items oiJOIN orders o   ON o.order_id = oi.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.status = 'Delivered' AND oi.unit_price < p.list_priceGROUP BY p.product_id, p.product_nameORDER BY discount_given DESC;

WITH product_sales AS (  SELECT p.category, p.product_name, SUM(oi.quantity) AS units  FROM order_items oi  JOIN orders o   ON o.order_id = oi.order_id  JOIN products p ON p.product_id = oi.product_id  WHERE o.status = 'Delivered'  GROUP BY p.category, p.product_name),ranked AS (  SELECT product_sales.*, RANK() OVER (PARTITION BY category ORDER BY units DESC) AS rnk  FROM product_sales)SELECT category, product_name, unitsFROM rankedWHERE rnk = 1ORDER BY category;

SELECT p.product_id, p.product_name, p.categoryFROM products pWHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

SELECT c.customer_id, c.full_name, c.city,       COUNT(DISTINCT o.order_id)       AS orders,       SUM(oi.quantity * oi.unit_price) AS lifetime_value,       CASE WHEN COUNT(DISTINCT o.order_id) > 1 THEN 'Repeat' ELSE 'One-time' END AS buyer_typeFROM customers cJOIN orders o       ON o.customer_id = c.customer_id AND o.status = 'Delivered'JOIN order_items oi ON oi.order_id = o.order_idGROUP BY c.customer_id, c.full_name, c.cityORDER BY lifetime_value DESCLIMIT 5;

WITH per_customer AS (  SELECT c.customer_id, COUNT(o.order_id) AS delivered_orders  FROM customers c  LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'Delivered'  GROUP BY c.customer_id)SELECT CASE WHEN delivered_orders = 0 THEN 'Never bought'            WHEN delivered_orders = 1 THEN 'One-time'            ELSE 'Repeat' END AS segment,       COUNT(*) AS customersFROM per_customerGROUP BY segmentORDER BY customers DESC;

WITH rfm AS (  SELECT o.customer_id,         DATEDIFF('2025-12-31', MAX(o.order_date)) AS recency_days,         COUNT(DISTINCT o.order_id)                AS frequency,         SUM(oi.quantity * oi.unit_price)          AS monetary  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  WHERE o.status = 'Delivered'  GROUP BY o.customer_id),scored AS (  SELECT rfm.*,         NTILE(3) OVER (ORDER BY recency_days DESC) AS r_score,         NTILE(3) OVER (ORDER BY frequency)         AS f_score,         NTILE(3) OVER (ORDER BY monetary)          AS m_score  FROM rfm)SELECT customer_id, recency_days, frequency, monetary, r_score, f_score, m_score,       CASE WHEN r_score = 3 AND f_score >= 2 AND m_score >= 2 THEN 'Champion'            WHEN r_score = 1 THEN 'At risk'            ELSE 'Regular' END AS segmentFROM scoredORDER BY r_score DESC, m_score DESC, customer_id;

WITH firsts AS (  SELECT customer_id, MIN(order_date) AS first_order  FROM orders  WHERE status = 'Delivered'  GROUP BY customer_id)SELECT DATE_FORMAT(f.first_order, '%Y-%m') AS cohort,       COUNT(DISTINCT f.customer_id) AS customers,       COUNT(DISTINCT CASE WHEN o.order_date > f.first_order THEN o.customer_id END) AS came_backFROM firsts fLEFT JOIN orders o ON o.customer_id = f.customer_id AND o.status = 'Delivered'GROUP BY cohortORDER BY cohort;

SELECT c.city,       SUM(CASE WHEN o.channel = 'Web'         THEN oi.quantity * oi.unit_price ELSE 0 END) AS web,       SUM(CASE WHEN o.channel = 'App'         THEN oi.quantity * oi.unit_price ELSE 0 END) AS app,       SUM(CASE WHEN o.channel = 'Marketplace' THEN oi.quantity * oi.unit_price ELSE 0 END) AS marketplace,       SUM(oi.quantity * oi.unit_price) AS totalFROM orders oJOIN customers c    ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idWHERE o.status = 'Delivered'GROUP BY c.cityORDER BY total DESC;