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




Related Posts

Sales By Day Of The Week Problem

LeetCode 1479. Write an SQL query to report how many...

Countries You Can Safely Invest In Problem

LeetCode 1501. Write an SQL query to find the countries...

Friendly Movies Streamed Last Month Problem

LeetCode 1495. Write an SQL query to report the distinct...

Find Users With Valid Emails Problem

LeetCode 1517. Write an SQL query to find the users...

Customer Order Frequency Problem

LeetCode 1511. Write an SQL query to report the customer_id...

Group Sold Products By The Date Problem

LeetCode 1484. Write an SQL query to find for each...