Sorry for the delay in responding, follow the requested queries, as some are more complex than usual, I am available if you have any questions.
Michelle
WITH post AS (SELECT
id AS post_id,
topic_id,
EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
AND deleted_at ISNULL
AND post_number != 1)
SELECT
p.year,
t.category_id AS id,
c.name category,
COUNT(p.post_id) AS qt
FROM post p
INNER JOIN topics t ON t.id = p.topic_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at ISNULL
-- AND t.category_id NOT NULL --> *** Enable to delete Post without category***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
year
id
category
qt
2020
13
General
14
2020
16
Knowledge Base
3
2020
15
Staff
3
2020
1
Uncategorized
2
2020
17
Ideas
1
2019
18
Builds
10
2019
1
Uncategorized
8
2019
11
CS001x: Intro to Computer Science
7
2019
13
General
5
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 -- *** Choose the ranking limit ***
ORDER BY year DESC, qt DESC
year
rank
tag_name
quantity
2020
1
featured
7
2020
2
human-resources
3
2020
3
demo
1
2019
1
demo
12
2019
2
human-resources
4
2019
3
featured
3
2019
3
customer
3
2019
3
milestones-2019
3
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 topics
WHERE deleted_at ISNULL)
SELECT
year,
month,
ROUND(AVG(qt_topic_day)) AS avg_topic_by_day
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_by_day
2020
1
1
2020
2
1
2020
3
2
2020
4
3
2020
5
2
2019
4
9
2019
5
4
2019
6
4
2019
7
1
2019
8
2
2019
9
3
2019
10
1
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 posts
WHERE post_type = 1
AND deleted_at ISNULL
AND post_number != 1)
SELECT
year,
month,
ROUND(AVG(qt_reply_day)) AS avg_reply_by_day
FROM
(SELECT COUNT(id) AS qt_reply_day,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_reply
GROUP BY month, year
ORDER BY year DESC, month ASC
year
month
avg_reply_by_day
2020
1
7
2020
3
2
2020
4
5
2020
5
6
2019
4
3
2019
5
2
2019
6
4
2019
7
2
2019
8
15
2019
9
3
2019
10
5
2019
12
2
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_new_user)) AS avg_new_user_by_day
FROM
(SELECT COUNT(id) AS qt_new_user,
day,
month,
year
FROM data
GROUP BY day, month, year) AS top_new_user
GROUP BY month, year
ORDER BY year DESC, month ASC