SQL面试题练习 —— 查询每个产品每年总销售额

1 题目

已知有表 t_product_sales 如下，记录了每个产品id、产品名称、产品销售开始日期、产品销售结束日期以及产品日均销售金额，请计算出每个产品每年的销售金额。

样例数据

+-------------+---------------+----------------------+----------------------+----------------------+
| product_id  | product_name  |     period_start     |      period_end      | average_daily_sales  |
+-------------+---------------+----------------------+----------------------+----------------------+
| 1           | LC Phone      | 2019-01-25 00:00:00  | 2019-02-28 00:00:00  | 100                  |
| 2           | LC T-Shirt    | 2018-12-01 00:00:00  | 2020-01-01 00:00:00  | 10                   |
| 3           | LC Keychain   | 2019-12-01 00:00:00  | 2020-01-31 00:00:00  | 1                    |
+-------------+---------------+----------------------+----------------------+----------------------+

期望结果

+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1          | LC Phone     |    2019     | 3500         |
| 2          | LC T-Shirt   |    2018     | 310          |
| 2          | LC T-Shirt   |    2019     | 3650         |
| 2          | LC T-Shirt   |    2020     | 10           |
| 3          | LC Keychain  |    2019     | 31           |
| 3          | LC Keychain  |    2020     | 31           |
+------------+--------------+-------------+--------------+

2 建表语句

--建表语句
create table if not exists t_product_sales
(
    product_id          bigint,
    product_name        string,
    period_start        string,
    period_end          string,
    average_daily_sales bigint
)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
    STORED AS orc;

--插入数据

insert into t_product_sales(product_id, product_name, period_start, period_end, average_daily_sales)
values (1, 'LC Phone', '2019-01-25 00:00:00', '2019-02-28 00:00:00', 100)