Ces éléments ne sont pas très « cool » — il ne s’agit que de reproductions de certaines statistiques du tableau de bord des rapports d’administration — mais je les ai trouvés utiles pour fournir un accès aux utilisateurs non administrateurs :
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 -- robots d'indexation
or t.req_type = 7 -- connectés
or t.req_type = 8 -- anonymes
group by t.date
) as e on a.date = e.date
order by date desc