HOUR::date as day,

case

when symbol = 'WETH' then '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7'

when symbol = 'USDT' then '0x068f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8'

when symbol = 'USDC' then '0x053c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8'

when symbol = 'DAI' then '0x00da114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3'

when symbol = 'WBTC' then '0x03fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac'

when symbol = 'wstETH' then '0x042b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2'

end as token_address,

case when symbol = 'WETH' then 'ETH' else SYMBOL end as token,

DECIMALS,

avg(PRICE) as usd

from ethereum.price.ez_hourly_token_prices

where hour>='2023-07-01'

and SYMBOL in ('USDT','USDC','DAI','WBTC','WETH','wstETH')

GROUP by 1,2,3,4)

date_trunc('day',TIMESTAMP) as days,

count(distinct tx_hash) as txs,

count(distinct CONTRACT) as users,

sum(usd_amount) as amount

from (

SELECT

s.TIMESTAMP,

s.tx_hash,

s.CONTRACT,

(PARAMETERS[2]:value[0]:value::string)/pow(10,DECIMALS) as amount,

amount*usd as usd_amount

from external.tokenflow_starknet.decoded_events s

left join pricet p on s.timestamp::date=p.day and s.contract= p.token_address

where s.CHAIN_ID = 'mainnet'

and s.TIMESTAMP < current_date

and name = 'Transfer'