קבל ספירת חברים כוללת לפי חודש באמצעות Data Explorer

סיכום מבוסס בינה מלאכותית

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? :smiley:

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!