LeetCode MySQL 1511. Customer Order Frequency

文章目录

• • 1. 题目
  • 2. 解题

1. 题目

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 of 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 of 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.

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.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/customer-order-frequency
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

2. 解题

• 返回6月和7月都花了至少100美元的人

# Write your MySQL query statement below select o.customer_id, c.name from Customers c, Product p, Orders o where c.customer_id = o.customer_id and p.product_id = o.product_id group by o.customer_id having (sum(case when o.order_date like '2020-06%' then o.quantity*p.price else 0 end) >= 100andsum(case when o.order_date like '2020-07%' then o.quantity*p.price else 0 end) >= 100 )

762 ms

---

我的CSDN博客地址 https://michael.blog.csdn.net/

长按或扫码关注我的公众号（Michael阿明），一起加油、一起学习进步！

总结

以上是生活随笔为你收集整理的LeetCode MySQL 1511. Customer Order Frequency的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。