What cool data explorer queries have you come up with?

Would it be possible to save the queries under given names?

Category lurkers:

See for a category (or all categories by default) the users’ notification level.
rationale: we use categories for “working groups” that everyone can “join” (track or watch). Therefore, a working group member is someone that has a notification level set for this category, and this query lists the members

-- [params]
-- null int :category 

select category_id, cu.user_id,
CASE notification_level 
  WHEN 1 THEN 'regular'
  WHEN 2 THEN 'tracking'
  WHEN 3 THEN 'watching'
  WHEN 4 THEN 'watching_first_post'
END as level

from category_users cu
join categories on category_id=categories.id
where (:category is null OR category_id= :category)
order by category_id, notification_level desc
3 Likes

I agree! This data explorer is blowing my mind. So impressive!

I wanted to import @jomaxro’s User’s Directory but can’t figure out how to add this SQL query.

In the OP, of the Data Explorer Plugin thread, @riking shows how to add his .json file to import a plugin, but how do we grab one of the SQL queries from your GIT repository, and add it to the Data Explorer?

Any chance you could tell me step-by-step what I need to do, here?

3 Likes

For the bare .sql files, simply copy & paste the entire content into the editor.

5 Likes

@riking or @jomaxro - is there a way to add the “name” to this query instead of just the “username”?

This is for the User Directory Query:

-- https://meta.discourse.org/t/43516/48?u=sidv

-- [params]
-- null int :period

SELECT users.username AS "Username",
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
3 Likes

Thank you @riking!

For any other newbs out there:

  1. Visit the Data Explorer link, within the Plugins menu and then click the " + " button
  2. Add a title for your data explorer query
  3. Add a description for your data explorer querty
  4. After finding and copying a data explorer query to your clipboard, paste it here
  5. Save it!

5 Likes

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.

image image

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 :pray:

3 Likes

2 posts were split to a new topic: User badge counts with badge name filter

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