Welcome to Subscribe On Youtube
1511. Customer Order Frequency
Description
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.
The query result format is in the following example.
Example 1:
Input: Customers table: +--------------+-----------+-------------+ | customer_id | name | country | +--------------+-----------+-------------+ | 1 | Winston | USA | | 2 | Jonathan | Peru | | 3 | Moustafa | Egypt | +--------------+-----------+-------------+ Product table: +--------------+-------------+-------------+ | product_id | description | price | +--------------+-------------+-------------+ | 10 | LC Phone | 300 | | 20 | LC T-Shirt | 10 | | 30 | LC Book | 45 | | 40 | LC Keychain | 2 | +--------------+-------------+-------------+ Orders table: +--------------+-------------+-------------+-------------+-----------+ | order_id | customer_id | product_id | order_date | quantity | +--------------+-------------+-------------+-------------+-----------+ | 1 | 1 | 10 | 2020-06-10 | 1 | | 2 | 1 | 20 | 2020-07-01 | 1 | | 3 | 1 | 30 | 2020-07-08 | 2 | | 4 | 2 | 10 | 2020-06-15 | 2 | | 5 | 2 | 40 | 2020-07-01 | 10 | | 6 | 3 | 20 | 2020-06-24 | 2 | | 7 | 3 | 30 | 2020-06-25 | 2 | | 9 | 3 | 30 | 2020-05-08 | 3 | +--------------+-------------+-------------+-------------+-----------+ Output: +--------------+------------+ | customer_id | name | +--------------+------------+ | 1 | Winston | +--------------+------------+ Explanation: Winston spent 300 (300 * 1) in June and 100 ( 10 * 1 + 45 * 2) in July 2020. Jonathan spent 600 (300 * 2) in June and 20 ( 2 * 10) in July 2020. Moustafa spent 110 (10 * 2 + 45 * 2) in June and 0 in July 2020.
Solutions
Solution 1: Join + Group By + Having
We can use the JOIN statement to join the Orders table and the Product table, and then join the result with the Customers table. We can filter out the records where the order_date is not in the year $2020$, and then use the GROUP BY statement to group the data by customer_id. Finally, we can use the HAVING statement to filter out the customers whose spending in June and July is greater than or equal to $100$.
-
# Write your MySQL query statement below SELECT customer_id, name FROM Orders JOIN Product USING (product_id) JOIN Customers USING (customer_id) WHERE YEAR(order_date) = 2020 GROUP BY 1 HAVING SUM(IF(MONTH(order_date) = 6, quantity * price, 0)) >= 100 AND SUM(IF(MONTH(order_date) = 7, quantity * price, 0)) >= 100;