CODEWITHSUNDEEP
sqlpostgresql
Michi
Michi

Reputation: 5471

Check if a customer has not placed another order in the 12 months after his last order

DB-Fiddle

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    customer VARCHAR(255)
);

INSERT INTO customers
(order_date, customer)
VALUES 
('2020-05-10', 'user_01'),
('2020-05-15', 'user_01'),
('2020-05-18', 'user_02'),
('2020-05-26', 'user_03'),

('2020-06-12', 'user_04'),
('2020-06-19', 'user_05'),
('2020-06-23', 'user_06'),


('2021-05-09', 'user_01'),
('2021-05-17', 'user_07'),

('2021-06-03', 'user_04'),
('2021-06-18', 'user_05'),
('2021-06-20', 'user_08');

Expected Result:

churn_date    |   customer   |
--------------|--------------|----
2021-05-18    |   user_02    |
2021-05-26    |   user_03    |
--------------|--------------|-----
2021-06-23    |   user_06    |

I want to extract all customers that

a) had an order exactly 12 months ago and
b) have not ordered again since this order.

For a single month I am able to to this with this query:

SELECT
(c1.order_date + interval '12 month')::date as churn_date,
c1.customer
FROM customers c1
WHERE c1.order_date BETWEEN '2020-05-01 00:00:00' AND '2020-05-31 23:59:59'

AND NOT EXISTS

   (SELECT
    c2.customer
    FROM customers c2
    WHERE c2.order_date BETWEEN '2020-06-01 00:00:00' AND '2021-05-31 23:59:59'
    AND c2.customer = c1.customer)
    
ORDER BY 1,2;

However, instead of querying each month seperately I would prefer a solution that iterates through the data and gives me the result for multiple months.

How do I need to modify my query to achieve this?

Upvotes: 1

Views: 313

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Check if a customer has not placed another order in the 12 months after his last order

This answers the question. Just use GROUP BY with HAVING:

SELECT c.customer,
       MAX(c.order_date + interval '12 month')::date as churn_date
FROM customers c
GROUP BY c.customer
HAVING MAX(c.order_date) < CURRENT_DATE - interval '12 month';

Apparently, the question that you want answered is a bit different from the one you describe in English. It is something more along the lines of:

For each calendar month, which users had there last order in the calendar month exactly 12 months before that. The "churn date" is then exactly 12 months after the last purchase date.

A little bit different, but the query is:

SELECT gs.month, c.customer, MAX(c.order_date + interval '12 month')::date as churn_date
FROM customers c CROSS JOIN
     GENERATE_SERIES('2021-05-01'::date, '2021-06-01'::date, interval '1 month') gs(month)
GROUP BY gs.month, c.customer
HAVING DATE_TRUNC('month', MAX(c.order_date)) = DATE_TRUNC('month', gs.month) - interval '12 month';

Here is a db<>fiddle.

Upvotes: 1

Related Questions