Hey there everyone!
Has anyone ever written a Data Explorer query for fetching members count overall by month giving a result like this?
The discussion revolves around creating a Data Explorer query to calculate the overall number of members on a platform by month, rather than just new users each month. Konrad_Sopala initially sought a solution to show cumulative member counts, such as 1000 in March and 1020 in April after adding 20 new users. sam provided a query for counting new users per month, which was close but not exactly what was needed. michebs offered a more accurate solution using a Common Table Expression (CTE) and a window function to compute the cumulative total of users over time. The final query successfully returns the year, month, new users, and the running total, which Konrad_Sopala confirmed as perfect.
Hey there everyone!
Has anyone ever written a Data Explorer query for fetching members count overall by month giving a result like this?
Any Data Explorer geniuses in here? ![]()
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)
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.
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 |
Perfectly thanks for help!