Queste non sono molto “fighe” — sono solo repliche di alcune statistiche dalla dashboard dei report di amministrazione — ma le ho trovate utili per fornire l’accesso agli utenti non amministratori:
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 -- crawler
or t.req_type = 7 -- utenti connessi
or t.req_type = 8 -- anonimi
group by t.date
) as e on a.date = e.date
order by date desc