每个日期所有用户的第一天退货率

Question

我在一次面试中遇到了这个问题，我想验证一下这种方法。

编写一条SQL语句，计算每个日期所有用户的第一天返回率。"；第1天返回率"；是%在第n天看到的不同用户，也在第(n+1(天看到。我们希望每天跟踪用户退货率。

例如，对于上面的样本数据：-

1. 2021-09-01的D1RR为50%
2. 2021-09-02年D1RR为0%

*/

我的想法是统计第一天登录的不同用户。user_id，min(日期(，然后继续计算其他日期。

CREATE TABLE bi_staging.sessions (
session_ts TIMESTAMP,
user_id string,
app_name string
)
INSERT INTO bi_staging.sessions(session_ts, user_id, app_name)
VALUES
('2021-09-01 06:00:00', '123', 'ios'),
('2021-09-01 12:30:00', '123', 'ios'),
('2021-09-02 06:00:00', '123', 'ios'),
('2021-09-01 06:00:00', 'abc', 'ios'),
('2021-09-03 06:00:00', 'abc', 'ios');

Answer 1

一个简单的解决方案可以是如下所示：

注意：我使用了datetime而不是timestamp来简化我的测试。在该解决方案中，需要一种将timestamp转换为date的方法

DECLARE @temp TABLE (session_ts_date DATE, user_id Varchar(10))

INSERT INTO @temp(session_ts_date, user_id)
SELECT DISTINCT CAST(session_ts AS DATE),
user_id
FROM @sessions

SELECT s.session_ts_date,
CAST(SUM( CASE WHEN t.user_id IS NULL THEN 0 ELSE 1 END) as DECIMAL)/ CAST(count(s.user_id) AS DECIMAL)
FROM @temp s
LEFT JOIN @temp t on s.session_ts_date = DATEADD(day,-1,t.session_ts_date) and s.user_id = t.user_id
GROUP BY s.session_ts_date