Iterating through users and check if they exist in the past 12 months

DB-Fiddle

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

INSERT INTO customers
(order_date, customer)
VALUES 
('2020-04-10', 'user_01'),
('2020-05-15', 'user_01'),
('2020-09-08', 'user_02'),
('2020-11-23', 'user_03'),

('2020-01-03', 'user_04'),
('2020-06-03', 'user_04'),
('2020-06-03', 'user_04'),
('2020-07-01', 'user_05'),
('2020-09-24', 'user_06'),


('2021-05-02', 'user_01'),
('2021-05-05', 'user_02'),
('2021-05-12', 'user_03'),
('2021-05-19', 'user_03'),
('2021-05-20', 'user_07'),

('2021-06-08', 'user_04'),
('2021-06-20', 'user_05'),
('2021-06-21', 'user_05'),
('2021-06-25', 'user_08');

Expected Result:

order_date   |   customer  |  
-------------|-------------|----
2021-05-05   |   user_02   |  
2021-05-12   |   user_03   |  
2021-05-19   |   user_03   |  
-------------|-------------|----
2021-06-20   |   user_05   |  
2021-06-21   |   user_05   |

I want to list all customers in a certain month which

a) exist in the past 12 months and
b) also exist in the current month.

For a single month I am able to achieve this by using this query:

SELECT
c1.order_date,
c1.customer
FROM customers c1
WHERE c1.order_date BETWEEN '2021-05-01 00:00:00' AND '2021-05-31 23:59:59'

AND EXISTS

   (SELECT
    c2.customer
    FROM customers c2
    WHERE c2.order_date BETWEEN '2020-06-01 00:00:00' AND '2021-04-30 23:59:59'
    AND c2.customer = c1.customer)

ORDER BY 2;

However, I have to run this query for each month seperatly.

Therefore, I am wondering if there is an iterating solution that goes through multiple months at once.

In the example above it would run BETWEEN '2021-05-01 00:00:00' AND '2021-06-30 23:59:59' and calculate 12 months back from May and in the next step 12 months back from June to get the expected result.

Do you have any idea if this is possible?

Answer

Use LAG() to check if there is an order in the previous 12 months:

SELECT c.order_date, c.customer
FROM (SELECT c.*,
             LAG(order_date) OVER (PARTITION BY customer) as prev_order_date
      FROM customers c
     ) c
WHERE c.order_date >= '2021-05-01' AND c.order_date < '2021-06-30' AND
      c.prev_order_date >= c.order_date - INTERVAL '12 month';

Here is a db<>fiddle.

Note that I fixed the date comparisons so you are not fiddling with seconds when defining a month timeframe.

Advertisement

Answer