superflyUntitled Query
    Updated 2022-08-22
    with users as (select DISTINCT ORIGIN_FROM_ADDRESS as "Unique Users"
    from optimism.core.fact_token_transfers
    where ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
    and BLOCK_TIMESTAMP > CURRENT_DATE - 14),
    games as( select CONTRACT_ADDRESS as "Game", count( ORIGIN_FROM_ADDRESS) as User,
    case
    when CONTRACT_ADDRESS in ( '0x9a0475765a234e628a90e8113a170abce4d5bfd7', '0x82483e5095d1244bbc4391add3feb213b6953a1f', '0xc40c30d1e96e6d925357fe2289f33ce6793e05b5') then 'Manchester United vs Brighton & Hove Albion'
    when CONTRACT_ADDRESS in ('0x7088db05486a279429256fe75ec5cfcf3d791d17', '0x707c42601cf9ea937fcc4c6cf711d3e5b79df2b3', '0x138e39f539b2b683a8ac6fbdd3470431cf28c33e') then 'Brentford FC vs Manchester United'
    when CONTRACT_ADDRESS in ( '0xdac4a4e7bc112908dc9e66e962feb928fc0357db', '0x8997330f71bb9cc1606cef5de7291f6d30c44917', '0x42e0b68055e663abd699150b355e236385979190') then 'West Ham United vs Manchester City'
    when CONTRACT_ADDRESS in ( '0x33aada93773749f1098957e58b921fc9621f5bc3', '0xdbb65ecb08b6cb8a70045ee37ea66f9f735c0d3e', '0x402cfa85076bf2c3a57994ec1f17524e19df4b9e') then 'Arsenal FC vs Leicester City'
    when CONTRACT_ADDRESS in ( '0x7d46f148bf1deaa4fd2cbd96abf3ec204477983c', '0x16c966add954844773483056c3cec0c5f4098429', '0x37e681b81d4602c2489c0a0a16ffbdb3e9497a57') then 'Getafe vs Atletico Madrid'
    when CONTRACT_ADDRESS in ( '0xe2d6086b602b896c7161af2bf34019311eedb4a5', '0x7cbf2992beca7e27840297c9b84bd0c21188be21', '0xdd433e355c4582320eb6f5c0372f0e03ca45717c') then 'Liverpool vs Crystal Palace'
    when CONTRACT_ADDRESS in ( '0xdaf532113547ff62018f31b7fb7e4bc255515fb9', '0x0c3fbbdda44e02879ffa9c9868c96338b98c3727', '0xb5a3db8d284bb9248e76382dcfe119376c2f9282') then 'Chelsea vs Tottenham Hotspur'
    when CONTRACT_ADDRESS in ( '0x4a5cff1f31c5035504e76638609dec9e440cbb2a', '0x9a031da92b41431e33ea0c8961c7fcc7e241ee9f', '0x1e6310613808c3a333e34c4a6ecf414119bccacb') then 'Leicester City vs Brentford FC'
    when CONTRACT_ADDRESS in ( '0xe6862674f66dae6d79f19aaf3743e10d043278da', '0x507a7705d7d7174b8b27878143d7ed1772a11bb8', '0xa147e8b87fbc3e160363c8e5dc313eb3690a8458') then 'Almería vs Real Madrid'
    when CONTRACT_ADDRESS in ( '0x24058e4058a7e8842bb1d90630d19ae541b52ce4', '0xdc95a723d333f3cb52ce46f7b20a9f2c0a8328ef', '0x6bcc5f84837b16f2b8956c72891b205a49824c83') then 'Brighton & Hove Albion vs Newcastle United'

    End as Name
    from optimism.core.fact_token_transfers
    where ORIGIN_FROM_ADDRESS in ( select "Unique Users" from users )
    and ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
    and FROM_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
    and TO_ADDRESS in ( select "Unique Users" from users )
    and BLOCK_TIMESTAMP > CURRENT_DATE - 14
    and contract_address != '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' --sUSD contract
    and name is not NULL
    group by 1
    order by 2 desc)

    select name, sum(user) as users
    from games
    group by 1
    order by 2 desc
    limit 10
    Run a query to Download Data