token_recommendation_algo_bigquery.sql

token_recommendation_algo_bigquery.sql

#standardSQL
with top_tokens as (
  select token_address, count(1) as transfer_count
  from `bigquery-public-data.crypto_ethereum.token_transfers` as token_transfers
  group by token_address
  order by transfer_count desc
  limit 1000
),
token_balances as (
    with double_entry_book as (
        select token_address, to_address as address, cast(value as float64) as value, block_timestamp
        from `bigquery-public-data.crypto_ethereum.token_transfers`
        union all
        select token_address, from_address as address, -cast(value as float64) as value, block_timestamp
        from `bigquery-public-data.crypto_ethereum.token_transfers`
    )
    select double_entry_book.token_address, address, sum(value) as balance
    from double_entry_book
    join top_tokens on top_tokens.token_address = double_entry_book.token_address
    where address != '0x0000000000000000000000000000000000000000'
    group by token_address, address
    having balance > 0
),
token_supplies as (
    select token_address, sum(balance) as supply
    from token_balances
    group by token_address
)
select 
    token_balances.token_address, 
    token_balances.address as user_address, 
    balance/supply * 100 as rating
from token_balances
join token_supplies on token_supplies.token_address = token_balances.token_address
where balance/supply * 100 > 0.001