List (loyal) customers who've made purchases in every year-CodePudding

I want to list customer_id's that have made purchases from every distinctly available year from a column.

The column is in sales.orders called 'order_date'

This is what I've done so far (didn't work):

--List customers who've made purchases in every year
SELECT customer_id
FROM sales.orders
WHERE year(order_date) = ALL (
    select distinct YEAR(order_date)
    from sales.orders
)

I wanted something that shows the customer_id only if they've made purchases at least once a year since opening.

CodePudding user response：

Using COUNT, @Lanru suggestion was not Wrong.

Here what you can do

Assuming that your table look like below :

CREATE TABLE sales.orders 
( 
    customer_id NVARCHAR(4000), 
    order_date date
); 

INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1002', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1002', '10-10-2022'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2021'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2021'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2022'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2021'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2022'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2022');

This is the solutions you're asking for :

    SELECT me.customer_id FROM (
SELECT  DISTINCT customer_id, 
        COUNT(DISTINCT YEAR(order_date)) AS 'count_year'
FROM sales.orders
GROUP BY customer_id ) AS me
WHERE me.count_year = (SELECT COUNT(DISTINCT YEAR(order_date) ) FROM sales.orders)

CodePudding user response：

SELECT *
FROM sales.customers
WHERE customer_id IN (
        SELECT t.customer_id
        FROM (
            SELECT o.customer_id,
                count(DISTINCT (year(order_date))) AS cnt
            FROM sales.orders AS o
            GROUP BY customer_id
            ) t
        WHERE t.cnt = (
                SELECT count(DISTINCT (year(order_date)))
                FROM sales.orders
                )
        )