I’d like to be able to get a list of all tags and how many new topics/posts have been created for each in a certain period (say last month).
Can anyone help with the query?
סיכום מבוסס בינה מלאכותית
In a discussion titled “Number of topics per tag” under the “Data & reporting” category with the tag “sql-query”, tpetrov requested help crafting a SQL query to list all tags and count new topics created per tag within a specified period (e.g., last month). Mittineague advised exploring the database schema via the plugins admin page, highlighting relevant tables (topic_tags and tags) and their fields, and encouraged starting with a simple query to verify access. tpetrov followed up, thanking Mittineague and sharing a working SQL query that calculates topic counts per tag for a given month using a CTE for the time period, joining topics, topic_tags, and tags tables, filtering for non-system topics, and ordering results by count descending. The query includes a parameter :months_ago to adjust the time window.
You could pore through files I suppose, but IMHO it is more efficient to use the menu on the plugins admin page. You should be able to see many tables that when selected display field names and schema info.
I’m not at my desktop, but the model has
# == Schema Information
#
# Table name: topic_tags
#
# id :integer not null, primary key
# topic_id :integer not null
# tag_id :integer not null
# created_at :datetime not null
# updated_at :datetime not null
#
# Indexes
#
# index_topic_tags_on_topic_id_and_tag_id (topic_id,tag_id) UNIQUE
Note the “topic_id” and “tag_id” it suggests the names are “table name, underscore, field name”.
The tag table has
# == Schema Information
#
# Table name: tags
#
# id :integer not null, primary key
# name :string not null
# topic_count :integer default(0), not null
# created_at :datetime not null
# updated_at :datetime not null
# pm_topic_count :integer default(0), not null
# target_tag_id :integer
#
# Indexes
#
# index_tags_on_lower_name (lower((name)::text)) UNIQUE
# index_tags_on_name (name) UNIQUE
and I’ll leave it to you to look at the topic tables fields.
But that’s probably getting ahead of things. Have you been able to run a very simple query against one of those tables as a kind of “Hello Data Explorer”? What is it?
Awesome, thanks a lot for the hints.
I was able to build it. Might not be perfect, but If anyone else finds it useful:
New topics per tag
-- [params]
-- int :months_ago = 0
WITH query_period as (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)
SELECT
tags.name,
count(1) as topic_count
FROM topics t
RIGHT JOIN topic_tags tt
ON t.id = tt.topic_id
RIGHT JOIN tags tags
ON tt.tag_id = tags.id
RIGHT JOIN query_period qp
ON t.created_at >= qp.period_start
AND t.created_at <= qp.period_end
WHERE t.user_id > 0
AND tt.topic_id IS NOT NULL
GROUP BY tags.name
ORDER BY topic_count DESC