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 ( '0x7cf58bcae46e63ac96cacac0be9095893d1fae4d', '0xeb0937e6d94b748551c8232e19f4e1fa2dbd12f9') then 'Baltimore Orioles vs Boston Red Sox'

when CONTRACT_ADDRESS in ('0x7088db05486a279429256fe75ec5cfcf3d791d17', '0x707c42601cf9ea937fcc4c6cf711d3e5b79df2b3', '0x138e39f539b2b683a8ac6fbdd3470431cf28c33e') then 'Brentford FC vs Manchester United'

when CONTRACT_ADDRESS in ( '0x1a1006b31f91877a11890e88c683c74bbca38113', '0x2a077642224ade2b2b358dc429be4e3dff1e3dac', '0x4459b42f3e3966546bd10f4442a4e50d8062179c') then 'Newcastle 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 ( '0x99b3179049b60150ae0c1367a2dc4eafdb7ede51', '0x7e72e2fa72ed0837bf9bc9646c0a65e6b027b8a0', '0xcf0f8a1b7621f6caebae26c2c3fef26e029723c0') then 'Bournemouth vs Arsenal FC'

when CONTRACT_ADDRESS in ( '0x3c2adb8b24b7cf1c40319f963ab8e36199c57a75', '0xf06fcc6c4ed15811ad2c7becbb15c2a3086104f3', '0x333f824c32b11cd4fbd4b044f3bdd915ae778eac') then 'Manchester United vs Liverpool'

when CONTRACT_ADDRESS in ( '0x24058e4058a7e8842bb1d90630d19ae541b52ce4', '0xdc95a723d333f3cb52ce46f7b20a9f2c0a8328ef', '0x6bcc5f84837b16f2b8956c72891b205a49824c83') then 'Brighton & Hove Albion vs Newcastle United'

else CONTRACT_ADDRESS

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'

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