当前位置:网站首页>daily sql - user retention rate for two days
daily sql - user retention rate for two days
2022-08-11 07:13:00 【Eating too much sugar will not gain weight】
Two-day retention rate
Data column:
id device_id quest_id result date
select count(date2) / count(date1) as avg_ret
from
(select distinct d1.device_id, d1.date as date1, d2.date as date2
from question_practice_detail d1
leftjoin(
select distinct device_id, date
from question_practice_detail) d2
on d1.device_id = d2.device_id
and date_add(d1.date, interval 1 days) = d2.date
)a
select avg(if(datediff(date2,date1)= 1,1,0)) as avg_ret
from(
select distinct device_id,date as date1,lead(date) over(partition by device_id orderby date) as date2
from (select distinct device_id,date from question_practice_detail) b
)a
边栏推荐
猜你喜欢
随机推荐
HCIP-生成树(802.1D ,标准生成树/802.1W : RSTP 快速生成树/802.1S : MST 多生成树)
亚马逊获得AMAZON商品详情 API 返回值说明
MySQL导入导出&视图&索引&执行计划
HCIP MGRE\OSPF综合实验
自定义MVC增删改查
ovnif摄像头修改ip
iptables nat
京东商品详情API调用实例讲解
unable to extend table xxx by 1024 in tablespace xxxx
【LeetCode】306.累加数(思路+题解)
利用opencv读取图片,重命名。
姿态解算-陀螺仪+欧拉法
强烈推荐一款好用的API接口
拼多多api接口应用示例
LabelEncoder和LabelBinarizer的区别
ETCD containerized to build a cluster
矩阵分析——微分、积分、极限
查看内核版本和发行版版本
阿里巴巴规范之POJO类中布尔类型的变量都不要加is前缀详解
Conference OA Project My Conference