# Ritirando retrospettivamente il numero di utenti ogni mese calendario

**URL:** <https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522>\
**Category:** Data & reporting\
**Created:** [5 Novembre 2024, 1:40am UTC](https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522 "2024-11-05T01:40:38Z")\
**Posts on this page:** 1\
**Showing post:** 2

<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:** [5 Novembre 2024, 1:56am UTC](https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522/2 "2024-11-05T01:56:33Z")

</div>

Questa query dell’esploratore di dati restituirà le visite mensili uniche degli utenti. È quello che desideri?

```sql
--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins

WITH not_allowed_groups AS (
    SELECT gu.user_id FROM group_users gu
    INNER JOIN groups g ON (g.id = gu.group_id)
    WHERE g.name IN (:group_name) 
)

SELECT 
    to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
    date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
    COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
    uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
    AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE

```

---

_[View the full topic](https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522)._