Problem
Table: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| country | varchar |
+---------------+---------+
customer_id is the primary key for this table. This table contains information about the customers in the company.
Table: Product
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| description | varchar |
| price | int |
+---------------+---------+
product_id is the primary key for this table. This table contains information on the products in the company. price is the product cost.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_id | int |
| order_date | date |
| quantity | int |
+---------------+---------+
order_id is the primary key for this table. This table contains information on customer orders. customer_id is the id of the customer who bought “quantity” products with id “product_id”. Order_date is the date in format (‘YYYY-MM-DD’) when the order was shipped.
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.
Return the result table in any order.
Solution
Method 1 - Join
Code
Sql
SELECT c.customer_id, name
FROM Orders o
JOIN Customers c USING (customer_id)
JOIN Product p USING (product_id)
GROUP BY name
HAVING SUM(CASE WHEN MONTH(order_date) = 6 THEN quantity*price END) >= 100 AND
SUM(CASE WHEN MONTH(order_date) = 7 THEN quantity*price END) >= 100;