select address from

optimism.core.dim_labels

where address= lower('0x99C9fc46f92E8a1c0deC1b1747d010903E884bE1')),

prices as (

select hour::date as day, symbol,token_address as token, decimals, avg(price) as price

from optimism.core.fact_hourly_token_prices

where day >= '2022-07-01'

and day <= '2022-07-31'

group by 1,2,3,4

union

select hour::date as day, symbol,token_address as token, decimals, avg(price) as price

from ethereum.core.fact_hourly_token_prices

where day >= '2022-07-01'

and day <= '2022-07-31'

group by 1,2,3,4

),

use_bridge as(

select

a.Tx_hash,

b.raw_amount,

a.block_timestamp::date as day,

a.contract_address

from ethereum.core.fact_event_logs a

join ethereum.core.fact_token_transfers b on a.tx_hash = b.tx_hash

where b.FROM_ADDRESS in (select address from address)

and day >= '2022-07-01'

and day <= '2022-07-31')

select

a.day,

symbol,

-- count (distinct tx_hash) as tx_count,

sum((raw_amount / power(10, decimals)) * price) as volume