Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Invoices (
    id INT,
    name VARCHAR,
    customer_id INT,
    total_amount FLOAT,
    state VARCHAR,
    invoice_date DATE
);

CREATE TABLE


INSERT INTO Invoices
    (id, name, customer_id, total_amount, state, invoice_date)
VALUES
    (1,  'INV/2020/0001', 2, 100, 'posted', '2020-04-05'),
    (2,  'INV/2020/0002', 1, 100, 'draft',  '2020-04-05'),
    (3,  'INV/2020/0003', 2, 100, 'draft',  '2020-05-24'),
    (4,  'INV/2020/0004', 1, 100, 'posted', '2020-05-25'),
    (5,  'INV/2020/0005', 2, 100, 'posted', '2020-06-05'),
    (6,  'INV/2020/0006', 1, 100, 'posted', '2020-07-05'),
    (7,  'INV/2020/0007', 1, 100, 'draft',  '2020-08-24'),
    (8,  'INV/2020/0008', 1, 100, 'posted', '2020-08-25'),
    (9,  'INV/2020/0009', 1, 100, 'posted', '2020-09-05'),
    (10, 'INV/2020/0010', 1, 100, 'draft',  '2020-09-05'),
    (11, 'INV/2020/0011', 2, 100, 'draft',  '2020-10-24'),
    (12, 'INV/2020/0012', 1, 100, 'posted', '2020-10-25'),
    (13, 'INV/2020/0013', 2, 100, 'posted', '2020-11-05'),
    (14, 'INV/2020/0014', 1, 100, 'posted', '2020-11-05'),
    (15, 'INV/2020/0015', 2, 100, 'draft',  '2020-11-24'),
    (16, 'INV/2020/0016', 1, 100, 'posted', '2020-11-25');

INSERT 0 16


SELECT customer_id, 
       sum(total_amount) as total_amount,
       sum(total_amount) filter (where invoice_date >= date '2020-04-01' and invoice_date < date '2020-07-01') as period_1_sum,
       sum(total_amount) filter (where invoice_date >= date '2020-07-01' and invoice_date < date '2020-10-01') as period_2_sum,
       sum(total_amount) filter (where invoice_date >= date '2020-10-01' and invoice_date < date '2021-01-01') as period_3_sum
FROM Invoices 
WHERE state = 'posted' 
GROUP BY customer_id;


customer_id
total_amount
period_1_sum
period_2_sum
period_3_sum


1
700
100
300
300

2
300
200
null
100



SELECT 2