How can I calculate the number of publications per month?-CodePudding

There is a table of posts on social networks with the date and title of the publication.

id	created_at	title
1	2022-01-17 08:50:58	Sberbank is the best bank
2	2022-01-17 18:36:41	Visa vs MasterCard
3	2022-01-17 16:16:17	Visa vs UnionPay
4	2022-01-17 18:01:00	Mastercard vs UnionPay
5	2022-01-16 16:44:36	Hadoop or Greenplum: pros and cons
6	2022-01-16 14:57:32	NFC: wireless payment

I need to calculate the number of publications per month, indicating the first date of the month and the percentage of increase in the number of posts (publications) relative to the previous month. The data in the resulting table should be arranged in chronological order. The percentage of the increase in the number of messages can be negative, and the result should be rounded to one decimal place with the addition of the % sign.

Table results

dt	count	prent_growth
2022-02-01	175	null
2022-03-01	338	93.1%
2022-04-01	345	2.1%
2022-05-01	295	-14.5%
2022-06-01	330	11.9%

I read documentation, but i don't understand how to do that..

CodePudding user response：

step-by-step demo: db<>fiddle

SELECT 
    *,
    (count * 100 / prev_count - 100)::text || '%'                 -- 4
FROM (
    SELECT
        *,
        lag(count) OVER (ORDER BY pub_month) as prev_count        -- 3
    FROM (
        SELECT
            date_trunc('month', pub_date)::date as pub_month,     -- 1
            COUNT(*)                                              -- 2
        FROM mytable
        GROUP BY 1
    ) s
) s

Normalize all dates to the first day of the month ("truncates" the day part if you like to see it that way)
Group all normalized dates and count all entrys per normalized date/month
Using lag() window function to shift the previous count result to the current row. Now you can directly compare the previous and current month count
Calculate the percentage. The result is a numeral type. So can cast it into text type to add the percentage character afterwards.