Monthly data from XRPL

MonthlyQuery.sql

WITH
  ledgersByPeriod AS ( # Get first and last ledger index per month
  SELECT
    MIN(ledgers.LedgerIndex) AS firstLedgerIndex,
    MAX(ledgers.LedgerIndex) AS lastLedgerIndex,
    DATETIME_TRUNC(CloseTime,
      MONTH) AS Period
  FROM
    xrpledgerdata.fullhistory.ledgers
  GROUP BY
    Period),
  transactionsStepOne AS ( # Get all wallets receiving XRP
  SELECT
    Period,
    Destination AS Wallet,
    firstLedgerIndex,
    lastLedgerIndex,
    COUNT(*) AS ReceivedCount,
    SUM(AmountXRP) / 1000000 AS ReceivedAmount
  FROM
    ledgersByPeriod AS ledgers,
    xrpledgerdata.fullhistory.transactions AS transactions
  WHERE
    transactions.LedgerIndex >= ledgers.firstLedgerIndex
    AND transactions.LedgerIndex <= ledgers.lastLedgerIndex
    AND TransactionType = "Payment"
    AND TransactionResult = "tesSUCCESS"
    AND AmountXRP IS NOT NULL
    AND Account != Destination
    AND SendMaxDEX.value IS NULL # Excludes a few payments that makes results unreliable
  GROUP BY
    Period,
    firstLedgerIndex,
    lastLedgerIndex,
    Destination),
  transactionsStepTwo AS ( # Get all wallets sending XRP
  SELECT
    Period,
    Account AS Wallet,
    firstLedgerIndex,
    lastLedgerIndex,
    COUNT(*) AS SentCount,
    SUM(AmountXRP) / 1000000 AS SentAmount,
    SUM(Fee) / 1000000 AS BurnedAmount
  FROM
    ledgersByPeriod AS ledgers,
    xrpledgerdata.fullhistory.transactions AS transactions
  WHERE
    transactions.LedgerIndex >= ledgers.firstLedgerIndex
    AND transactions.LedgerIndex <= ledgers.lastLedgerIndex
    AND TransactionType = "Payment"
    AND TransactionResult = "tesSUCCESS"
    AND AmountXRP IS NOT NULL
    AND Account != Destination
    AND SendMaxDEX.value IS NULL # Excludes a few payments that makes results unreliable
  GROUP BY
    Period,
    firstLedgerIndex,
    lastLedgerIndex,
    Account),
  transactionsStepThree AS ( # Join the sending and receiving wallets and add data
  SELECT
    Wallet,
    Period,
    IFNULL(ReceivedCount,0) AS ReceivedCount,
    IFNULL(ReceivedAmount,0) AS ReceivedAmount,
    IFNULL(SentCount,0) AS SentCount,
    IFNULL(SentAmount,0) AS SentAmount,
    IFNULL(BurnedAmount,0) AS BurnedAmount,
    IFNULL(ReceivedAmount,0) - IFNULL(SentAmount,0) AS Balance,
    IFNULL(ReceivedCount,0) + IFNULL(SentCount,0) AS TotalCount,
    MAX(IFNULL(SentCount,0)) OVER (PARTITION BY Period) AS MaxSentCount,
    MIN(IFNULL(SentCount,0)) OVER (PARTITION BY Period) AS MinSentCount,
    MAX(IFNULL(ReceivedCount,0)) OVER (PARTITION BY Period) AS MaxReceivedCount,
    MIN(IFNULL(ReceivedCount,0)) OVER (PARTITION BY Period) AS MinReceivedCount,
    MAX(IFNULL(ReceivedCount,0) + IFNULL(SentCount,0)) OVER (PARTITION BY Period) AS MaxTotalCount,
    MIN(IFNULL(ReceivedCount,0) + IFNULL(SentCount,0)) OVER (PARTITION BY Period) AS MinTotalCount,
    MAX(IFNULL(ReceivedAmount,0) - IFNULL(SentAmount,0)) OVER (PARTITION BY Period) AS MaxBalance,
    MIN(IFNULL(ReceivedAmount,0) - IFNULL(SentAmount,0)) OVER (PARTITION BY Period) AS MinBalance
  FROM
    transactionsStepOne
  FULL OUTER JOIN
    transactionsStepTwo
  USING
    (Wallet,Period)),
  transactionsStepFour AS ( # Add more data
  SELECT
    *,
    IF(
      Balance / IF(SentAmount != 0,SentAmount,1) > 1,
      1,
      IF(
        Balance / IF(SentAmount != 0,SentAmount,1) < -1,
        -1,
        Balance / IF(SentAmount != 0,SentAmount,1)
      )
    ) AS AccumulationDistributionBias,
    # Normalized between 0 and 1
    IFNULL(NULLIF((SentCount - MinSentCount),0) / (MaxSentCount - MinSentCount),0) AS SentActivityIndex,
    IFNULL(NULLIF((ReceivedCount - MinReceivedCount),0) / (MaxReceivedCount - MinReceivedCount),0) AS ReceivedActivityIndex,
    IFNULL(NULLIF((TotalCount - MinTotalCount),0) / (MaxTotalCount - MinTotalCount),0) AS TotalActivityIndex,
    IFNULL(NULLIF((Balance - MinBalance),0) / (MaxBalance - MinBalance),0) AS BalanceIndex
  FROM
    transactionsStepThree)
SELECT # Group by period and sum/avg the fields
  Period,
  COUNT(*) AS Wallets,
  SUM(ReceivedCount) AS Transactions,
  SUM(ReceivedAmount) AS Amount,
  SUM(BurnedAmount) AS Burned,
  AVG(AccumulationDistributionBias) AS AverageAccumulationDistributionBias,
  AVG(SentActivityIndex) AS AverageSentActivityIndex,
  AVG(ReceivedActivityIndex) AS AverageReceivedActivityIndex,
  AVG(TotalActivityIndex) AS AverageTotalActivityIndex,
  AVG(BalanceIndex) AS AverageBalanceIndex,
  SUM(IF(Balance > 0,Balance,0)) AS Circulating,
  IF(SUM(ReceivedAmount) = 0 OR SUM(IF(Balance > 0,Balance,0)) = 0,
    0,
    SUM(ReceivedAmount) / SUM(IF(Balance > 0,Balance,0))) AS ReuseRatio,
  AVG(Balance) AS AverageBalance
FROM
  transactionsStepFour
# WHERE
#   SentActivityIndex < 0.2 AND ReceivedActivityIndex < 0.2 AND TotalActivityIndex < 0.2 AND BalanceIndex < 0.8 # Filter the results
GROUP BY
  Period
ORDER BY
  Period ASC