# Calculate total participation moments by month using Data Explorer

**URL:** <https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [February 16, 2021, 2:14pm UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885 "2021-02-16T14:14:38Z")\
**Posts on this page:** 1\
**Showing post:** 10

<div class="post-metadata">

**Author:** ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)\
**Post date:** [February 19, 2021, 7:40pm UTC](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885/10 "2021-02-19T19:40:10Z")

</div>

Hi Konrad,

Below is the adjusted query.

```sql

WITH monthly_users AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    WHERE id > 0
    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)
),

monthly_posts AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "posts_count"
	FROM posts p
	WHERE p.deleted_at IS NULL
		AND p.post_type = 1
	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)
),

monthly_active_users AS (
	SELECT
        date_part('year', visited_at) AS year, 
        date_part('month', visited_at) AS month,
		COUNT(DISTINCT user_id) AS "active_users_count"
	FROM user_visits uv
	GROUP BY date_part('year', visited_at), date_part('month', visited_at)
	ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
),

monthly_solutions AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "solutions_count"
	FROM user_actions ua
	WHERE ua.action_type = 15
	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)
),

monthly_likes AS (
	SELECT
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "likes_count"
	FROM user_actions ua
	WHERE ua.action_type = 2
	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
    mu.year,
    mu.month,
    SUM(new_users_month) over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS total_users,
    posts_count,
    COALESCE(active_users_count, 0) AS active_users_count,
    COALESCE(solutions_count, 0) AS solutions_count,
    COALESCE(likes_count, 0) AS solutions_count
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
ORDER BY mu.year, mu.month 

```

#### If you need to add all the columns, to be exactly the same as the image, this is the query:

> **SQL details**
>
> ```sql
> WITH monthly_users AS (
> SELECT 
> date_part('year', created_at) AS year, 
> date_part('month', created_at) AS month,
> COUNT(*) AS new_users_month
> FROM users
> WHERE id > 0
> 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)
> ),
> 
> monthly_posts AS (
> SELECT
> date_part('year', created_at) AS year, 
> date_part('month', created_at) AS month,
> COUNT(*) AS posts_count
> FROM posts p
> WHERE p.deleted_at IS NULL
> AND p.post_type = 1
> 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)
> ),
> 
> monthly_active_users AS (
> SELECT
> date_part('year', visited_at) AS year, 
> date_part('month', visited_at) AS month,
> COUNT(DISTINCT user_id) AS active_users_count
> FROM user_visits uv
> GROUP BY date_part('year', visited_at), date_part('month', visited_at)
> ORDER BY date_part('year', visited_at) ASC, date_part('month', visited_at)
> ),
> 
> monthly_solutions AS (
> SELECT
> date_part('year', created_at) AS year, 
> date_part('month', created_at) AS month,
> COUNT(*) AS solutions_count
> FROM user_actions ua
> WHERE ua.action_type = 15
> 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)
> ),
> 
> monthly_likes AS (
> SELECT
> date_part('year', created_at) AS year, 
> date_part('month', created_at) AS month,
> COUNT(*) AS likes_count
> FROM user_actions ua
> WHERE ua.action_type = 2
> 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
> mu.year,
> mu.month,   
> SUM(new_users_month + COALESCE(posts_count,0) + 
> COALESCE(active_users_count, 0) + 
> COALESCE(solutions_count, 0) + 
> COALESCE(likes_count, 0)) 
> over (ORDER BY mu.year, mu.month rows between unbounded preceding AND current row) AS sum_total
> FROM monthly_users mu
> LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
> LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
> LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
> LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
> ORDER BY mu.year, mu.month 
> 
> ```

---

_[View the full topic](https://meta.discourse.org/t/calculate-total-participation-moments-by-month-using-data-explorer/179885)._
