Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
xRapid - process inter-exchange payments
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.