with data1 as (select

date_trunc('hour', block_timestamp) as hour,

count(distinct(tx_id)) as n_swaps,

count(distinct(trader)) as swappers,

from_currency as token1,

sum(iff(from_currency = token1, from_amount/1e6, to_amount/1e6)) as token1_amount

from osmosis.core.fact_swaps

where hour >= current_date() - 68

and hour < '2022-10-24'

group by hour, token1),

data2 as (select

date_trunc('hour', block_timestamp) as hour,

count(distinct(tx_id)) as n_swaps,

count(distinct(trader)) as swappers,

to_currency as token2,

sum(iff(from_currency = token2, from_amount/1e6, to_amount/1e6)) as token2_amount

from osmosis.core.fact_swaps

where hour >= current_date() - 68

and hour < '2022-10-24'

group by hour, token2),

total_per_token as (

select

d.hour,

d.n_swaps + f.n_swaps as total_swaps,

d.swappers + f.swappers as total_swappers,

d.token1 as token_address,

l.project_name as token,

d.token1_amount + f.token2_amount as total_sum

from data1 d, data2 f, osmosis.core.dim_labels l

where d.token1 = f.token2