Credit Card Installment Value Sum, PostgreSQL

Language: Layout:

DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions (
    transaction_id int primary key,
    credit_card_id int,
    transaction_date timestamp,
    merchant_name varchar(256),
    total_value decimal(19,4),
    installment_value decimal(19,4),
    installments int,
    txn int
);

insert into transactions values(1,11111111,'2018-01-10T00:00:00','Colorful Soaps', 19.99, 19.99, 1, 1);
insert into transactions values(2,22222222,'2018-01-11T00:01:00','Cantina da Mamma',43.5,43.5,1,2);
insert into transactions values(3,33333333,'2018-01-12T01:02:00','Boulevard Hotel',129,129,1,3);
insert into transactions values(4,11111111,'2018-01-15T11:11:11','Micas Bar',225.9,75.3,3,4);
insert into transactions values(5,11111111,'2018-01-15T11:11:11','Micas Bar',225.9,75.3,3,4);
insert into transactions values(6,11111111,'2018-01-15T11:11:11','Micas Bar',225.9,75.3,3,4);
insert into transactions values(7,22222222,'2018-01-18T22:10:01','IPear Store',9999.99,9999.99,1,5);
insert into transactions values(8,11111111,'2018-02-20T21:08:32','Forrest Paintball',1337,1337,1,6);
insert into transactions values(9,44444444,'2018-02-22T00:05:30','Unicorn Costumes',100,50,2,7);
insert into transactions values(10,44444444,'2018-02-22T00:05:30','Unicorn Costumes',100,50,2,7);

SELECT * FROM transactions;

WITH cte AS
   (SELECT *,
       transaction_date + 
       (interval '1 month'  *
         (ROW_NUMBER() OVER(PARTITION BY transaction_date, credit_card_id ORDER BY transaction_date) - 1)
       ) AS install_date
       
    FROM transactions)

SELECT credit_card_id, 
       Date_Part('year', install_date), 
       Date_Part('month', install_date), SUM(installment_value) As sum_installment_value
FROM cte 
GROUP BY credit_card_id, 
         Date_Part('year', install_date), 
         Date_Part('month', install_date)
ORDER BY credit_card_id, 
         Date_Part('year', install_date), 
         Date_Part('month', install_date);


absolute service time: 0,55 sec