Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE "Currency" AS ENUM ('USD', 'EUR', 'GBP');

CREATE TYPE


CREATE TYPE "TransactionStatus" AS ENUM ('COMPLETE', 'PENDING', 'REJECTED');

CREATE TYPE


CREATE TYPE "TransactionType" AS ENUM ('EXCHANGE', 'TRANSFER', 'DEPOSIT', 'WITHDRAWAL', 'CARD_IN', 'CARD_OUT', 'INVOICE_PAYMENT');

CREATE TYPE


CREATE TABLE "Account"
(
    id          SERIAL PRIMARY KEY,
    currency    "Currency"                                 NOT NULL
)

CREATE TABLE


INSERT INTO "Account" (id, currency) VALUES (1, 'USD');
INSERT INTO "Account" (id, currency) VALUES (2, 'GBP');
INSERT INTO "Account" (id, currency) VALUES (3, 'EUR');
INSERT INTO "Account" (id, currency) VALUES (4, 'USD');
INSERT INTO "Account" (id, currency) VALUES (5, 'GBP');
INSERT INTO "Account" (id, currency) VALUES (6, 'EUR');
INSERT INTO "Account" (id, currency) VALUES (7, 'USD');
INSERT INTO "Account" (id, currency) VALUES (8, 'GBP');
INSERT INTO "Account" (id, currency) VALUES (9, 'EUR');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


CREATE TABLE "Transaction"
(
    id                  SERIAL
        PRIMARY KEY,
    type                "TransactionType"                      NOT NULL,
    "fromAccountId"     INTEGER
                                                               REFERENCES "Account"
                                                                   ON UPDATE CASCADE ON DELETE SET NULL,
    "fromAmount"        DOUBLE PRECISION,
    "toAccountId"       INTEGER
                                                               REFERENCES "Account"
                                                                   ON UPDATE CASCADE ON DELETE SET NULL,
    "toAmount"          DOUBLE PRECISION,
    "createdAt"         TIMESTAMP(3) DEFAULT current_timestamp NOT NULL,
    status              "TransactionStatus"                    NOT NULL
)

CREATE TABLE


INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (8, 'DEPOSIT', null, null, 3, 1126062, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (9, 'DEPOSIT', null, null, 1, 8962768, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (11, 'DEPOSIT', null, null, 6, 1262031, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (12, 'DEPOSIT', null, null, 8, 8316692, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (13, 'DEPOSIT', null, null, 5, 6739586, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (15, 'DEPOSIT', null, null, 1, 2954408, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (19, 'DEPOSIT', null, null, 2, 9334989, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (21, 'DEPOSIT', null, null, 2, 3585419, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (24, 'DEPOSIT', null, null, 3, 2121075, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (26, 'DEPOSIT', null, null, 2, 2910474, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (28, 'DEPOSIT', null, null, 5, 1864872, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (29, 'DEPOSIT', null, null, 4, 1226615, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (30, 'DEPOSIT', null, null, 5, 2863291, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (31, 'DEPOSIT', null, null, 4, 2404810, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (37, 'DEPOSIT', null, null, 8, 3611272, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (38, 'DEPOSIT', null, null, 6, 7159320, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (39, 'DEPOSIT', null, null, 7, 5760753, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (40, 'DEPOSIT', null, null, 3, 1550935, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (43, 'DEPOSIT', null, null, 3, 7582280, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (44, 'DEPOSIT', null, null, 8, 4431650, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (45, 'DEPOSIT', null, null, 9, 2669120, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (46, 'DEPOSIT', null, null, 3, 8376512, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (47, 'DEPOSIT', null, null, 3, 7464642, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (52, 'WITHDRAWAL', 5, 745969, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (54, 'WITHDRAWAL', 1, 817272,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (56, 'WITHDRAWAL', 3, 490693,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (58, 'WITHDRAWAL', 4, 188072,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (59, 'WITHDRAWAL',  4, 359753,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (62, 'WITHDRAWAL',  8, 871142,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (64, 'WITHDRAWAL',  8, 598029,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (69, 'WITHDRAWAL',  1, 521680,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (70, 'WITHDRAWAL',  8, 608998,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (75, 'WITHDRAWAL',  1, 152653,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (76, 'WITHDRAWAL',  5, 168617,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (82, 'WITHDRAWAL',  6, 464464,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (83, 'WITHDRAWAL',  4, 244697,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (84, 'WITHDRAWAL',  6, 302192,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (88, 'WITHDRAWAL', 7, 484241,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (90, 'WITHDRAWAL',  2, 663081,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (96, 'WITHDRAWAL',  1, 512212,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (97, 'WITHDRAWAL', 3, 446931,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (98, 'WITHDRAWAL',  1, 280695,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (99, 'WITHDRAWAL', 8, 975881,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (107, 'EXCHANGE', 8, 57381, 9, 57381, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (109, 'EXCHANGE', 6, 75737, 4, 75737, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (113, 'EXCHANGE', 3, 29284, 2, 29284, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (114, 'EXCHANGE', 9, 93063, 7, 93063, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (115, 'EXCHANGE', 4, 36917, 5, 36917, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (117, 'EXCHANGE', 8, 90786, 9, 90786, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (118, 'EXCHANGE', 1, 68880, 2, 68880, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (120, 'EXCHANGE', 9, 78170, 7, 78170, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (121, 'EXCHANGE', 9, 69579, 8, 69579, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (122, 'EXCHANGE', 6, 88410, 4, 88410, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (124, 'EXCHANGE', 4, 34284, 5, 34284, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (125, 'EXCHANGE', 2, 54175, 1, 54175, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (126, 'EXCHANGE', 6, 38257, 5, 38257, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (127, 'EXCHANGE', 3, 99679, 1, 99679, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (128, 'EXCHANGE', 3, 22072, 2, 22072, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (131, 'EXCHANGE', 5, 88716, 6, 88716, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (133, 'EXCHANGE', 4, 99239, 6, 99239, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (136, 'EXCHANGE', 6, 60024, 4, 60024, 'COMPLETE');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT a.id                                                              AS account_id,
       a.currency,
       sum(coalesce(t."toAmount", 0)) - sum(coalesce(t."fromAmount", 0)) AS balance,
       ingoing_exchange_transactions."toAmount"                          AS ingoing_exchange,
       outgoing_exchange_transactions."fromAmount"                       AS outgoing_exchange,
       date_part('month', date_trunc('month', t."createdAt"))            AS date,
       date_part('year', date_trunc('year', t."createdAt"))              AS year
FROM "Transaction" t
         INNER JOIN "Account" a ON (t."fromAccountId" = a.id OR t."toAccountId" = a.id) 
         LEFT JOIN LATERAL (SELECT "toAccountId", "toAmount"
                            FROM "Transaction"
                            WHERE "type" = 'EXCHANGE'
                              AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) ingoing_exchange_transactions
                   ON a.id = ingoing_exchange_transactions."toAccountId"
         LEFT JOIN LATERAL (SELECT "fromAccountId", "fromAmount"
                            FROM "Transaction"
                            WHERE "type" = 'EXCHANGE'
                              AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) outgoing_exchange_transactions
                   ON a.id = outgoing_exchange_transactions."fromAccountId"
WHERE t.type <> 'EXCHANGE'
  AND (t.status = 'COMPLETE' OR t.status = 'PENDING')
GROUP BY account_id, a.currency, ingoing_exchange, outgoing_exchange, date, year
ORDER BY date, year;


account_id
currency
balance
ingoing_exchange
outgoing_exchange
date
year


1
USD
9632664
54175
68880
3
2024

1
USD
9632664
99679
68880
3
2024

2
GBP
15167801
22072
54175
3
2024

2
GBP
15167801
29284
54175
3
2024

2
GBP
15167801
68880
54175
3
2024

3
EUR
27283882
null
22072
3
2024

3
EUR
27283882
null
29284
3
2024

3
EUR
27283882
null
99679
3
2024

4
USD
2838903
60024
34284
3
2024

4
USD
2838903
60024
36917
3
2024

4
USD
2838903
60024
99239
3
2024

4
USD
2838903
75737
34284
3
2024

4
USD
2838903
75737
36917
3
2024

4
USD
2838903
75737
99239
3
2024

4
USD
2838903
88410
34284
3
2024

4
USD
2838903
88410
36917
3
2024

4
USD
2838903
88410
99239
3
2024

5
GBP
10553163
34284
88716
3
2024

5
GBP
10553163
36917
88716
3
2024

5
GBP
10553163
38257
88716
3
2024

6
EUR
7654695
88716
38257
3
2024

6
EUR
7654695
88716
60024
3
2024

6
EUR
7654695
88716
75737
3
2024

6
EUR
7654695
88716
88410
3
2024

6
EUR
7654695
99239
38257
3
2024

6
EUR
7654695
99239
60024
3
2024

6
EUR
7654695
99239
75737
3
2024

6
EUR
7654695
99239
88410
3
2024

7
USD
5276512
78170
null
3
2024

7
USD
5276512
93063
null
3
2024

8
GBP
13305564
69579
57381
3
2024

8
GBP
13305564
69579
90786
3
2024

9
EUR
2669120
57381
69579
3
2024

9
EUR
2669120
57381
78170
3
2024

9
EUR
2669120
57381
93063
3
2024

9
EUR
2669120
90786
69579
3
2024

9
EUR
2669120
90786
78170
3
2024

9
EUR
2669120
90786
93063
3
2024



SELECT 38


SELECT a.id                                                              AS account_id,
       a.currency,
       sum(coalesce(t."toAmount", 0)) - sum(coalesce(t."fromAmount", 0)) AS balance,
       sum(ingoing_exchange_transactions."toAmount")                     AS ingoing_exchange,
       sum(outgoing_exchange_transactions."fromAmount")                  AS outgoing_exchange,
       date_part('month', date_trunc('month', t."createdAt"))            AS date,
       date_part('year', date_trunc('year', t."createdAt"))              AS year
FROM "Transaction" t
         INNER JOIN "Account" a ON (t."fromAccountId" = a.id OR t."toAccountId" = a.id)
         LEFT JOIN LATERAL (SELECT "toAccountId", "toAmount"
                            FROM "Transaction"
                            WHERE "type" = 'EXCHANGE'
                              AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) ingoing_exchange_transactions
                   ON a.id = ingoing_exchange_transactions."toAccountId"
         LEFT JOIN LATERAL (SELECT "fromAccountId", "fromAmount"
                            FROM "Transaction"
                            WHERE "type" = 'EXCHANGE'
                              AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) outgoing_exchange_transactions
                   ON a.id = outgoing_exchange_transactions."fromAccountId"
WHERE t.type <> 'EXCHANGE'
  AND (t.status = 'COMPLETE' OR t.status = 'PENDING')
GROUP BY account_id, a.currency, date, year
ORDER BY date, year;


account_id
currency
balance
ingoing_exchange
outgoing_exchange
date
year


1
USD
19265328
1076978
964320
3
2024

2
GBP
45503403
480944
650100
3
2024

3
EUR
81851646
null
1208280
3
2024

4
USD
25550127
3362565
2556600
3
2024

5
GBP
31659489
547290
1330740
3
2024

6
EUR
61237560
3007280
2099424
3
2024

7
USD
10553024
342466
null
3
2024

8
GBP
26611128
974106
1037169
3
2024

9
EUR
16014720
444501
481624
3
2024



SELECT 9
account_id	currency	balance	ingoing_exchange	outgoing_exchange	date	year
1	USD	9632664	54175	68880	3	2024
1	USD	9632664	99679	68880	3	2024
2	GBP	15167801	22072	54175	3	2024
2	GBP	15167801	29284	54175	3	2024
2	GBP	15167801	68880	54175	3	2024
3	EUR	27283882	null	22072	3	2024
3	EUR	27283882	null	29284	3	2024
3	EUR	27283882	null	99679	3	2024
4	USD	2838903	60024	34284	3	2024
4	USD	2838903	60024	36917	3	2024
4	USD	2838903	60024	99239	3	2024
4	USD	2838903	75737	34284	3	2024
4	USD	2838903	75737	36917	3	2024
4	USD	2838903	75737	99239	3	2024
4	USD	2838903	88410	34284	3	2024
4	USD	2838903	88410	36917	3	2024
4	USD	2838903	88410	99239	3	2024
5	GBP	10553163	34284	88716	3	2024
5	GBP	10553163	36917	88716	3	2024
5	GBP	10553163	38257	88716	3	2024
6	EUR	7654695	88716	38257	3	2024
6	EUR	7654695	88716	60024	3	2024
6	EUR	7654695	88716	75737	3	2024
6	EUR	7654695	88716	88410	3	2024
6	EUR	7654695	99239	38257	3	2024
6	EUR	7654695	99239	60024	3	2024
6	EUR	7654695	99239	75737	3	2024
6	EUR	7654695	99239	88410	3	2024
7	USD	5276512	78170	null	3	2024
7	USD	5276512	93063	null	3	2024
8	GBP	13305564	69579	57381	3	2024
8	GBP	13305564	69579	90786	3	2024
9	EUR	2669120	57381	69579	3	2024
9	EUR	2669120	57381	78170	3	2024
9	EUR	2669120	57381	93063	3	2024
9	EUR	2669120	90786	69579	3	2024
9	EUR	2669120	90786	78170	3	2024
9	EUR	2669120	90786	93063	3	2024
account_id	currency	balance	ingoing_exchange	outgoing_exchange	date	year
1	USD	19265328	1076978	964320	3	2024
2	GBP	45503403	480944	650100	3	2024
3	EUR	81851646	null	1208280	3	2024
4	USD	25550127	3362565	2556600	3	2024
5	GBP	31659489	547290	1330740	3	2024
6	EUR	61237560	3007280	2099424	3	2024
7	USD	10553024	342466	null	3	2024
8	GBP	26611128	974106	1037169	3	2024
9	EUR	16014720	444501	481624	3	2024