#standardSQL

with double_entry_book as (
    -- debits
    select to_address as address, SAFE_CAST(value as NUMERIC) as value
    from bigquery-public-data.crypto_ethereum.token_transfers
    where to_address is not null
    and token_address = '0x514910771af9ca656af840dff83e8264ecf986ca'
    union all
    -- credits
    select from_address as address, -SAFE_CAST(value as NUMERIC) as value
    from bigquery-public-data.crypto_ethereum.token_transfers
    where from_address is not null
    and token_address = '0x514910771af9ca656af840dff83e8264ecf986ca'
)
, balances as (
  select address, sum(value) as balance
  from double_entry_book
  group by address
  order by balance desc
)
, chainlink_generals as (
  select address,balance
  from balances
  where balance > SAFE_CAST('1000000000000000000000000' as NUMERIC)
),
chainlink_wallets_non_link as(
  select trans.from_address as address from
  bigquery-public-data.crypto_ethereum.token_transfers trans
  where trans.token_address != '0x514910771af9ca656af840dff83e8264ecf986ca'
  group by trans.from_address
),
chainlink_wallets_1 as(
  select address, 0 as rank  from
  chainlink_generals
  where address not in (select address from chainlink_wallets_non_link)
),
chainlink_wallets_2 as(
  select min(trans.to_address) as address, 1 as rank ,min(trans.transaction_hash) as transaction, Concat(min(w1.address)," ; ", min(trans.to_address)) address_tree, Concat(" ; ", min(trans.transaction_hash)) as transaction_tree, count(*) as c from
  bigquery-public-data.crypto_ethereum.token_transfers trans, chainlink_wallets_1 w1
  where trans.from_address = w1.address
  and trans.to_address not in (select address from chainlink_wallets_non_link)
  and trans.token_address = '0x514910771af9ca656af840dff83e8264ecf986ca'
  group by trans.to_address
  order by c desc
),chainlink_wallets_3 as(
  select min(trans.to_address)  as address, 2 as rank, min(trans.transaction_hash) as transaction, Concat(min(w2.address_tree)," ; ", min(trans.to_address)) address_tree, Concat( min(w2.transaction_tree)," ; ", min(trans.transaction_hash)) as transaction_tree, count(*) as c from
  bigquery-public-data.crypto_ethereum.token_transfers trans, chainlink_wallets_2 w2
  where trans.from_address = w2.address
  and trans.to_address not in (select address from chainlink_wallets_non_link)
  and trans.token_address = '0x514910771af9ca656af840dff83e8264ecf986ca'
  group by trans.to_address
  order by c desc
),chainlink_wallets_4 as(
  select min(trans.to_address)  as address, 3 as rank, min(trans.transaction_hash) as transaction, Concat(min(w3.address_tree)," ; ", min(trans.to_address)) address_tree, Concat( min(w3.transaction_tree)," ; ", min(trans.transaction_hash)) as transaction_tree, count(*) as c from
  bigquery-public-data.crypto_ethereum.token_transfers trans, chainlink_wallets_3 w3
  where trans.from_address = w3.address
  and trans.to_address not in (select address from chainlink_wallets_non_link)
  and trans.token_address = '0x514910771af9ca656af840dff83e8264ecf986ca'
  group by trans.to_address
  order by c desc
),chainlink_wallets_5 as(
  select min(trans.to_address)  as address, 4 as rank, min(trans.transaction_hash) as transaction, Concat(min(w4.address_tree)," ; ", min(trans.to_address)) address_tree, Concat( min(w4.transaction_tree)," ; ", min(trans.transaction_hash)) as transaction_tree, count(*) as c from
  bigquery-public-data.crypto_ethereum.token_transfers trans, chainlink_wallets_4 w4
  where trans.from_address = w4.address
  and trans.to_address not in (select address from chainlink_wallets_non_link)
  and trans.token_address = '0x514910771af9ca656af840dff83e8264ecf986ca'
  group by trans.to_address
  order by c desc
),chainlink_wallets_6 as(
  select min(trans.to_address)  as address, 5 as rank, min(trans.transaction_hash) as transaction, Concat(min(w5.address_tree)," ; ", min(trans.to_address)) address_tree, Concat( min(w5.transaction_tree)," ; ", min(trans.transaction_hash)) as transaction_tree, count(*) as c from
  bigquery-public-data.crypto_ethereum.token_transfers trans, chainlink_wallets_5 w5
  where trans.from_address = w5.address
  and trans.to_address not in (select address from chainlink_wallets_non_link)
  and trans.token_address = '0x514910771af9ca656af840dff83e8264ecf986ca'
  group by trans.to_address
  order by c desc
)

,all_chainlink_dev_wallets as (
  select address,rank, "" as address_tree,"" as transaction_tree  from
  chainlink_wallets_1
  union all
  select address,rank, address_tree,transaction_tree from
  chainlink_wallets_2
  union all
  select address,rank, address_tree,transaction_tree from
  chainlink_wallets_3
  union all
  select address,rank, address_tree,transaction_tree from
  chainlink_wallets_4
  union all
  select address,rank, address_tree,transaction_tree from
  chainlink_wallets_5
  union all
  select address,rank, address_tree,transaction_tree  from
  chainlink_wallets_6
  )

select address,rank, address_tree,transaction_tree from all_chainlink_dev_wallets order by rank desc