Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create table tab1(product text, code text, order_date date, quantity int)
Insert into tab1 values
('Product_1',  '001',    '2020-01-02',    30),
('Product_1',  '001',    '2020-01-03',    31),
('Product_1',  '001',    '2020-02-23',    55),
('Product_1',  '001',    '2020-04-30',    125),
('Product_2',  '002',    '2020-03-20',    110),
('Product_2',  '002',    '2020-04-03',    59)
6 rows affected

with cte as (
select product, code,
generate_series(min(date_trunc('month',order_date)), max(date_trunc('month',order_date)), interval '1 month') dt_series
from tab1 group by product, code
),
cte1 as(
select row_number() over (partition by product order by dt_series) sno, 
product, code, dt_series 
from cte
)

select 
cte1.product, 
cte1.code,
coalesce(sum(quantity),0),
concat('month_',sno-1), 
to_char(dt_series,'yyyy-mm') from cte1 
left join tab1 on cte1.dt_series=date_trunc('month', order_date) and cte1.product=tab1.product
group by cte1.product,cte1.code,dt_series,concat('month_',sno-1),to_char(dt_series,'yyyy-mm')
order by cte1.product,cte1.code,dt_series


product
code
coalesce
concat
to_char


Product_1
001
61
month_0
2020-01

Product_1
001
55
month_1
2020-02

Product_1
001
0
month_2
2020-03

Product_1
001
125
month_3
2020-04

Product_2
002
110
month_0
2020-03

Product_2
002
59
month_1
2020-04

product	code	coalesce	concat	to_char
Product_1	001	61	month_0	2020-01
Product_1	001	55	month_1	2020-02
Product_1	001	0	month_2	2020-03
Product_1	001	125	month_3	2020-04
Product_2	002	110	month_0	2020-03
Product_2	002	59	month_1	2020-04