(

select

recorded_at::date as "date",

symbol as stars_symbol,

avg(price) as stars_price,

avg(volume_24h) as stars_volume

from osmosis.core.dim_prices

where 1=1

and symbol='STARS'

-- and recorded_at > '2022-09-12'

group by "date",stars_symbol

),

tab as

(

select

fs.*,

case

when from_currency = 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4' then (fs.from_amount*p.stars_price/1e6)

when to_currency = 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4' then (fs.to_amount*p.stars_price/1e6)

end as usd

from osmosis.core.fact_swaps fs

inner join stars_price p

on fs.block_timestamp::date=p."date"

where 1=1

and (from_currency = 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4'

or to_currency = 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4')

and fs.block_timestamp >= '2022-09-26'

-- and fs.block_timestamp >= '2022-09-12'

)

select concat(dl.label,' to ',dl2.label) as currency, sum(sv.usd) as swap_volume

from tab sv

left join osmosis.core.dim_labels dl on sv.from_currency=dl.address

left join osmosis.core.dim_labels dl2 on sv.to_currency=dl2.address