Create materialized view which calculates balances based on transaction sends and receive events

Question

I have the following table of transactions in postgres:

CREATE TABLE IF NOT EXISTS public.transfer
(
    id integer NOT NULL DEFAULT nextval('transfer_id_seq'::regclass),
    amount integer NOT NULL,
    currency integer NOT NULL,
    sender_id integer NOT NULL,
    recipient_id" integer NOT NULL,
)

where sender_id and recepient_id each have a foreign key constraint to user.id

And I would like to create a materialized view for the balances of the form:

(
    user_id integer NOT NULL,
    amount integer NOT NULL,
    currency integer NOT NULL,
    balance integer NOT NULL
)

Which is computed as: For each user, for each currency sum up all the amounts where the user is the recipient and subtract from that all the amounts where the user is the sender.

I've managed to get the sum of all sends with:

SELECT "currency", "sender_id", SUM(amount) 
FROM public.transfer
GROUP BY "currency", "sender_id"

But I don't know how to continue from this...?

Answer 1

Cross join two subqueries, like this:

SELECT a.user_id, a.currency, 
       COALESCE(b.total, 0) - COALESCE(a.total, 0) balance
  FROM (
          SELECT sender_id user_id, currency, SUM(amount) total
            FROM transfer
           GROUP BY sender_id, currency
       ) a
 CROSS JOIN (
          SELECT recipient_id user_id, currency, SUM(amount) total
            FROM transfer
           GROUP BY recipient_id, currency
       ) b  ON a.user_id = b.user_id AND a.currency = b.currency

The two subqueries get the SUMs you need for sender and recipient. COALESCE turns NULL values into zeros, to handle situations where somebody sent but never received or vice versa.

The CROSS JOIN takes all possible combinations of the two subqueries matching the ON filter.

If you must have this as a view, prefix the query with CREATE OR REPLACE VIEW my_balances AS. And, before you go to the trouble of making it into a materialized view, see whether it has such terrible performance that you have to materialize it.

This query will be helped by a couple of indexes:

CREATE INDEX sender_currency_amount ON transfer 
       (sender_id, currency) INCLUDE (amount);
CREATE INDEX recipient_currency_amount ON transfer 
       (recipient_id, currency) INCLUDE (amount);