小红书大数据面试SQL-品牌营销活动天数

数据仓库晨曦

发布于 2024-05-18 09:18:45

750

发布于 2024-05-18 09:18:45

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目

有营销活动记录表，记录了每个品牌每次营销活动的开始日期和营销活动的结束日期，现需要统计出每个品牌的总营销天数。

注意： 1:苹果第一行数据的营销结束日期比第二行数据的营销开始日期要晚，这部分有重叠的日期的要去重计算。

2:苹果第二行数据的营销结束日期和第三行的开始日期不连续，2019-09-07以及2019-09-08不统计到营销天数中。 样例数据

+--------+-------------+-------------+
| brand  | start_date  |  end_date   |
+--------+-------------+-------------+
| 华为   | 2018-08-04  | 2018-08-05  |
| 华为   | 2018-08-04  | 2020-12-25  |
| 小米   | 2018-08-15  | 2018-08-20  |
| 小米   | 2020-01-01  | 2020-01-05  |
| 苹果   | 2018-09-01  | 2018-09-05  |
| 苹果   | 2018-09-03  | 2018-09-06  |
| 苹果   | 2018-09-09  | 2018-09-15  |
+--------+-------------+-------------+

结果

+--------+-----------+
| brand  | act_days  |
+--------+-----------+
| 华为     | 875       |
| 小米     | 11        |
| 苹果     | 13        |
+--------+-----------+

二、分析

本题难点在解决交叉问题，但是题目给出的是开始日期和结束日期，我们根据开始和结束日期，使用生成函数，生成活动期间每天的记录，然后根据品牌分组，对日期进行去重即可

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

1.生成每次活动每天的记录

select
    brand,
    start_date,
    end_date,
    t.pos,
    t.value,
    date_add(start_date,t.pos) as act_date
from t_marketing_act
lateral view posexplode(split(space(datediff(end_date,start_date)),'')) t as pos,value；

执行结果 这里由于数据太多，我们仅展示“苹果数据”

+--------+-------------+-------------+--------+----------+-------------+
| brand  | start_date  |  end_date   | t.pos  | t.value  |  act_date   |
+--------+-------------+-------------+--------+----------+-------------+
| 苹果     | 2018-09-01  | 2018-09-05  | 0      |          | 2018-09-01  |
| 苹果     | 2018-09-01  | 2018-09-05  | 1      |          | 2018-09-02  |
| 苹果     | 2018-09-01  | 2018-09-05  | 2      |          | 2018-09-03  |
| 苹果     | 2018-09-01  | 2018-09-05  | 3      |          | 2018-09-04  |
| 苹果     | 2018-09-01  | 2018-09-05  | 4      |          | 2018-09-05  |
| 苹果     | 2018-09-03  | 2018-09-06  | 0      |          | 2018-09-03  |
| 苹果     | 2018-09-03  | 2018-09-06  | 1      |          | 2018-09-04  |
| 苹果     | 2018-09-03  | 2018-09-06  | 2      |          | 2018-09-05  |
| 苹果     | 2018-09-03  | 2018-09-06  | 3      |          | 2018-09-06  |
| 苹果     | 2018-09-09  | 2018-09-15  | 0      |          | 2018-09-09  |
| 苹果     | 2018-09-09  | 2018-09-15  | 1      |          | 2018-09-10  |
| 苹果     | 2018-09-09  | 2018-09-15  | 2      |          | 2018-09-11  |
| 苹果     | 2018-09-09  | 2018-09-15  | 3      |          | 2018-09-12  |
| 苹果     | 2018-09-09  | 2018-09-15  | 4      |          | 2018-09-13  |
| 苹果     | 2018-09-09  | 2018-09-15  | 5      |          | 2018-09-14  |
| 苹果     | 2018-09-09  | 2018-09-15  | 6      |          | 2018-09-15  |
+--------+-------------+-------------+--------+----------+-------------+

2.根据品牌分组，act_date进行去重统计

with t as (
select
    brand,
    start_date,
    end_date,
    t.pos,
    t.value,
    date_add(start_date, t.pos) as act_date
    from t_marketing_act
    lateral view posexplode(split(space(datediff(end_date, start_date)), '')) t as pos, value)
select
    brand,
    count(distinct act_date) as act_days
    from t
group by brand；

执行结果

+--------+-----------+
| brand  | act_days  |
+--------+-----------+
| 华为     | 875       |
| 小米     | 11        |
| 苹果     | 13        |
+--------+-----------+

四、建表语句和数据插入

CREATE TABLE IF NOT EXISTS t_marketing_act (
    brand STRING, --品牌
    start_date STRING, -- 营销活动开始日期
    end_date STRING -- 营销活动结束日期
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC;

insert into t_marketing_act(brand, start_date, end_date) values
('华为','2018-08-04','2018-08-05'),
('华为','2018-08-04','2020-12-25'),
('小米','2018-08-15','2018-08-20'),
('小米','2020-01-01','2020-01-05'),
('苹果','2018-09-01','2018-09-05'),
('苹果','2018-09-03','2018-09-06'),
('苹果','2018-09-09','2018-09-15');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-05-13，如有侵权请联系 cloudcommunity@tencent.com 删除

sql