Last active
February 17, 2019 13:30
-
-
Save hmatejx/b5b6d6f791144ad3a9b975b90f61795c to your computer and use it in GitHub Desktop.
xRapid - process inter-exchange payments
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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