Layout Msg Queries

Ranking Tags (by year)

WITH data AS (SELECT 
    tag_id,
    EXTRACT(YEAR FROM created_at) AS year
FROM topic_tags)

SELECT year, rank, name, qt FROM (
    SELECT 
        tag_id,
        COUNT(tag_id) AS qt,
        year,
        rank() OVER (PARTITION BY year ORDER BY COUNT(tag_id) DESC) AS rank    
    FROM
        data
    GROUP BY year, tag_id) as rnk
INNER JOIN tags ON tags.id = rnk.tag_id
WHERE rank <= 5 -- >Ranking Limit 
ORDER BY year DESC, qt DESC
year rank name qt
2020 1 featured 1
2020 1 human-resources 1
2020 1 demo 1
2019 1 demo 12
2019 2 human-resources 4
2019 3 featured 3
2019 3 customer 3
2019 3 milestones-2019 3

Average User

WITH data AS (SELECT 
    id,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(YEAR FROM created_at) AS year
FROM users)

SELECT
    year,
    month,
    ROUND(AVG(qt_topic_day)) AS avg_topic
FROM
(SELECT COUNT(id) AS qt_topic_day,
        day,
        month,
        year
FROM data    
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
year month avg_topic
2020 1 1
2020 2 1
2020 3 1
2020 4 3
2020 5 1
2019 4 4
2019 5 2
2019 6 2
2019 7 1
2019 8 1
2019 9 2
2019 10 1