osmo_prices as (

SELECT

trunc(recorded_at,'day') as day,

symbol,

address,

avg(price) AS price

FROM osmosis.core.dim_prices

JOIN osmosis.core.dim_tokens ON symbol = project_name

GROUP BY 1, 2, 3

SELECT

trunc(x.block_timestamp,'day') as date,

z.symbol,

count(DISTINCT x.tx_id) AS transactions,

sum(transactions) over (partition by z.symbol order by date) as total_transactions,

count(distinct sender) as osmo_senders,

count(distinct receiver) as quasar_receivers,

SUM(parse_json(attribute_value):"amount"/pow(10,decimal)*price) as volume,

sum(volume) over (partition by z.symbol order by date) as total_volume,

avg(parse_json(attribute_value):"amount"/pow(10,decimal)*price) as avg_volume

FROM osmosis.core.fact_msg_attributes x

JOIN osmosis.core.fact_transfers y ON x.tx_id = y.tx_id

JOIN osmo_prices z ON z.address = y.currency AND day = trunc(x.block_timestamp,'day')

WHERE x.attribute_key = 'packet_data'

AND y.transfer_type = 'IBC_TRANSFER_OUT'

AND y.sender LIKE '%osmo%' AND y.receiver LIKE '%quasar%'

AND SUBSTRING(parse_json(attribute_value):"receiver",1,6) LIKE '%quasar%'

AND SUBSTRING(parse_json(attribute_value):"sender",1,4) LIKE '%osmo%'

GROUP BY 1, 2

ORDER BY 1 ASC, 2