今日真题
题目介绍： 消费者下单频率 customer-order-frequency
难度简单
SQL架构
表:
代码语言：javascript复制Customers --------------- ---------
| Column Name | Type |
--------------- ---------
| customer_id | int |
| name | varchar |
| country | varchar |
--------------- ---------
customer_id 是该表主键.
该表包含公司消费者的信息.表:
代码语言：javascript复制Product --------------- ---------
| Column Name | Type |
--------------- ---------
| product_id | int |
| description | varchar |
| price | int |
--------------- ---------
product_id 是该表主键.
该表包含公司产品的信息.
price 是本产品的花销.表:
代码语言：javascript复制Orders --------------- ---------
| Column Name | Type |
--------------- ---------
| order_id | int |
| customer_id | int |
| product_id | int |
| order_date | date |
| quantity | int |
--------------- ---------
order_id 是该表主键.
该表包含消费者下单的信息.
customer_id 是买了数量为"quantity", id为"product_id"产品的消费者的 id.
Order_date 是订单发货的日期, 格式为('YYYY-MM-DD').写一个 SQL 语句, 报告消费者的 id 和名字, 其中消费者在 2020 年 6 月和 7 月, 每月至少花费了$100.
结果表无顺序要求.
查询结果格式如下例所示.
``` 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 表: -------------- ------------ | customer_id | name | -------------- ------------ | 1 | Winston | -------------- ------------ Winston 在2020年6月花费了300(300 * 1), 在7月花费了100(10 * 1 45 * 2). Jonathan 在2020年6月花费了600(300 * 2), 在7月花费了20(2 * 10). Moustafa 在2020年6月花费了110 (10 * 2 45 * 2), 在7月花费了0. ```
代码语言：javascript复制sql
select customer_id,name
from Customers
where customer_id in
(select customer_id
from
(select customer_id, month(order_date) as month , sum(quantity*price) as total
from Orders o left join Product p on o.product_id = p.product_id
where month(order_date) = 6 or month(order_date)=7
group by customer_id,month(order_date)
) as t1
where total >=100
group by customer_id
having count(*)>=2
)- 已经有灵感了？在评论区写下你的思路吧！
