Merchant Payouts & AP/AR Flows

SELECT chain, DATE_TRUNC('month', activity_date) AS month, population,
       SUM(total_usd) AS usd, COUNT(*) AS runs
FROM stablecoins.intelligence.merchant_payout_runs
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

SELECT *
FROM stablecoins.intelligence.merchant_payout_runs
WHERE disbursement_scale = 'consumer_scale'
  AND ticket_mode_share >= 0.5
  AND mode_day_of_week_iso BETWEEN 1 AND 5
  AND NOT is_round_the_clock;

SELECT chain, DATE_TRUNC('month', activity_date) AS month, ticket_band,
       SUM(usd_amount) AS usd
FROM stablecoins.intelligence.apar_flows
WHERE is_established_relationship
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

SELECT activity_date, usd_amount, transfers_180d,
       avg_interval_days_180d, settlement_pattern
FROM stablecoins.intelligence.apar_flows
WHERE chain = :chain AND from_address = :payer AND to_address = :payee
ORDER BY block_timestamp;

SELECT receiving_entity,
       COUNT(DISTINCT from_address) AS payers,
       SUM(usd_amount) AS usd
FROM stablecoins.intelligence.apar_flows
WHERE is_established_relationship AND receiving_entity IS NOT NULL
GROUP BY 1
ORDER BY usd DESC
LIMIT 25;