Create list of dates if values exist before and after a certain time period

Question

I have the following table which you can also find in the SQL Fiddle here:

CREATE TABLE Orders (
    Customer TEXT,
    Order_Date DATE
);

INSERT INTO Orders
(Customer, Order_Date)
VALUES 
("Customer A", "2017-05-23"),
("Customer A", "2019-01-03"),
("Customer A", "2019-02-15"),
("Customer A", "2019-02-16"),

("Customer B", "2018-09-10"),
("Customer B", "2019-01-09"),

("Customer C", "2016-09-04"),
("Customer C", "2019-02-12"),
("Customer C", "2019-02-20"),

("Customer D", "2017-03-15"),
("Customer D", "2019-02-17"),
("Customer D", "2019-02-19"),
("Customer D", "2019-02-20"),

("Customer E", "2019-02-03"),
("Customer E", "2015-10-12");

As you can see the table displays the order dates from different customers.
I use the below SQL to get the unique count of orders for customers that:

a) placed an order in February 2019 and
b) did not place an order in the 12 month before and
c) placed an order before this 12 months period

Referring to the answer here.

SELECT o.Customer,
       MAX( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) as num_feb_orders
FROM ORDERS o
GROUP BY o.Customer
HAVING SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) > 0 AND
       SUM( o.Order_Date >= '2018-02-01' AND o.Order_Date < '2019-02-01' ) = 0 AND
       SUM( o.Order_Date < '2018-02-01' ) > 0 ;

All this works fine so far.

---

However, now instead of make the GROUP BY based on the customer I want to make the GROUP BY based on the Order_Date so all latest order_dates in February referring to customers that fullfill the above criterias should be listed. The result should look like this.

Order_Date      UniqueOrders
2019-02-03            1       --> Customer E
2019-02-20            2       --> Customer C and Customer D

What do I need to change in my code to make this work?

Answer 1

Hope this helps I have used the given query as a sub query to get the required out put,

SELECT OrderDate, 
    (COUNT(*) 
     FROM (
         SELECT o.Customer, MAX(o.Order_Date) AS OrderDate,
             MAX( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) AS UniqueOrders
         FROM ORDERS AS o
         GROUP BY o.Customer
        HAVING SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) > 0 AND
           SUM( o.Order_Date >= '2018-02-01' AND o.Order_Date < '2019-02-01' ) = 0 AND
           SUM( o.Order_Date < '2018-02-01' ) > 0)a
GROUP BY OrderDate ;