# How to measure active users?

**URL:** https://meta.discourse.org/t/how-to-measure-active-users/74574
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [November 22, 2017, 9:39am UTC](https://meta.discourse.org/t/how-to-measure-active-users/74574 "2017-11-22T09:39:55Z")
**Posts on this page:** 1
**Showing post:** 5

<div class="post-metadata">

### Author: ![HAWK](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/hawk/32/86627_2.png) [@HAWK](https://meta.discourse.org/u/HAWK)
#### Post date: [November 22, 2017, 5:03pm UTC](https://meta.discourse.org/t/how-to-measure-active-users/74574/5 "2017-11-22T17:03:01Z")

</div>

These queries will help:

### Top 50 posters

Returns the top 50 posters for a given monthly period. Results are ordered by post\_count. It accepts a ‘months\_ago’ parameter, defaults to 1 to give results for the most recently completed calendar month.

```sql
-- [params]
-- int :months_ago = 1

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
),

user_posts_in_period AS (
SELECT
p.user_id
FROM posts p
INNER JOIN query_period qp
ON p.created_at >= qp.period_start
AND p.created_at <= qp.period_end
WHERE p.user_id > 0
)

SELECT
up.user_id,
count(1) as post_count
FROM user_posts_in_period up
GROUP BY up.user_id
ORDER BY post_count DESC
LIMIT 50

```

### Top 50 likers

Returns the top 50 likers for a given monthly period. Results are ordered by like\_count. It accepts a ‘months\_ago’ parameter, defaults to 1 to give results for the most recently completed calendar month.

```sql
-- [params]
-- int :months_ago = 1

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)

SELECT
ua.user_id,
count(1) AS like_count
FROM user_actions ua
INNER JOIN query_period qp
ON ua.created_at >= qp.period_start
AND ua.created_at <= qp.period_end
WHERE ua.action_type = 1
GROUP BY ua.user_id
ORDER BY like_count DESC
LIMIT 50
```

---

_[View the full topic](https://meta.discourse.org/t/how-to-measure-active-users/74574)._