Find clients who have not placed an order in the past 18 months-CodePudding

I have a mysql db with client orders and I am using client email addresses to distinguish between different clients. I would like to find all clients who have not ordered in the past 18 months. To be clear, I do not want to list all orders that were placed more than 18 months ago but only find those clients (removing duplicates based on emails) who have not placed an order within the past 18 months.

Example 1: a client who placed an order 2 years ago and then placed another order 1 year ago should not appear in the results of this query. Example 2: a client who placed an order 3 years ago and another one 2 years ago should appear in the results but only once.

Here an example Table

| order_id | client_name | client_email | order_date |
|---------:|:-----------:|:------------:|:----------:|
| 1        | Peter Pan   | [email protected]  | 2022-11-02 |
| 2        | John Doe    | [email protected]  | 2021-11-02 |
| 3        | Jane Doe    | [email protected]  | 2020-11-02 |
| 4        | Peter Pan   | [email protected]  | 2020-10-22 |
| 5        | Jane Doe    | [email protected]  | 2019-10-22 |
| 6        | John Doe    | [email protected]  | 2018-10-22 |

For this example, the query should yield only 1 result.

Jane Doe [email protected]

Because both Peter Pan and John Doe have ordered within the past 18 months.

CodePudding user response：

Ok this is written without a db, so let's hope it's right.

select distinct o1.client_email, o1.client_name from orders as o1
where (select max(order_date) from orders as o2 where o2.client_email = o1.client_email) < NOW() - INTERVAL 18 MONTH

Something along those lines.