Customer Order Frequency Problem
Description
LeetCode Problem 1511.
Table: Customers
1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
| country | varchar |
+---------------+---------+
customer_id is the primary key for this table.
This table contains information of the customers in the company.
Table: Product
1
2
3
4
5
6
7
8
9
10
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| description | varchar |
| price | int |
+---------------+---------+
product_id is the primary key for this table.
This table contains information of the products in the company.
price is the product cost.
Table: Orders
1
2
3
4
5
6
7
8
9
10
11
12
13
+---------------+---------+
| 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Customers
+--------------+-----------+-------------+
| customer_id | name | country |
+--------------+-----------+-------------+
| 1 | Winston | USA |
| 2 | Jonathan | Peru |
| 3 | Moustafa | Egypt |
+--------------+-----------+-------------+
Product
+--------------+-------------+-------------+
| product_id | description | price |
+--------------+-------------+-------------+
| 10 | LC Phone | 300 |
| 20 | LC T-Shirt | 10 |
| 30 | LC Book | 45 |
| 40 | LC Keychain | 2 |
+--------------+-------------+-------------+
Orders
+--------------+-------------+-------------+-------------+-----------+
| 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 |
+--------------+-------------+-------------+-------------+-----------+
Result table:
+--------------+------------+
| customer_id | name |
+--------------+------------+
| 1 | Winston |
+--------------+------------+
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.
MySQL Solution
1
2
3
4
5
6
7
8
9
10
11
12
select customer_id, name
from
(
select o.customer_id, c.name,
sum(case when left(o.order_date,7) = '2020-06' then p.price * o.quantity end) as JuneSpend,
sum(case when left(o.order_date,7) = '2020-07' then p.price * o.quantity end) as JulySpend
from Orders o
left join Customers c on o.customer_id = c.customer_id
left join Product p on o.product_id = p.product_id
group by o.customer_id
having JuneSpend >= 100 and JulySpend >= 100
) as temp
LeetCode Database - Easy
LeetCode 1407
LeetCode 1435
LeetCode 1484
LeetCode 1495
LeetCode 1511
LeetCode 1517
LeetCode 1527
LeetCode 1543
LeetCode 1565
LeetCode 1571
LeetCode 1581
LeetCode 1587
LeetCode 1607
LeetCode 1623
LeetCode 1633
LeetCode 1661
LeetCode 1667
LeetCode 1677
LeetCode 1683
LeetCode 1693
LeetCode 1729
LeetCode 1731
LeetCode 1741
LeetCode 1757
LeetCode 1777
LeetCode 1789
LeetCode 1795
LeetCode 1809
More LeetCode Database
MySQL Tutorials