(

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)*-1

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-12' and fs.block_timestamp < '2022-09-26'

)

select

pool_ids,

sum(usd) as swap_volume from tab

group by pool_ids

order by swap_volume desc