Mysql Scenario Question 2 | Find customers who placed more than one delivered order | Rajdeep Da

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

CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
order_status VARCHAR(50) NOT NULL,
payment_mode VARCHAR(50) NOT NULL,

CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
discount_percent DECIMAL(5,2) DEFAULT 0,

CONSTRAINT fk_items_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id),

CONSTRAINT fk_items_product
FOREIGN KEY (product_id)
REFERENCES products(product_id)
);

INSERT INTO customers
(customer_id, customer_name, city, signup_date)
VALUES
(1, 'Amit Sharma', 'Kolkata', '2024-01-10'),
(2, 'Priya Sen', 'Kolkata', '2024-01-15'),
(3, 'Rahul Das', 'Delhi', '2024-02-01'),
(4, 'Sneha Roy', 'Mumbai', '2024-02-10'),
(5, 'Arjun Mehta', 'Delhi', '2024-03-05'),
(6, 'Riya Ghosh', 'Kolkata', '2024-03-15'),
(7, 'Sourav Bose', 'Bangalore', '2024-04-01'),
(8, 'Neha Agarwal', 'Mumbai', '2024-04-20'),
(9, 'Kiran Dutta', 'Kolkata', '2024-05-01'),
(10, 'Moumita Pal', 'Delhi', '2024-05-12');

INSERT INTO products
(product_id, product_name, category, price)
VALUES
(101, 'Laptop', 'Electronics', 55000.00),
(102, 'Mobile Phone', 'Electronics', 25000.00),
(103, 'Headphones', 'Electronics', 2000.00),
(104, 'Office Chair', 'Furniture', 7500.00),
(105, 'Study Table', 'Furniture', 12000.00),
(106, 'Notebook', 'Stationery', 80.00),
(107, 'Pen Pack', 'Stationery', 150.00),
(108, 'Water Bottle', 'Lifestyle', 500.00),
(109, 'Backpack', 'Lifestyle', 1800.00),
(110, 'Keyboard', 'Electronics', 1500.00);

INSERT INTO orders
(order_id, customer_id, order_date, order_status, payment_mode)
VALUES
(1001, 1, '2024-01-12', 'Delivered', 'UPI'),
(1002, 2, '2024-01-18', 'Delivered', 'Card'),
(1003, 1, '2024-02-05', 'Delivered', 'UPI'),
(1004, 3, '2024-02-08', 'Cancelled', 'COD'),
(1005, 4, '2024-02-15', 'Delivered', 'Card'),
(1006, 5, '2024-03-10', 'Delivered', 'UPI'),
(1007, 2, '2024-03-12', 'Delivered', 'UPI'),
(1008, 6, '2024-03-18', 'Returned', 'COD'),
(1009, 7, '2024-04-05', 'Delivered', 'Card'),
(1010, 8, '2024-04-22', 'Delivered', 'UPI'),
(1011, 1, '2024-05-03', 'Delivered', 'Card'),
(1012, 3, '2024-05-09', 'Delivered', 'UPI'),
(1013, 9, '2024-05-15', 'Cancelled', 'COD'),
(1014, 10, '2024-05-20', 'Delivered', 'UPI'),
(1015, 6, '2024-06-02', 'Delivered', 'Card'),
(1016, 4, '2024-06-10', 'Delivered', 'UPI'),
(1017, 7, '2024-06-15', 'Returned', 'COD'),
(1018, 8, '2024-06-18', 'Delivered', 'Card'),
(1019, 2, '2024-07-01', 'Delivered', 'UPI'),
(1020, 5, '2024-07-05', 'Delivered', 'Card');

INSERT INTO order_items
(item_id, order_id, product_id, quantity, discount_percent)
VALUES
(1, 1001, 101, 1, 10),
(2, 1001, 103, 2, 5),

(3, 1002, 102, 1, 0),
(4, 1002, 108, 3, 0),

(5, 1003, 104, 1, 15),
(6, 1003, 106, 10, 0),

(7, 1004, 105, 1, 0),
(8, 1004, 107, 5, 0),

(9, 1005, 101, 1, 5),
(10, 1005, 110, 2, 0),

(11, 1006, 109, 2, 10),
(12, 1006, 108, 2, 0),

(13, 1007, 102, 1, 5),
(14, 1007, 103, 1, 0),

(15, 1008, 104, 1, 0),
(16, 1008, 106, 20, 0),

(17, 1009, 105, 1, 10),
(18, 1009, 107, 10, 0),

(19, 1010, 101, 1, 0),
(20, 1010, 109, 1, 0),

(21, 1011, 102, 2, 10),
(22, 1011, 103, 3, 5),

(23, 1012, 104, 2, 0),
(24, 1012, 108, 4, 0),

(25, 1013, 101, 1, 0),
(26, 1013, 110, 1, 0),

(27, 1014, 105, 1, 5),
(28, 1014, 106, 15, 0),

(29, 1015, 102, 1, 0),
(30, 1015, 109, 2, 5),

(31, 1016, 101, 1, 10),
(32, 1016, 103, 2, 0),

(33, 1017, 104, 1, 0),
(34, 1017, 107, 8, 0),

(35, 1018, 105, 2, 15),
(36, 1018, 108, 5, 0),

(37, 1019, 102, 1, 5),
(38, 1019, 110, 1, 0),

(39, 1020, 109, 3, 10),
(40, 1020, 106, 25, 0);

SELECT * FROM customers;
SELECT * FROM products;
SELECT * FROM orders;
SELECT * FROM order_items;
-- Find customers who placed more than one delivered order.
-- Business Scenario

-- The marketing team wants to find repeat customers.
-- A repeat customer means a customer who has placed more than one Delivered order.

select c.customer_id,c.customer_name ,count(o.order_id) as number_of_orders
from customers c
join orders o
on c.customer_id=o.customer_id
where o.order_status='Delivered'
group by c.customer_id,c.customer_name
having number_of_orders greater then 1
order by number_of_orders desc;

Видео Mysql Scenario Question 2 | Find customers who placed more than one delivered order | Rajdeep Da канала Rajdeep Dar Pathshala