Add:
users.name AS "Name", after SELECT users.username AS "Username",
3 Likes
Many thanks @SidV!
I also wanted to add the users id (to be able to sort by when the user joined the community ツ
Here’s what my query looks like now, and it’s EXACLTY what I wanted! (note, I added the different “period” params to remind me what they are without having to come back to @jomaxro’s post.
-- https://meta.discourse.org/t/43516/48?u=sidv
-- [params]
-- null int :period
-- 1: all
-- 2: yearly
-- 3: monthly
-- 4: weekly
-- 5: daily
-- 6: quarterly
SELECT users.id AS "User ID",
users.username AS "Username",
users.name AS "Name",
directory_items.likes_received AS "Likes Received",
directory_items.likes_given AS "Likes Given",
directory_items.topic_count AS "Topics Created",
directory_items.post_count AS "Replied",
directory_items.days_visited AS "Vists",
directory_items.topics_entered AS "Viewed",
directory_items.posts_read AS "Read"
FROM users
JOIN directory_items ON users.id = directory_items.user_id
WHERE directory_items.period_type = :period
ORDER BY directory_items.likes_received DESC
Thanks again, y’all!!!
2 Likes
Hot trick: See those “posts”, “users”, “topics”, dropdowns on the right side? If you expand the “users” segment, you can see what’s legal to put in the users.____ slot. It also includes the numeric explanation you’ve put as a comment in your query.
6 Likes
Ahhhhh! I love it! Thanks for the extra instruction. I added “I’d” and hoped it would work and it did… but I’m glad to know the “source” of what will work or not!
The syntax of this query wasn’t too hard, but i don’t understand most of the other ones. Do you know of some SQL basics tutorial or something that would help me understand and know how to write these?
1 Like
In my case I studied SQL, this is PostgreSQL, check this: PostgreSQL: Tutorials & Other Resources
5 Likes
Haven’t seen these cases covered in this topic, so here are my queries:
Number of posts by top-level category and user
For each top-level category, a list of the users who contributed to it and the number of posts they contributed. Posts include both the topic-starter posts and the comments below. You can select to see only one top-level category by editing the query.
/* Sum up codes in sub-level and top-level categories */
SELECT
category_id, username AS "User", SUM(contributions) AS "Post Count"
FROM (
/* Users with topics in top-level categories. */
SELECT
topics.category_id, COUNT(*) AS contributions, users.username
FROM users
INNER JOIN posts ON users.id = posts.user_id
INNER JOIN topics ON posts.topic_id = topics.id
INNER JOIN categories ON topics.category_id = categories.id
WHERE categories.parent_category_id IS NULL
GROUP BY category_id, username
UNION
/* Users with topics in sub-level categories, by top-level category. */
SELECT
categories.parent_category_id AS category_id, COUNT(*) AS contributions, users.username
FROM users
INNER JOIN posts ON users.id = posts.user_id
INNER JOIN topics ON posts.topic_id = topics.id
INNER JOIN categories ON topics.category_id = categories.id
WHERE categories.parent_category_id IS NOT NULL
GROUP BY categories.parent_category_id, username
) AS users_by_category
INNER JOIN categories ON category_id = categories.id
GROUP BY category_id, username
HAVING SUM(contributions) > 5
ORDER BY category_id, "Post Count" DESC
Views by top-level category
Including grand total of all topic views on your forum since the very beginning.
/* Outer SELECT to hide the artificial sort order column, and sort by it. */
SELECT "Category", "Views"
FROM (
/* Views per top-level category. */
SELECT
topcat AS "Category",
SUM(views) AS "Views",
0 AS sortorder /* Artificial sorting column, here to sort to the beginning. */
FROM (
/* Topic views in sub-level categories. */
SELECT
topcat.name AS topcat, SUM(topics.views) AS views
FROM topics
INNER JOIN categories subcat ON topics.category_id = subcat.id
INNER JOIN categories topcat ON subcat.parent_category_id = topcat.id
GROUP BY topcat.name
UNION
/* Topic views in top-level categories (excluding sub-level cats). */
SELECT
topcat.name AS topcat, SUM(topics.views) AS views
FROM topics
INNER JOIN categories topcat ON topics.category_id = topcat.id
WHERE topcat.parent_category_id IS NULL
GROUP BY topcat.name
) AS views_by_cat
GROUP BY topcat
UNION
/* Adding a TOTAL row at the end. */
SELECT
'GRAND TOTAL' AS "Category",
SUM(topics.views) AS "Views",
1 AS sortorder /* Artificial sorting column, here to sort to the end. */
FROM
topics
GROUP BY "Category"
/* Sort the output by either category or views. Enable one of these: */
/* ORDER BY topcat, subcat */
ORDER BY "Views" DESC
) AS views_by_cat_with_total
ORDER BY sortorder, "Category"
Views by sub-level category
A more granular analysis of topic views, but without summing them up. Entries with sub-category “(none)” relate to topics placed directly into the top-level categories.
SELECT
topcat AS "Category",
subcat AS "Subcategory",
SUM(views) AS "Subcategory views"
FROM (
/* Topic views in sub-level categories. */
SELECT
topcat.name AS topcat, subcat.name AS subcat, SUM(topics.views) AS views
FROM topics
INNER JOIN categories subcat ON topics.category_id = subcat.id
INNER JOIN categories topcat ON subcat.parent_category_id = topcat.id
GROUP BY topcat.name, subcat.name
UNION
/* Topic views in top-level categories (excluding sub-level cats). */
SELECT
topcat.name AS topcat, '(none)', SUM(topics.views) AS views
FROM topics
INNER JOIN categories topcat ON topics.category_id = topcat.id
WHERE topcat.parent_category_id IS NULL
GROUP BY topcat.name
) AS views_by_cat
GROUP BY topcat, subcat
/* Sort the output by either category or views. Enable one of these: */
/* ORDER BY topcat, subcat */
ORDER BY "Subcategory views" DESC
6 Likes
@tanius Those look great! How would you go about adding data parameters (start and end) to run those queries for specific date ranges? It would be useful to track views and post numbers for top-level categories each month.
2 Likes
How about a query that shows how many activity summary/digest emails were sent out in the last X weeks?
3 Likes
This query is very helpful, does anyone know how I can tweak it to add the date the the user was created too?
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:
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
7 Likes
Please READ before posting here:
Open new topic in #plugin:data-explorer section 
3 Likes
I’m going to close this topic for now. It’s a great topic, but it’s too difficult to search for specific queries. If you have questions about how to write a Data Explorer query, create a new #support topic and tag it with data-explorer.
11 Likes