xRapid - process inter-exchange payments

process_inter_exchange_payments.sql

WITH step1 AS (
  SELECT
    CloseTime AS Time, CloseTimeTimestamp AS Timestamp, LedgerIndex, Account, Destination, SourceTag, DestinationTag, Fee, Amount,
    CONCAT(xSource, "->", xDestination) AS xLeg, xSource, xDestination
  FROM xRapid.InterExchange),
step2 AS (
  SELECT
    Time, Timestamp, LedgerIndex, Account, Destination, SourceTag, DestinationTag, Fee, Amount, xLeg, xSource, xDestination,
    LEAD(Amount, 1, 0) OVER (PARTITION BY xLeg, DestinationTag ORDER BY LedgerIndex DESC) AS leadAmount,
    LEAD(Timestamp, 1, 0) OVER (PARTITION BY xLeg, DestinationTag ORDER BY LedgerIndex DESC) AS leadTimestamp,
    LAG(Amount, 1, 0) OVER (PARTITION BY xLeg, DestinationTag ORDER BY LedgerIndex DESC) AS lagAmount,
    LAG(Timestamp, 1, 0) OVER (PARTITION BY xLeg, DestinationTag ORDER BY LedgerIndex DESC) AS lagTimestamp 
  FROM step1)
SELECT
  Time, Timestamp, LedgerIndex, Account, Destination, SourceTag, DestinationTag, Fee, Amount, xLeg, xSource, xDestination,
  CASE WHEN ABS(Amount-leadAmount) < ABS(Amount-lagAmount) THEN ABS(Amount-leadAmount) ELSE ABS(Amount-lagAmount) END AS dA,
  CASE WHEN ABS(Timestamp-leadTimeStamp) < ABS(Timestamp-leadTimeStamp) THEN ABS(Timestamp-leadTimeStamp) ELSE ABS(Timestamp-leadTimeStamp) END AS dT
FROM step2
ORDER BY LedgerIndex DESC, xLeg