SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE transacoes ( usuario_origem INT NOT NULL, usuario_destino INT NOT NULL, data_transacao datetime, montante FLOAT ); INSERT INTO transacoes (usuario_origem, usuario_destino, data_transacao, montante) VALUES (1, 5, CAST('2022-01-02' AS date), 124.00), (2, 5, CAST('2022-01-02' AS date), 23.00), (5, 1, CAST('2022-01-02' AS date), 55.15), (3, 2, CAST('2022-01-05' AS date), 44.50), (3, 2, CAST('2022-01-15' AS date), 12.00), (4, 1, CAST('2022-01-15' AS date), 44.10), (3, 4, CAST('2022-01-18' AS date), 50.00), (1, 2, CAST('2022-01-20' AS date), 150.10), (5, 2, CAST('2022-01-21' AS date), 500.10), (3, 1, CAST('2022-01-21' AS date), 20.10), (1, 5, CAST('2022-01-30' AS date), 40.50), (4, 1, CAST('2022-01-31' AS date), 999.00); SELECT * FROM transacoes; WITH debito AS ( SELECT usuario_origem, SUM(montante) AS debitado FROM transacaoes GROUP BY 1 ), credito AS ( SELECT usuario_destino, SUM(montante) AS creditado FROM transacoes GROUP BY 1 ) -- full (outer) join debits and credits tables on user id, taking net change as difference between credits and debits, coercing nulls to zeros with coalesce() SELECT COALESCE(sender, receiver) AS user, COALESCE(credited, 0) - COALESCE(debited, 0) AS net_change FROM debito d FULL JOIN credito c ON d.usuario_origem = c.usuario_destino ORDER BY 2 DESC