Write an SQL query to list customers who have not placed an order in the last 6 months but have placed more than 5 orders in total.

Table creation script:

-- Create Customers table
CREATE TABLE Customers101 (
    Customer_id INT PRIMARY KEY,
    Name VARCHAR(50),
    Join_Date DATE
);

CREATE TABLE Orders101 (
    Order_id INT PRIMARY KEY,
    Customer_id INT,
    Order_Date DATE,
    Amount DECIMAL(10, 2),
    FOREIGN KEY (Customer_id) REFERENCES Customers101(Customer_id)
);

Data insertion script:

-- Insert sample data into Customers table
INSERT INTO Customers101 (Customer_id, Name, Join_Date)
VALUES
    (1, 'John Doe', '2023-01-01'),
    (2, 'Jane Smith', '2023-01-02'),
    (3, 'Bob Johnson', '2023-01-03'),
    (4, 'Mark Kith', '2023-10-03'),
    (5, 'Alia Mary', '2023-1-03');

-- Insert sample data into Orders table
INSERT INTO Orders101 (Order_id, Customer_id, Order_Date, Amount)
VALUES
    (101, 1, '2023-01-05', 150.50),
    (102, 2, '2023-01-06', 200.75),
    (103, 1, '2023-01-07', 120.00),
    (104, 3, '2023-01-08', 300.25),
    (105, 1, '2023-01-05', 150.50),
    (106, 1, '2023-02-06', 200.75),
    (107, 1, '2023-03-07', 120.00),
    (108, 1, '2023-05-08', 300.25),
    (109, 2, '2023-01-06', 200.75),
    (110, 2, '2023-11-06', 200.75),
    (111, 2, '2023-10-06', 200.75),
    (112, 2, '2023-01-06', 200.75),
    (113, 4, '2023-11-06', 200.75),
    (114, 4, '2023-11-07', 200.75),
    (115, 4, '2023-11-08', 200.75),
    (116, 4, '2023-11-09', 200.75),
    (117, 4, '2023-11-10', 200.75),
    (118, 4, '2023-02-12', 200.75),
    (119, 5, '2023-02-05', 150.50),
    (120, 5, '2023-03-05', 150.50),
    (121, 5, '2023-04-05', 150.50),
    (122, 5, '2023-04-15', 150.50),
    (123, 5, '2023-04-25', 150.50),
    (124, 5, '2023-03-03', 150.50),
    (125, 5, '2023-02-02', 150.50);

Data:

Customers and Orders data

Task: Write an SQL query to list customers who have not placed an order in the last 6 months but have placed more than 5 orders in total.

Final Query:

SELECT  a.customer_id ,b.name
FROM Orders101 AS a
INNER JOIN Customers101 AS b
ON a.Customer_id = b.Customer_id
GROUP BY customer_id
HAVING MONTH(CURRENT_DATE())-MONTH(max(a.Order_Date)) > 6
AND count(*) > 5;

customers who have not placed an order in the last 6 months but have placed more than 5 orders in total.

Detailed discussion (step-by-step query):

1- Bring customers and their order data together (i.e. Join the customer and orders table)

-- Bring customer and orders data together
SELECT   a.* ,b.* 
FROM Orders101 AS a
INNER JOIN Customers101 AS b
ON a.Customer_id = b.Customer_id;

2- Find the last order_date and total no. of orders placed by each customer(i.e. Group by customer id, and for each customer id group , find the max order date(last order date) and count(*) (total orders)

SELECT  a.customer_id , max(a.Order_Date) , count(*)
FROM Orders101 AS a
INNER JOIN Customers101 AS b
ON a.Customer_id = b.Customer_id
GROUP BY customer_id

Last order_date and total no. of orders placed by each customer

3- Filter only those customer_id groups where the difference between last order date and the current date is more than 6 months, and the customer has placed more than 5 orders

SELECT  a.customer_id , max(a.Order_Date) , count(*)
FROM Orders101 AS a
INNER JOIN Customers101 AS b
ON a.Customer_id = b.Customer_id
GROUP BY customer_id
HAVING MONTH(CURRENT_DATE())-MONTH(max(a.Order_Date)) > 6
AND count(*) > 5;

4 — In the final step, show only the name and id of the customer

SELECT  a.customer_id , b.name
FROM Orders101 AS a
INNER JOIN Customers101 AS b
ON a.Customer_id = b.Customer_id
GROUP BY customer_id
HAVING MONTH(CURRENT_DATE())-MONTH(max(a.Order_Date)) > 6
AND count(*) > 5;

Thank you !

Please drop your solution in the comment.

Please let me know in case of any issues in my solution or if you have any queries