Running balance calculation using window functions with join-CodePudding

I am trying to find a way to calculate the running balance of an account. Below is my schema and the query I tried.

CREATE TABLE transactions(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,date DATE  NOT NULL
  ,dr   INTEGER  NOT NULL
  ,cr   INTEGER  NOT NULL
  ,amt  INTEGER  NOT NULL
);
CREATE TABLE accounts(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,name VARCHAR(55)  DEFAULT NULL
);
INSERT INTO accounts(id,name) VALUES (1,'C1');
INSERT INTO accounts(id,name) VALUES (2,'C2');
INSERT INTO accounts(id,name) VALUES (3,'C3');

INSERT INTO transactions(id,date,dr,cr,amt) VALUES (1,'2020-01-01',1,2,100);
INSERT INTO transactions(id,date,dr,cr,amt) VALUES (2,'2020-01-01',1,2,200);
INSERT INTO transactions(id,date,dr,cr,amt) VALUES (3,'2020-01-02',1,3,100);
INSERT INTO transactions(id,date,dr,cr,amt) VALUES (4,'2020-01-03',3,2,100);

SELECT date, dr as id, balance FROM (
  SELECT date, dr, SUM(amt) OVER (PARTITION BY dr ORDER BY date ) as balance FROM transactions
  GROUP BY date, dr, amt
  UNION ALL
  SELECT date, cr, -SUM(amt) OVER (PARTITION BY cr ORDER BY date ) as balance FROM transactions
  GROUP BY date, cr, amt
) as balances
GROUP BY date, dr, balance
ORDER BY date, dr, balance

Output

date	id	balance
2020-01-01	1	300
2020-01-01	2	-300
2020-01-02	1	400
2020-01-02	3	-100
2020-01-03	2	-400
2020-01-03	3	100

Trying to arrive at the output as shown below, taking into consideration of debit and credit accounts. But as you can see, the balance of C3 on 2020-01-03 is 100 instead of 0. What am I doing wrong?

id	name	date	balance
1	C1	2020-01-01	300
2	C2	2020-01-01	-300
1	C1	2020-01-02	400
3	C3	2020-01-02	-100
2	C2	2020-01-03	-400
3	C3	2020-01-03	000

View on DB Fiddle

CodePudding user response：

Union the dr's and crs

SELECT 
  acc.id, 
  acc.name, 
  SUM(tx.amt) as balance 
FROM 
  accounts acc 
  JOIN transactions tx ON tx.dr = acc.id
GROUP BY   acc.id,   acc.name
UNION  ALL
SELECT 
  acc.id, 
  acc.name, 
  SUM(tx.amt * -1) as balance 
FROM 
  accounts acc 
  JOIN transactions tx ON tx.CR = acc.id
GROUP BY   acc.id,   acc.name;

CodePudding user response：

finally found the solution, though very convoluted! Any other optimized solutions ?

WITH debits AS (
    SELECT d1.dr as id, d1.date, coalesce(d1.debit, 0) as debit FROM (
        SELECT dr, date,
            amt as debit
        FROM transactions
        GROUP BY dr, date, amt
    ) as d1
    GROUP BY d1.dr, d1.date, d1.debit
),
credits as (
    SELECT c1.cr as id, c1.date, coalesce(c1.credit, 0) as credit FROM (
        SELECT cr, date,
            amt as credit
        FROM transactions
        GROUP BY cr, date, amt
    ) as c1
    GROUP BY c1.cr, c1.date, c1.credit
),
ledger as (
    SELECT id, date, debit, 0 AS credit FROM debits
    UNION ALL
    SELECT id, date, 0 AS debit, credit FROM credits
    ORDER BY date, id
)
SELECT id, date,
       sum( coalesce(SUM(debit), 0) - coalesce(SUM(credit), 0) ) over (partition by id order by date) as balance
FROM ledger
GROUP BY date, id
ORDER by date, id, balance

View on DB Fiddle