PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Calculate quarterly percentage increase and decrease in sql

I can simply use LAG() to calculate the percentage change from my table (see below) of this month compared to the data 3 months ago or 1 year ago.

But now, I want to get this result: calculate the sum of each quarter, and then get the percentage change (Q1 of the following year needs to be calculated with reference to Q4 of the previous year.)

For example:

year quarter total_num quarter_change_pct
2021 1 1088030 0.00%
2021 2 1077857 -0.93%
2021 3 1048368 -2.74%
2021 4 992279 -5.35%
2022 1 1026123 3.41%
2022 2 1074024 4.67%
2022 3 1054501 -1.82%
2022 4 1080568 2.47%
2023 1 1001410 -7.33%
2023 2 961672 -3.97%
2023 3 979835 1.89%
2023 4 982167 0.24%

I don’t know how to solve this problem, it would be greatly appreciated if anyone can help.(BTW, the database I use supports the majority of PostgreSQL syntax, so you can also use PostgreSQL for demo, thanks!)

sample data:

create table tb1(
  date date,
  num int
);

insert into tb1 values
('2021-01-31', 359738),
('2021-02-28', 378564),
('2021-03-31', 349728),
('2021-04-30', 368945),  
('2021-05-31', 321456),
('2021-06-30', 387456),
('2021-07-31', 310567),
('2021-08-31', 342189),
('2021-09-30', 395612),
('2021-10-31', 278945),
('2021-11-30', 365478),
('2021-12-31', 347856),
('2022-01-31', 319478),
('2022-02-28', 382456),
('2022-03-31', 324189),
('2022-04-30', 395612),  
('2022-05-31', 367845),
('2022-06-30', 310567),
('2022-07-31', 382456),
('2022-08-31', 347856),
('2022-09-30', 324189),
('2022-10-31', 395612),
('2022-11-30', 319478),
('2022-12-31', 365478),
('2023-01-31', 302856),
('2023-02-28', 334531),
('2023-03-31', 364023),
('2023-04-30', 334534),  
('2023-05-31', 313678),
('2023-06-30', 313460),
('2023-07-31', 357281),
('2023-08-31', 314578),
('2023-09-30', 307976),
('2023-10-31', 304567),
('2023-11-30', 311378),
('2023-12-31', 366222);

My table looks like this(simplified):

date num
2021-01-31 359738
2021-02-28 378564
2021-03-31 349728
2021-04-30 368945
2021-05-31 321456
2021-06-30 387456
2021-07-31 310567
2021-08-31 342189
2021-09-30 395612
2021-10-31 278945
2021-11-30 365478
2021-12-31 347856
2022-01-31 319478
2022-02-28 382456
2022-03-31 324189
2022-04-30 395612
2022-05-31 367845
2022-06-30 310567
2022-07-31 382456
2022-08-31 347856
2022-09-30 324189
2022-10-31 395612
2022-11-30 319478
2022-12-31 365478
2023-01-31 302856
2023-02-28 334531
2023-03-31 364023
2023-04-30 334534
2023-05-31 313678
2023-06-30 313460
2023-07-31 357281
2023-08-31 314578
2023-09-30 307976
2023-10-31 304567
2023-11-30 311378
2023-12-31 366222
Tags:

3

Answers


  1. 0

    You have to use LAG and Quarter function to solve the issue in ProgreSQL.

    QUARTER function return one of the following values:

    • 1 if the date falls in January, February or March.
    • 2 if the date falls in April, May or June.
    • 3 if the date falls in July, August or September.
    • 4 if the date falls in October, November or December.

    LAG() function is a powerful window function that allows you to access data from a previous row within the same result set. It’s particularly useful for comparing values in the current row with values in the preceding row.

    Finally, to get your result set the SQL script will be .

    SELECT Year, QUARTER, total_num,  
to_char((total_num-(LAG(total_num,1) OVER (ORDER BY Year, QUARTER))) * 100 /(LAG(total_num,1) OVER (ORDER BY Year,QUARTER)), 'S999D99%')  as PCT 
FROM 
(SELECT EXTRACT('Year' FROM DATE) AS Year, 
EXTRACT(QUARTER FROM DATE) AS QUARTER, 
CAST (SUM(NUM) AS DECIMAL) AS total_num FROM tb1 GROUP BY Year, QUARTER 
ORDER BY Year,QUARTER);

    Implement this work on PostgreSQL 17.

    Thanks

    Login or Signup to reply.
  2. 0

    This query may be a little complicated, but the result meets your requirements. I hope it can help you.

    test on PostgreSQL 16: https://dbfiddle.uk/7W1SW7KH

    WITH QuarterlyTotals AS (
  SELECT
    EXTRACT(YEAR FROM date) AS year,
    EXTRACT(QUARTER FROM date) AS quarter,
    SUM(num) AS total_num
  FROM tb1
  GROUP BY year,quarter
),
QuarterlyChanges AS (
  SELECT
    t1.year,
    t1.quarter,
    t1.total_num,
    COALESCE(t2.total_num, t1.total_num) AS prev_quarter_total
  FROM QuarterlyTotals t1
  LEFT JOIN QuarterlyTotals t2 ON (
    t1.year = t2.year AND t1.quarter = t2.quarter + 1
    OR (t1.year = t2.year + 1 AND t1.quarter = 1 AND t2.quarter = 4)
  )
)
SELECT
  year,
  quarter,
  total_num,
  ROUND((total_num - prev_quarter_total)::numeric / prev_quarter_total * 100, 2) || '%' AS quarter_change_pct
FROM QuarterlyChanges
ORDER BY  year, quarter;
    Login or Signup to reply.
  3. 0 
    WITH quarterly_sums AS (
SELECT 
    DATE_TRUNC('quarter', tran_date) AS quarter,
    SUM(amount) AS total_amount
FROM tb1_sample GROUP BY DATE_TRUNC('quarter', tran_date)
ORDER BY quarter

    ),
    quarterly_changes AS (
    SELECT
    quarter,
    total_amount,
    LAG(total_amount) OVER (ORDER BY quarter) AS previous_total
    FROM quarterly_sums
    )
    SELECT
    EXTRACT(YEAR FROM quarter) AS year,
    EXTRACT(QUARTER FROM quarter) AS year,
    total_amount,
    COALESCE(previous_total,0) as previous,
    CASE
    WHEN previous_total<>0 THEN
    round(((total_amount – previous_total)/previous_total)*100,2)
    ELSE
    0
    END AS percentage_change
    FROM quarterly_changes
    ORDER BY quarter;

    You find the Quaterly amount and Previous Quarter amount from the 1st and 2nd cte (using LAG) and then calcualte the percentage change.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search