directpay.sql

BEGIN;

    CREATE TEMP TABLE our_exchanges AS
        SELECT *
          FROM exchanges
         WHERE "timestamp" >= (SELECT ts_start FROM paydays ORDER BY ts_start DESC LIMIT 1)
           AND "timestamp" < (SELECT ts_end FROM paydays ORDER BY ts_end DESC LIMIT 1);

    CREATE TEMP TABLE our_charges AS
        SELECT *
          FROM our_exchanges
         WHERE amount > 0;

    CREATE TEMP TABLE our_transfers AS
        SELECT *
          FROM transfers
         WHERE "timestamp" >= (SELECT ts_start FROM paydays ORDER BY ts_start DESC LIMIT 1)
           AND "timestamp" < (SELECT ts_end FROM paydays ORDER BY ts_end DESC LIMIT 1);

    CREATE TEMP TABLE single_transfers AS
        SELECT tipper, min(tippee) AS tippee, min(amount) AS amount
          FROM our_transfers
      GROUP BY tipper
        HAVING count(*) = 1;

    -- Direct payments from a credit card to a bank account
    SELECT status, count(*), sum(t.amount)
      FROM our_charges c
      JOIN single_transfers t ON t.tipper = c.participant
     WHERE t.amount = c.amount
       AND NOT EXISTS (
               SELECT *
                 FROM our_transfers t
                WHERE t.tippee = c.participant
           )
       AND EXISTS (
               SELECT *
                 FROM our_exchanges e
                WHERE e.participant = t.tippee
                  AND amount < 0
           )
  GROUP BY status;

    -- All credit card charges
    SELECT status, count(*), sum(amount)
      FROM our_charges
  GROUP BY status;

ROLLBACK;