# Replicated dashboard stats

**URL:** https://meta.discourse.org/t/replicated-dashboard-stats/275169
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports
**Created:** [May 11, 2020, 4:15pm UTC](https://meta.discourse.org/t/replicated-dashboard-stats/275169 "2020-05-11T16:15:18Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![mbauman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mbauman/32/139835_2.png) [@mbauman](https://meta.discourse.org/u/mbauman)
#### Post date: [May 11, 2020, 4:15pm UTC](https://meta.discourse.org/t/replicated-dashboard-stats/275169/1 "2020-05-11T16:15:18Z")

</div>

These aren’t very “cool” — they’re just replications of some of stats from the admin report dashboard — but I’ve found them helpful to provide access to non-admin users:

```sql
SELECT
    a.date,
    a.users,
    b.posts,
    c.topics,
    d.likes,
    e.pageviews
FROM
(
-- /admin/reports/signups
SELECT DATE(u.created_at), count(*) as users
  from users as u
    group by DATE(u.created_at)
) as a
LEFT JOIN
(
-- /admin/reports/posts
SELECT DATE(p.created_at), count(*) as posts
  from posts as p
  join topics as t on t.id = p.topic_id
    where p.deleted_at is null and
          p.post_type = 1 and
          t.archetype <> 'private_message'
    group by DATE(p.created_at)
) as b on a.date = b.date
LEFT JOIN
(
-- /admin/reports/topics
SELECT DATE(t.created_at), count(*) as topics
  from topics as t
    where t.archetype <> 'private_message' and
          t.deleted_at is null
    group by DATE(t.created_at)
) as c on a.date = c.date
LEFT JOIN
(
-- /admin/reports/likes
SELECT DATE(pa.created_at), count(*) as likes
  from post_actions as pa
    where pa.post_action_type_id = 2
    group by DATE(pa.created_at)
) as d on a.date = d.date
LEFT JOIN
(
-- /admin/reports/page_view_total_reqs
SELECT t.date, sum(t.count) as pageviews
  from application_requests as t
    where t.req_type = 6 -- crawlers
        or t.req_type = 7 -- logged in
        or t.req_type = 8 -- anonymous
    group by t.date
) as e on a.date = e.date
order by date desc

```