# 如何用SQL做留存率分析

APP分析中经常用到AARRR模型（海盗模型）用来分析APP的现状，其中一个重要节点就是提高留存（Acquisition），而留存率这个指标在这个阶段可以说是核心指标也不为过。那如何用SQL计算留存率呢？

select
user_id,
from user_info
group by 1,2;

SELECT
b.user_id,
c.first_day
FROM
(select
user_id,
from user_info
group by 1,2) b
LEFT JOIN
(SELECT —找到user_id对应的最早登录时间，然后匹配带登录时间的user_id
user_id,
FROM
(select
user_id,
from user_info
group by 1,2) a
group by 1) c
on b.user_id = c.user_id
order by 1,2;

SELECT
user_id,
first_day,
FROM
(SELECT
b.user_id,
c.first_day
FROM
(SELECT
user_id,
FROM user_info
GROUP BY 1,2) b
LEFT JOIN
(SELECT
user_id,
FROM
(select
user_id,
from user_info
group by 1,2) a
group by 1) c
on b.user_id = c.user_id
order by 1,2) e
order by 1,2

SELECT
first_day,
sum(case when by_day = 0 then 1 else 0 end) day_0,
sum(case when by_day = 1 then 1 else 0 end) day_1,
sum(case when by_day = 2 then 1 else 0 end) day_2,
sum(case when by_day = 3 then 1 else 0 end) day_3,
sum(case when by_day = 4 then 1 else 0 end) day_4,
sum(case when by_day = 5 then 1 else 0 end) day_5,
sum(case when by_day = 6 then 1 else 0 end) day_6,
sum(case when by_day >= 7 then 1 else 0 end) day_7plus
FROM
(SELECT
user_id,
first_day,
FROM
(SELECT
b.user_id,
c.first_day
FROM
(SELECT
user_id,
FROM user_info
GROUP BY 1,2) b
LEFT JOIN
(SELECT
user_id,
FROM
(select
user_id,
FROM
user_info
group by 1,2) a
group by 1) c
on b.user_id = c.user_id
order by 1,2) e
order by 1,2) f
group by 1
order by 1

08-30 1万+

06-23 925
02-02 2万+
07-21 1372
05-31 1万+
12-09 276
11-13 614
09-10 200
06-05 1215
07-27 1314
04-11