题目一：计算平台的每一个用户发过多少朋友圈、获得多少点赞

已知，数据如下：

T1：10万行数据

uid(用户id)

log_id(日记id)

uid1

log_id1

uid1

log_id2

uid2

log_id3

…

…

T2：1000万行数据（注：没有被点赞的日记此表不做记录）

log_id(日记id)

like_uid(点赞的用户id)

log_id1

uid2

log_id1

uid3

log_id1

uid4

log_id3

uid2

…

…

需求：请用sql计算出如下结果：

uid(用户id)

发过多少日记

获得多少点赞

uid1

2

3

uid2

1

1

…

…

…

题目二：处理产品版本号

版本号信息存储在数据表中，每行一个版本号。

版本号命名规则符合正则表达式：(?:[1-9]\d|[1-9])(.(?:[1-9]\d|\d)){1,2}

补充说明：

产品版本号由三个部分组成

如：9.11.2

第一部分9为主版本号，为1-99之间的数字；

第二部分11为子版本号，为0-99之间的数字；

第三部分2为阶段版本号，为0-99之间的数字（可选）；

请根据具体条件和问题，使用hive sql编程：

1.条件：T1表有100个版本号，找出其中最大的版本号。

举例：

T1：

v_id (版本号)

9.9.2

8.1

9.9.2

9.20

31.0.1

…

2.条件：T1表有1000万个版本号，给如下格式的所有版本号排序。对于相同的版本号，顺序号一致。

table_result （排序结果表） :

v_id (版本号)

seq (顺序号)

31.0.1

0

9.20

1

9.9.2

2

9.9.2

2

9.0.8

4

…

…

题目三、计算用户留存

用户留存是体现平台健康程度的重要数据指标之一，请用hive sql完成如下需求：

已知：

用户信息表结构：

CREATE TABLE if NOT EXISTS tb_user (
     user_id BIGINT COMMENT "用户id",
     create_time STRING COMMENT "注册时间,如：2019-01-01 08:00:00"
)
COMMENT "用户信息表";

访问日志表结构

CREATE TABLE if NOT EXISTS tb_access_log (
     user_id BIGINT COMMENT "用户id",
     page_url STRING COMMENT "网站页面路径,用于标记不同页面",
     create_time STRING COMMENT "访问时间,如：2019-01-01 08:00:01"
)
COMMENT "用户访问日志表"
PARTITIONED by (dt STRING COMMENT "格式：yyyymmdd ,数值与create_time转化后的日期一致";

tb_user : 总量大约200万行

user_id

create_time

123456

2018-09-01 08:00:01

123457

2019-05-24 09:21:42

123458

2019-08-04 21:02:12

…

…

tb_access_log ：每天新增访问记录约8000万行

user_id

page_url

create_time

dt

123456

index

2018-09-01 08:08:32

20180901

123457

hot

2019-05-24 09:21:46

20190524

123458

mine

2019-08-04 22:01:21

20190804

…

…

…

…

定义：

  • 新用户：某一日新注册的用户
  • 新用户次1日留存：T日成为新用户，T+1日回访平台网站任意页面（T+1即T日期的第二天）
  • 新用户次3日留存：T日成为新用户，T+3日回访平台网站任意页面
  • 新用户次n日留存：T日成为新用户，T+n日回访平台网站任意页面

1.假设平台元旦节做了一次拉新活动，当日新增用户数约为平日的两倍，2019年1月9日，距离活动已经过去一周了**，公司希望评估一下2019年1月1日当天的全部新增用户，在接下来一周的留存情况****(即1月2日-1月8日每天的留存用户数)，要求交付的数据结果表如下，请用一个简洁的sql****计算出如下数据： **

新用户数

次1日留存用户数

次2日留存用户数

次3日留存用户数

次4日留存用户数

次5日留存用户数

次6日留存用户数

次7日留存用户数

dt（成为新用户日期）

61132

5054

4284

3492

3209

3202

2929

2116

20190101

2.本次活动拉新留存情况符合预期，公司决定后续会不定期举办拉新活动，以获得更多新用户，如何设计调度任务，使得报表按天增量更新如下，帮助公司长期监控平台新用户的留存，希望保留全量历史数据，方便随时对比各期活动情况？（请给出完整的相关sql，包括建表语句，写入数据过程等）

（以下数据范例最近更新日期为：20190109）

表名：reporting_new_user_retain_day

新用户数

次1日留存用户数

次2日留存用户数

次3日留存用户数

次4日留存用户数

次5日留存用户数

次6日留存用户数

次7日留存用户数

dt（成为新用户日期，格式yyyymmdd）

33492

null

null

null

null

null

null

null

20190108

33679

3452

null

null

null

null

null

null

20190107

34657

3253

2662

null

null

null

null

null

20190106

35264

3582

2865

1627

null

null

null

null

20190105

33266

3281

2660

1619

1387

null

null

null

20190104

37125

3620

2761

1897

1578

1372

null

null

20190103

31206

3121

2465

1597

1078

995

991

null

20190102

61132

5054

4284

3492

3209

3202

2929

2816

20190101

30206

3021

2165

1397

971

895

873

867

20181231

…

…

…

…

…

…

…

…

…

温馨提示：答题结果建议保存为sql文件或者pdf格式，避免代码排版错乱等问题，谢谢！

SELECT DATE_FORMAT(t1.create_time, "yyyy-MM-dd"),
       count(t1.user_id),
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 1 THEN t1.USER_id
             END) ,
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 2 THEN t1.USER_id
             END),
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 3 THEN t1.USER_id
             END),
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 4 THEN t1.USER_id
             END)
FROM tb_user t1
LEFT JOIN （
SELECT user_id,
       DATE_FORMAT(create_time, "yyyy-MM-dd") AS create_time
FROM tb_access_log
GROUP BY user_id,
         DATE_FORMAT(create_time, "yyyy-MM-dd") ) t2 ON t1.user_id = t2.user_id
GROUP BY DATE_FORMAT(t1.create_time, "yyyy-MM-dd")

考虑到数据量大的问题，只算近七天的数据，之前的数据算完之后保存为历史，近7天增量更新