CODEWITHSUNDEEP
sqlsql-server
user9489168
user9489168

Reputation: 35

How to write a SQL query to find out customers who have purchased at least two times every month of the year

I have a table with columns order_id, customer_id, order_date and Amount.

enter image description here

How to find the customers who have ordered at least 2 times each month of the year in SQL (from Jan to Dec)?

Upvotes: 1

Views: 2507

Answers (1)

Aducci
Aducci

Reputation: 26644

I think you are looking for something like this

select
    order_date_year,
    customer_id
from 
(
    select  
        year(order_date) as order_date_year,
        month(order_date) as order_date_month,
        customer_id,
        count(*) as number_of_orders
    from
        tableName
    group by
        year(order_date),
        month(order_date),
        customer_id
    having
        count(*) >= 2
) as t
group by
    order_date_year,
    customer_id
having
    count(*) = 12

Upvotes: 6

Related Questions