Count date if it is in the same months as the first order of the customer

DB Fiddle

/* table returned */
CREATE TABLE returned (
    id int auto_increment primary key,
    customerID VARCHAR(255),
    returned_date DATE
);

INSERT INTO returned
(customerID, returned_date
)
VALUES 
("Customer_01", "2020-03-20"),
("Customer_01", "2020-03-22"),
("Customer_01", "2020-04-19"),
("Customer_02", "2020-08-09"),
("Customer_03", "2020-07-31"),
("Customer_03", "2020-08-25"),
("Customer_04", "2020-09-16"),
("Customer_04", "2021-09-25");


/* table customers */
CREATE TABLE customers (
    id int auto_increment primary key,
    customerID VARCHAR(255),
    first_order DATE
);

INSERT INTO customers
(customerID, first_order
)
VALUES 
("Customer_01", "2020-03-15"),
("Customer_02", "2020-06-16"),
("Customer_03", "2020-07-27"),
("Customer_04", "2020-08-10");

Expected Result

customerID      returned_date   first_order             COUNT
Customer_01     2020-03-20      2020-03-15                1
Customer_01     2020-03-22      2020-03-15                0
Customer_01     2020-04-19      2020-03-15                0
Customer_02     2020-08-09      2020-06-16                0
Customer_03     2020-07-31      2020-07-27                1
Customer_03     2020-08-25      2020-07-27                0
Customer_04     2020-09-16      2020-08-10                0

My database consists of two tables returned and customers.
In the customers table you can find the date of the first_order per customer.
In the returned table you can find each return a customer has done so far.

Now, I want to create a list and count all returned_dates which are in the same month as the first_order date of a certain customer.
If there are multiple orders in the same month then only the first match should be counted 1.
The others should be 0. (see Customer_01 in the example)

The difference to this question is that there is no return_date that matches exactly with a first_order date.
Therefore, this query does not work:

SELECT
r.customerID,
r.returned_date,
c.first_order,
(r.returned_date = c.first_order) is_first_order
FROM returned r
INNER JOIN customers c ON c.customerID = r.customerID

Do you have any idea how I can achieve the expected result?

Answer

You seem to want:

select r.customerID, r.returned_date, c.first_order,
       (case when r.returned_date < c.first_order + interval 1 month and
                  row_number() over (partition by c.customerID order by returned_date) = 1
             then 1 else 0
         end) as return_flag
from returned r join
     customers c
     on c.customerID = r.customerID;

This flags the first return as “1” only if it is the first order and occurs within a month of the start date for the customer.

Advertisement

Answer