Problem

Table: Customers

1
2
3
4
5
6
7

+---------------+---------+
| 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

1
2
3
4
5
6
7

+---------------+---------+
| 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

1
2
3
4
5
6
7
8
9

+---------------+---------+
| 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

1
2
3
4
5
6
7

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;