Skip to content

Instantly share code, notes, and snippets.

@WietseWind
Last active September 27, 2018 14:23
Show Gist options
  • Save WietseWind/b21bf6289a19237cfbca921cd62e29b8 to your computer and use it in GitHub Desktop.
Save WietseWind/b21bf6289a19237cfbca921cd62e29b8 to your computer and use it in GitHub Desktop.
Google BigQuery, Top 5 Transactions / Account
SELECT
COUNT(1) as TxCount,
MIN(LedgerIndex) as MinLedger,
MAX(LedgerIndex) as MaxLedger,
COUNT(DISTINCT LedgerIndex) as LedgersWithTxCount
FROM
xrpledgerdata.fullhistory.transactions
SELECT
STRING_AGG(DISTINCT TransactionType) as TransactionType,
Account,
COUNT(Destination) as TxCount,
SUM(AmountXRP) as SumXRPDrops,
SUM(Fee) as SumFeeDrops,
MIN(LedgerIndex) as AccMinLedger,
MAX(LedgerIndex) as AccMaxLedger
FROM
xrpledgerdata.fullhistory.transactions
WHERE
AmountXRP IS NOT NULL
AND
TransactionType = 'Payment'
GROUP BY
Account
ORDER BY
COUNT(Destination) DESC
LIMIT 5
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment