软件交付问题之要在需求评审中高效决策，如何解决

SQL - 17
with visit_log as (
select stack (
6,
'2024-01-01', '101', '湖北', '武汉', 'Android',
'2024-01-01', '102', '湖南', '长沙', 'IOS',
'2024-01-01', '103', '四川', '成都', 'Windows',
'2024-01-02', '101', '湖北', '孝感', 'Mac',
'2024-01-02', '102', '湖南', '邵阳', 'Android',
'2024-01-03', '101', '湖北', '武汉', 'IOS'
) 
-- 字段：日期，用户，省份，城市，设备类型
as (dt, user_id, province, city, device_type)
)
select * from visit_log;

-- SQL - 18
select user_id
from (
select 
*,
lag(dt, 2 - 1) over (partition by user_id order by dt) as lag_dt
from (select dt, user_id from visit_log group by dt, user_id) t0
) t1
where datediff(dt, lag_dt) + 1 = 2
group by user_id;

SQL - 17
with visit_log as (
select stack (
6,
'2024-01-01', '101', '湖北', '武汉', 'Android',
'2024-01-01', '102', '湖南', '长沙', 'IOS',
'2024-01-01', '103', '四川', '成都', 'Windows',
'2024-01-02', '101', '湖北', '孝感', 'Mac',
'2024-01-02', '102', '湖南', '邵阳', 'Android',
'2024-01-03', '101', '湖北', '武汉', 'IOS'
) 
-- 字段：日期，用户，省份，城市，设备类型
as (dt, user_id, province, city, device_type)
)
select * from visit_log;

-- SQL - 19
select user_id
from (
select *, 
dense_rank() over (partition by user_id order by dt) as dr
from visit_log
) t1
where datediff(dt, date_add(dt, 1 - dr)) + 1 = 2
group by user_id;

SQL - 17
with visit_log as (
select stack (
6,
'2024-01-01', '101', '湖北', '武汉', 'Android',
'2024-01-01', '102', '湖南', '长沙', 'IOS',
'2024-01-01', '103', '四川', '成都', 'Windows',
'2024-01-02', '101', '湖北', '孝感', 'Mac',
'2024-01-02', '102', '湖南', '邵阳', 'Android',
'2024-01-03', '101', '湖北', '武汉', 'IOS'
) 
-- 字段：日期，用户，省份，城市，设备类型
as (dt, user_id, province, city, device_type)
)
select * from visit_log;

-- SQL - 20
select user_id
from (
select 
*,
-- 连续活跃天数
count(distinct dt) 
over (partition by user_id, cont) as cont_days
from (
select 
*, 
date_add(dt, 1 - dense_rank() 
over (partition by user_id order by dt)) as cont
from visit_log
) t1
) t2
where cont_days >= 2
group by user_id;