Get member count overall by month using Data Explorer

data & reporting
1

Hey there everyone!

Has anyone ever written a Data Explorer query for fetching members count overall by month giving a result like this?

1

2

Any Data Explorer geniuses in here? :smiley:

4

I guess you mean something like this?

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)
2 Likes
5

Pretty close but it’s giving me the number of users created each month, what I meant was the. overall number of users that our platform have in specific month. So let’s say in March it was 1000 and in April we have 20 new so it will be 1020 for April.

1 Like
7

I hope it helps.

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
4 Likes
10

Perfectly thanks for help!

2 Likes
11

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.