# 仪表盘报告 - DAU/MAU

**URL:** https://meta.discourse.org/t/dashboard-report-dau-mau/289383
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [2023年十二月20日 22:44 UTC](https://meta.discourse.org/t/dashboard-report-dau-mau/289383 "2023-12-20T22:44:15Z")
**Posts on this page:** 1
**Showing post:** 3

<div class="post-metadata">

### Author: ![NiceOldGuy](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/niceoldguy/32/326207_2.png) [@NiceOldGuy](https://meta.discourse.org/u/NiceOldGuy)
#### Post date: [2025年二月2日 20:44 UTC](https://meta.discourse.org/t/dashboard-report-dau-mau/289383/3 "2025-02-02T20:44:32Z")

</div>

谢谢，@SaraDev，这对我们很有帮助。由于我们是一个小型社区，工作人员比普通用户活跃得多，我（以及 ChatGPT）修改了查询，以便能够分别或合并运行普通用户和工作人员用户。而且我喜欢看到它被图形化，所以进行了修改以启用它。  
我最近在 @tyler.lamparter 的[这个技巧](https://meta.discourse.org/t/is-there-a-document-introducing-the-database-schema-design/171729/4?u=niceoldguy)中看到了如何下载数据库模式，并在这些会话开始时将其上传到 ChatGPT；这使得更容易要求 chatty 分开 `staff`，例如。

```plaintext
-- [params]
-- int :months_ago = 3
-- int :include_staff = 1

WITH staff_users AS (
  SELECT user_id FROM group_users WHERE group_id = 3
),
daily_users AS (
  SELECT
    date_trunc('day', visited_at)::DATE AS day,
    COUNT(DISTINCT user_id) AS dau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY day
),
monthly_users AS (
  SELECT
    date_trunc('month', visited_at)::DATE AS month,
    COUNT(DISTINCT user_id) AS mau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date, -- ✅ X-axis for graphing
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio -- ✅ Y-axis for graphing
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Non-Staff Only, 1 = All Users, 2 = Staff Only

```

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-dau-mau/289383)._