通过 Data Explorer 获取每月会员总数

社区 数据与报告
1

大家好！

是否有人编写过 Data Explorer 查询，用于按月统计成员总数，并得到如下结果？

1
1690×292 5.43 KB

2

这里有 Data Explorer 的天才吗？:blush:

4

我想你指的是类似这样的内容？

select date_part('year', created_at) as year, 
date_part('month', created_at) as month,
count(*) as "count"
from users
group by date_part('year', created_at), date_part('month', created_at)
order by date_part('year', created_at) asc,
 date_part('month', created_at)
5

非常接近了，但当前显示的是每月新增用户数，而我想要的是平台在特定月份的用户总数。例如，3 月是 1000 人，4 月新增 20 人，那么 4 月的总数应为 1020 人。

7

希望这能帮到你。

WITH data_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
)

SELECT
  year, 
  month, 
  new_users_month,
  SUM(new_users_month) over (ORDER BY year, month rows between unbounded preceding AND current row) AS total
FROM data_month ORDER BY year, month
year month new_users_month total
2020 2 50 50
2020 3 100 150
2020 4 50 200
10

太好了，谢谢您的帮助！