from polygon.transactions

where to_address = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d'),

contract as (select contract_address, tx_id

from polygon.udm_events

where event_name = 'Approval' and event_type = 'event' and contract_address != '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619'

and tx_id in (select tx_id from raw)),

price as (select tx_id, sum(amount_usd) as Volume_USD

from polygon.udm_events

where amount_usd is not null and symbol in ('WETH', 'DAI') and tx_id in (select tx_id from contract)

and block_timestamp >= CURRENT_DATE - 7

group by 1)

select a.contract_address, case

when contract_address = '0xa5f1ea7df861952863df2e8d1312f7305dabf215' then 'ZED Horse'

when contract_address = '0x8634666ba15ada4bbc83b9dbf285f73d9e46e4c2' then 'Chicken Derby'

when contract_address = '0xfd12ec7ea4b381a79c78fe8b2248b4c559011ffb' then 'CryptoRaiders'

when contract_address = '0x1f5567d9d3efa79c4b03beb42491344111efbdf5' then 'WhelpsNFT'

when contract_address = '0x2ad64e95eff639fbd0273bcd4d0eeb72a60c8586' then 'MobsKeys'

when contract_address = '0x0a0bf65248805efa926c39bf51b6dd94e3d1a7af' then 'Visitor'

when contract_address = '0xbccaa7acb552a2c7eb27c7eb77c2cc99580735b9' then 'Artvatars'

when contract_address = '0x9498274b8c82b4a3127d67839f2127f2ae9753f4' then 'PolygonPunks'

when contract_address = '0xe7e16f2da731265778f87cb8d7850e31b84b7b86' then 'Embersword Land'

when contract_address = '0x82bbf7be0eb9a6024b7a641ba179e00812bdae53' then 'Riot Racers Cars'

else concat('unknown-',a.contract_address) end as Project_name,

sum(b.volume_usd) as Volume_USD,

count(distinct b.tx_id) as Trade_count

from contract a

join price b on a.tx_id = b.tx_id

group by 1,2

order by 3 DESC

limit 10