Sample Queries

This section provides ready-to-use SQL queries for common reconciliation and analysis use cases.

Basic queries

Today's bank deposits

SELECT TRANSFER_ID, AMOUNT, CURRENCY_ISO_CODE,  
       DISBURSEMENT_METHOD, STATUS, 
       DESTINATION_ACCOUNT_LAST_2, 
       TRANSFER_DESCRIPTOR 
FROM transfer_v1 
WHERE DISBURSEMENT_DATE = CURRENT_DATE 
ORDER BY AMOUNT DESC;

Transaction volume by payment method

SELECT PAYMENT_METHOD, PAYMENT_NETWORK, 
       COUNT(*) as transaction_count, 
       SUM(SETTLEMENT_AMOUNT) as total_volume, 
       AVG(SETTLEMENT_AMOUNT) as avg_transaction 
FROM transaction_disbursement_v1 
WHERE DISBURSEMENT_DATE = CURRENT_DATE 
GROUP BY PAYMENT_METHOD, PAYMENT_NETWORK 
ORDER BY total_volume DESC;

Reconciliation queries

Complete transfer breakdown

Trace transfers from transactions through fees to the final bank deposit:

WITH transfer_summary AS ( 
  SELECT t.TRANSFER_ID, t.AMOUNT as total_transfer, 
         t.DISBURSEMENT_DATE, t.STATUS, 
         COUNT(DISTINCT td.TRANSACTION_ID) as num_transactions, 
         SUM(td.SETTLEMENT_AMOUNT) as total_transaction_amount, 
         SUM(f.AMOUNT) as total_fees, 
         SUM(COALESCE(dd.AMOUNT, 0)) as total_dispute_impact 
  FROM transfer_v1 t 
  LEFT JOIN transaction_disbursement_v1 td ON t.TRANSFER_ID = td.TRANSFER_ID 
  LEFT JOIN fee_v1 f ON t.TRANSFER_ID = f.TRANSFER_ID 
  LEFT JOIN dispute_disbursement_v1 dd ON t.TRANSFER_ID = dd.TRANSFER_ID 
  WHERE t.DISBURSEMENT_DATE = CURRENT_DATE 
  GROUP BY t.TRANSFER_ID, t.AMOUNT, t.DISBURSEMENT_DATE, t.STATUS 
) 
SELECT TRANSFER_ID, total_transfer, num_transactions, 
       total_transaction_amount, total_fees, total_dispute_impact, 
       (total_transaction_amount - total_fees + total_dispute_impact) as calculated_net 
FROM transfer_summary 
ORDER BY total_transfer DESC;

Daily reconciliation report

Expected result: the value in reconciliation_difference should be zero.

SELECT t.DISBURSEMENT_DATE, 
       COUNT(DISTINCT t.TRANSFER_ID) as num_transfers, 
       COUNT(DISTINCT td.TRANSACTION_ID) as num_transactions, 
       SUM(td.SETTLEMENT_AMOUNT) as gross_transaction_amount, 
       SUM(f.AMOUNT) as total_fees, 
       SUM(COALESCE(dd.AMOUNT, 0)) as dispute_adjustments, 
       (SUM(td.SETTLEMENT_AMOUNT) - SUM(f.AMOUNT) + SUM(COALESCE(dd.AMOUNT, 0)))  
           as calculated_net, 
       SUM(t.AMOUNT) as actual_transfer_total, 
       (SUM(t.AMOUNT) - (SUM(td.SETTLEMENT_AMOUNT) - SUM(f.AMOUNT)  
           + SUM(COALESCE(dd.AMOUNT, 0)))) as reconciliation_difference 
FROM transfer_v1 t 
LEFT JOIN transaction_disbursement_v1 td ON t.TRANSFER_ID = td.TRANSFER_ID 
LEFT JOIN fee_v1 f ON t.TRANSFER_ID = f.TRANSFER_ID 
LEFT JOIN dispute_disbursement_v1 dd ON t.TRANSFER_ID = dd.TRANSFER_ID 
WHERE t.DISBURSEMENT_DATE = CURRENT_DATE 
GROUP BY t.DISBURSEMENT_DATE;