Number of topics per tag

Data & reporting
1

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?

3 Likes
2

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?

4 Likes
3

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
3 Likes