hivesql之统计每个品牌的优惠总天数

题目需求：

现有各品牌优惠周期表(promotion_info)如下,其记录了每个品牌的每个优惠活动的周期,其中同一品牌的不同优惠活动的周可能会有交叉。

promotion_id	brand	start_date	end_date
1	oppo	2024-06-05	2024-06-09
2	oppo	2024-06-11	2024-06-21
3	xiaomi	2024-06-05	2024-06-15

现要求统计每个品牌的优惠总天数,若某个品牌在同一天有多个优惠活动,则只按一天计算。期望结果如下:

brand (品牌)	promotion_day_count (优惠天数)
huawei	17
oppo	16
redmi	22
xiaomi	22

select brand,
       date_diff(max(end_date), min(start_date)) + 1 - sum(if(no_brand_date > 0, no_brand_date, 0)) promotion_day_count
from (select brand,
             start_date,
             end_date,
             date_diff(start_date,
                       coalesce(max(end_date) over (partition by brand rows between unbounded preceding and 1 preceding)),start_time) -1 as no_brand_date
      from promotion_info
     ) pro
group by brand