select date_trunc(day,hour) date,avg(price) price,symbol

from flipside_prod_db.ethereum_core.fact_hourly_token_prices

where hour >= dateadd('Month',-6, CURRENT_DATE)

and (symbol='USDC' OR symbol='USDT' OR symbol='DAI' OR symbol='MATIC' OR symbol='WBTC' OR symbol='WETH')

group by date,symbol),

B as( select

date_trunc('day', block_timestamp) as block_day,

count(TX_HASH) as Number_Of_Tx,

count(distinct(ORIGIN_FROM_ADDRESS)) as Unique_Users,

case

when ORIGIN_TO_ADDRESS = lower('0x3666f603Cc164936C1b87e207F36BEBa4AC5f18a') then 'USDC'

when ORIGIN_TO_ADDRESS = lower('0x3E4a3a4796d16c0Cd582C382691998f7c06420B6') then 'USDT'

when ORIGIN_TO_ADDRESS = lower('0x3d4Cc8A61c7528Fd86C55cfe061a78dCBA48EDd1') then 'DAI'

when ORIGIN_TO_ADDRESS = lower('0x22B1Cbb8D98a01a3B71D034BB899775A76Eb1cc2') then 'MATIC'

when ORIGIN_TO_ADDRESS = lower('0xb98454270065A31D71Bf635F6F7Ee6A518dFb849') then 'WBTC'

when ORIGIN_TO_ADDRESS =lower('0xb8901acB165ed027E32754E0FFe830802919727f') then 'WETH'

end as symbol,

SUM(EVENT_INPUTS:amount) as amount

from ethereum.core.fact_event_logs

where ( ORIGIN_TO_ADDRESS = lower('0x3666f603Cc164936C1b87e207F36BEBa4AC5f18a')

OR ORIGIN_TO_ADDRESS = lower('0x3E4a3a4796d16c0Cd582C382691998f7c06420B6')

OR ORIGIN_TO_ADDRESS = lower('0x3d4Cc8A61c7528Fd86C55cfe061a78dCBA48EDd1')

OR ORIGIN_TO_ADDRESS = lower('0x22B1Cbb8D98a01a3B71D034BB899775A76Eb1cc2')

OR ORIGIN_TO_ADDRESS = lower('0xb98454270065A31D71Bf635F6F7Ee6A518dFb849')

OR ORIGIN_TO_ADDRESS =lower('0xb8901acB165ed027E32754E0FFe830802919727f') )

and event_name = 'TransferSentToL2'

and TX_STATUS='SUCCESS'

and block_day >= current_date -180

group by block_day ,symbol ),

C as(select B.block_day, B.symbol, B.NUMBER_OF_Tx, UNIQUE_USERS,

case

when B.symbol='USDC' then B.amount/1e6

when B.symbol='USDT' then B.AMOUNT/1E6