every_day_balance.sql

select c.id,
            c.number,
            dates.date,
            closed_balances.balance as closed_balance,
            debits.debit,
            credits.credit,
            sum(
                case dates.date
                when :start_date then closed_balances.balance + debit - credit
                else debit - credit end
            ) over (partition by c.id order by dates.date) as balance_after
     from balance_accounts c
         join (
            select date_trunc('day', dates)::date as date
            from generate_series(:start_date::timestamp, :end_date::timestamp, '1 day'::interval) dates
         ) as dates on true

         -- DEBITS
         left join (
            select dba.id, dba.number, sum(amount) as debit, date
            from transactions
            inner join accounts da on transactions.debit_account_id = da.id
            inner join balance_accounts dba on da.balance_account_id = dba.id
            where dba.id in (
                'c2ae3627-04bd-4947-994e-6730711b79ac',
                'd461a74b-d804-460d-907d-595a4763e618',
                '0d020ad4-d05e-4a72-8b53-6849ae47edf6',
                '257629f5-3971-43f1-a567-0506cc93221c',
                'c2e5eb90-05c5-4e47-8e94-6de0da1bca7f',
                '43fffd8e-932b-42fb-9025-377067e0c67b',
                'ea3ab92d-e472-4dcc-9166-6582b08a32c6',
                'c8948560-8914-401b-b0d9-78e917404b7b',
                'b4cafe80-047e-4947-bb5a-ea6d4e342f03',
                'caa3ed3a-efd9-41eb-b4a8-8aada04521d0',
                '0e7277fb-eea0-4cb3-9ec4-2ced8a1b6ae5',
                '597327dc-f434-49c9-a76b-7d2876673162',
                'd46e89ff-8415-4f6e-987b-d10e505dd087',
                'a5ce47c3-63d7-44c5-9590-4af448a4d294',
                'dd76abee-03a1-4a19-a597-71af4dd0c7ee'
            ) and date between :start_date and :end_date
            group by dba.id, dba.number, date
         ) as debits on debits.id = c.id and dates.date = debits.date

         -- CREDITS
         left join (
            select cba.id, cba.number, sum(amount) as credit, date
            from transactions
            inner join accounts ca on transactions.credit_account_id = ca.id
            inner join balance_accounts cba on ca.balance_account_id = cba.id
            where cba.id in (
                'c2ae3627-04bd-4947-994e-6730711b79ac',
                'd461a74b-d804-460d-907d-595a4763e618',
                '0d020ad4-d05e-4a72-8b53-6849ae47edf6',
                '257629f5-3971-43f1-a567-0506cc93221c',
                'c2e5eb90-05c5-4e47-8e94-6de0da1bca7f',
                '43fffd8e-932b-42fb-9025-377067e0c67b',
                'ea3ab92d-e472-4dcc-9166-6582b08a32c6',
                'c8948560-8914-401b-b0d9-78e917404b7b',
                'b4cafe80-047e-4947-bb5a-ea6d4e342f03',
                'caa3ed3a-efd9-41eb-b4a8-8aada04521d0',
                '0e7277fb-eea0-4cb3-9ec4-2ced8a1b6ae5',
                '597327dc-f434-49c9-a76b-7d2876673162',
                'd46e89ff-8415-4f6e-987b-d10e505dd087',
                'a5ce47c3-63d7-44c5-9590-4af448a4d294',
                'dd76abee-03a1-4a19-a597-71af4dd0c7ee'
            ) and date between :start_date and :end_date
            group by cba.id, cba.number, date
        ) as credits on credits.id = c.id and dates.date = credits.date

        -- CLOSED BALANCES
        left join (
            select ba.id, sum(balance) as balance
            from account_closed_balances acb
            inner join accounts a on acb.account_id = a.id
            inner join balance_accounts ba on a.balance_account_id = ba.id
            where ba.id in (
                'c2ae3627-04bd-4947-994e-6730711b79ac',
                'd461a74b-d804-460d-907d-595a4763e618',
                '0d020ad4-d05e-4a72-8b53-6849ae47edf6',
                '257629f5-3971-43f1-a567-0506cc93221c',
                'c2e5eb90-05c5-4e47-8e94-6de0da1bca7f',
                '43fffd8e-932b-42fb-9025-377067e0c67b',
                'ea3ab92d-e472-4dcc-9166-6582b08a32c6',
                'c8948560-8914-401b-b0d9-78e917404b7b',
                'b4cafe80-047e-4947-bb5a-ea6d4e342f03',
                'caa3ed3a-efd9-41eb-b4a8-8aada04521d0',
                '0e7277fb-eea0-4cb3-9ec4-2ced8a1b6ae5',
                '597327dc-f434-49c9-a76b-7d2876673162',
                'd46e89ff-8415-4f6e-987b-d10e505dd087',
                'a5ce47c3-63d7-44c5-9590-4af448a4d294',
                'dd76abee-03a1-4a19-a597-71af4dd0c7ee'
            ) and date = :closed_date
            group by ba.id
        ) as closed_balances on closed_balances.id = c.id
        where c.id in (
            'c2ae3627-04bd-4947-994e-6730711b79ac',
            'd461a74b-d804-460d-907d-595a4763e618',
            '0d020ad4-d05e-4a72-8b53-6849ae47edf6',
            '257629f5-3971-43f1-a567-0506cc93221c',
            'c2e5eb90-05c5-4e47-8e94-6de0da1bca7f',
            '43fffd8e-932b-42fb-9025-377067e0c67b',
            'ea3ab92d-e472-4dcc-9166-6582b08a32c6',
            'c8948560-8914-401b-b0d9-78e917404b7b',
            'b4cafe80-047e-4947-bb5a-ea6d4e342f03',
            'caa3ed3a-efd9-41eb-b4a8-8aada04521d0',
            '0e7277fb-eea0-4cb3-9ec4-2ced8a1b6ae5',
            '597327dc-f434-49c9-a76b-7d2876673162',
            'd46e89ff-8415-4f6e-987b-d10e505dd087',
            'a5ce47c3-63d7-44c5-9590-4af448a4d294',
            'dd76abee-03a1-4a19-a597-71af4dd0c7ee'
         )
    ) as main