leetcode 1511 消费者下单频率(postgresql)

需求

表: Customers

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| customer_id | int |
| name | varchar |
| country | varchar |
±--------------±--------+
customer_id 是该表主键.
该表包含公司消费者的信息.

表: Product

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| product_id | int |
| description | varchar |
| price | int |
±--------------±--------+
product_id 是该表主键.
该表包含公司产品的信息.
price 是本产品的花销.

表: 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 查询，报告在 2020 年 6 月和 7 月 每个月至少花费 $100 的客户的 customer_id 和 customer_name 。

以任意顺序返回结果表.

查询结果格式如下例所示。

示例 1:

输入：
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 |
±-------------±------------±------------±------------±----------+

输出：
±-------------±-----------+
| 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.

输入

输出

with t1 as (select o.order_id,
                   o.customer_id,
                   o.product_id,
                   to_char(order_date, 'YYYY-MM') as order_month,
                   c.name,
                   quantity
            from orders o,
                 customers c
            where o.customer_id = c.customer_id
              and o.order_date >= '2020-06-01'
              and o.order_date < '2024-08-01'),
     t2 as (select order_id, customer_id, p.product_id, order_month, name, quantity * p.price as total_price
            from t1,
                 product p
            where t1.product_id = p.product_id),
     t3 as (select customer_id, name, order_month, sum(total_price) as month_price
            from t2
            group by name, order_month, customer_id
            having sum(total_price) >= 100
            order by name)
select customer_id, name
from t3
group by customer_id, name
having count(1) > 1