SQL面试题：求品牌活动天数

题目

求各品牌活动天数，活动之间时间会有交叉重叠，重叠部分只算一次

表和数据

DROP TABLE IF EXISTS test_promotion_info

CREATE EXTERNAL TABLE test_promotion_info
(
    promotion_id STRING COMMENT '优惠活动id',
    brand        STRING COMMENT '优惠品牌',
    start_date   STRING COMMENT '优化活动开始日期',
    end_date     STRING COMMENT '优惠活动结束日期'
) COMMENT '各品牌活动周期表';

INSERT OVERWRITE TABLE test_promotion_info VALUES
(1, 'oppo', '2023-06-05', '2023-06-09'),
(2, 'oppo', '2023-06-11', '2023-06-21'),
(3, 'vivo', '2023-06-05', '2023-06-15'),
(4, 'vivo', '2023-06-09', '2023-06-21'),
(5, 'redmi', '2023-06-05', '2023-06-21'),
(6, 'redmi', '2023-06-09', '2023-06-15'),
(7, 'redmi', '2023-06-17', '2023-06-26'),
(8, 'huawei', '2023-06-05', '2023-06-26'),
(9, 'huawei', '2023-06-09', '2023-06-15'),
(10, 'huawei', '2023-06-17', '2023-06-21');

解题思路

1. 获取当前历史活动中的最大的结束日期（截止当前活动起始日期的前一天）current_max_date
2. 处理交叉日期：
   1. current_max_date为空，说明该品牌是第一次进行活动
   2. current_max_date < start_date，说明该品牌不存在交叉
   3. current_max_date >  start_date，说明该品牌存在交叉，需要对该时间进行调整，start_date = current_max_date + 1
3. 对brand进行 end_date - start_date + 1 求和

详细步骤

WITH temp1 AS (
SELECT
    promotion_id,
    brand,
    start_date,
    end_date,
    max(end_date) over (partition by brand order by start_date rows between unbounded preceding and 1 preceding) as current_max_date
FROM
    test_promotion_info
), temp2 AS (
SELECT
    promotion_id,
    brand,
    start_date,
    end_date,
    IF(
        current_max_date is null or current_max_date < start_date, start_date, date_add(current_max_date, 1)
    )  as cal_start_date
FROM
    temp1
)

SELECT 
    brand,
    sum(datediff(end_date, cal_start_date) + 1)  AS days
FROM 
    temp2
WHERE 
    end_date > cal_start_date
GROUP BY 
    brand
;

 最终结果

+---------+-------+
|  brand  | days  |
+---------+-------+
| oppo    | 16    |
| vivo    | 17    |
| huawei  | 22    |
| redmi   | 22    |
+---------+-------+

数据验证

select
    promotion_id,
    brand,
    start_date,
    end_date,
    cal_start_date,
    IF(end_date > cal_start_date, datediff(end_date, cal_start_date) + 1, 0) as days
from
    temp2 a;

+---------------+---------+-------------+-------------+-----------------+-------+
| promotion_id  |  brand  | start_date  |  end_date   | cal_start_date  | days  |
+---------------+---------+-------------+-------------+-----------------+-------+
| 1             | oppo    | 2023-06-05  | 2023-06-09  | 2023-06-05      | 5     |
| 2             | oppo    | 2023-06-11  | 2023-06-21  | 2023-06-11      | 11    |
| 3             | vivo    | 2023-06-05  | 2023-06-15  | 2023-06-05      | 11    |
| 4             | vivo    | 2023-06-09  | 2023-06-21  | 2023-06-16      | 6     |
| 8             | huawei  | 2023-06-05  | 2023-06-26  | 2023-06-05      | 22    |
| 9             | huawei  | 2023-06-09  | 2023-06-15  | 2023-06-27      | 0     |
| 10            | huawei  | 2023-06-17  | 2023-06-21  | 2023-06-27      | 0     |
| 5             | redmi   | 2023-06-05  | 2023-06-21  | 2023-06-05      | 17    |
| 6             | redmi   | 2023-06-09  | 2023-06-15  | 2023-06-22      | 0     |
| 7             | redmi   | 2023-06-17  | 2023-06-26  | 2023-06-22      | 5     |
+---------------+---------+-------------+-------------+-----------------+-------+