select hour::date as day,

token_address,

symbol,

decimals,

avg (price) as USDPrice

from ethereum.core.fact_hourly_token_prices

group by 1,2,3,4

union ALL

select hour::date as day,

'0x8ac76a51cc950d9822d68b83fe1ad97b32cd580d' as token_address,

'Binance-Peg USDC' as symbol,

'18' as decimals,

1 as usdprice

from ethereum.core.fact_hourly_token_prices

group by 1,2,3,4),

maintable as (

select block_timestamp::Date as date,

tx_hash,

origin_from_address,

case when contract_address in ('0xeb466342c4d449bc9f53a865d5cb90586f405215','0xff970a61a04b1ca14834a43f5de4533ebddb5cc8','0xfab550568c688d5d8a52c7d794cb93edc26ec0ec','0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e','0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664','0x4268b8f0b87b6eae5d897996e6b845ddbd99adf3','0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48','0x750e4c4984a9e0f12978ea6742bc1c5d248f40ed','0x2791bca1f2de4661ed88a30c99a7a9449aa84174','0xfba3b7bb043415035220b1c44fb4756434639392') then 'USDC'

when contract_address in ('0x82af49447d8a07e3bd95bd0d56f35241523fbab1','0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab','0x2170ed0880ac9a755fd29b2688956bd959f933f8','0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2','0x7ceb23fd6bc0add59e62ac25578270cff1b9f619') then 'WETH'

when contract_address = '0x539bde0d7dbd336b79148aa742883198bbf60342' then 'MAGIC'

when contract_address = '0x8ac76a51cc950d9822d68b83fe1ad97b32cd580d' then 'Binance-Peg USDC'

when contract_address in ('0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9','0x9702230a8ea53601f5cd2dc00fdbc13d4df4a8c7','0xc7198437980c041c805a1edcba50c1ce5db95118','0xdac17f958d2ee523a2206206994597c13d831ec7','0xc2132d05d31c914a87c6611c10748aeb04b58e8f') then 'USDT'

when contract_address in ('0xfea7a6a0b346362bf88a9e4a88416b77a57d6c2a','0x130966628846bfd36ff31a822705796e8cb8c18d') then 'MIM'

when contract_address in ('0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f','0x50b7545627a5162f82a992c33b87adc75187b218') then 'WBTC'

when contract_address = '0x876ec6be52486eeec06bc06434f3e629d695c6ba' then 'FLUID'

when contract_address = '0xd4d42f0b6def4ce0383636770ef773390d85c61a' then 'SUHSHI'

when contract_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then 'WAVAX'

when contract_address = '0x60781c2586d68229fde47564546784ab3faca982' then 'PNG'

when contract_address in ('0xd586e7f844cea2f87f50152665bcbc2c279d8d70','0x6b175474e89094c44da98b954eedeac495271d0f','0x8f3cf7ad23cd3cadbd9735aff958023239c6a063') then 'DAI'

when contract_address = '0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd' then 'JOE'