Find accounts with most account activations (XRPL, BigQuery)

For_ Craig_DeWitt.sql

-- Remove the surrounding Q3 for a complete list of account activations

SELECT
  Account,
  COUNT(1) as AccountsActivated
FROM (
  SELECT
    Q1.*,
    Q2.Account,
    Q2.hash
  FROM (
    SELECT
      Destination as ActivatedAccount,
      MIN(LedgerIndex) as ActivatedInLedger
    FROM
      xrpledgerdata.fullhistory.transactions 
    GROUP BY
      Destination
  ) Q1
  LEFT JOIN
    xrpledgerdata.fullhistory.transactions Q2 ON (
      Q1.ActivatedInLedger = Q2.LedgerIndex
      AND
      Q1.ActivatedAccount = Q2.Destination
    )
) Q3
GROUP BY 
  Account
ORDER BY
  COUNT(1) DESC