1511. Customer Order Frequency

April 7, 2022

Write an SQL query to report the customer_id and customer_name of customers who have spent at least $100 in each month of June and July 2020.

code mysql

SELECT customer_id, name
FROM Customers JOIN Orders USING(customer_id) 
    JOIN Product USING(product_id)
GROUP BY customer_id
HAVING SUM(IF(LEFT(order_date, 7) = '2020-06', quantity, 0) * price) >= 100
    AND SUM(IF(LEFT(order_date, 7) = '2020-07', quantity, 0) * price) >= 100

code mssql

select o.customer_id, max(c.name) as name from Orders o 
join Product p on p.product_id = o.product_id
join Customers c on c.customer_id = o.customer_id
where MONTH(o.order_date) = 6 
group by o.customer_id having sum(o.quantity * p.price) >= 100
intersect
select o.customer_id, max(c.name) as name from Orders o 
join Product p on p.product_id = o.product_id
join Customers c on c.customer_id = o.customer_id
where MONTH(o.order_date) = 7
group by o.customer_id having sum(o.quantity * p.price) >= 100