select date_trunc('day',block_timestamp) as day,

avg ((to_amount/1e6)/(from_amount/1e6)) as AXLPrice

from osmosis.core.fact_swaps

where from_currency = 'ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E' -- AXL

and to_currency = 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858' --USDC

and to_amount > 0

and from_amount > 0

and tx_succeeded = 'TRUE'

group by 1)

select date_trunc('day',block_timestamp) as date,

'Swap From AXL' as swap_type,

count (distinct tx_id) as swaps,

count (distinct trader) as swappers,

sum (from_amount/1e6) as AXL_Volume,

sum ((from_amount/1e6)*axlprice) as USD_Volume,

avg (from_amount/1e6) as AVG_AXL_Volume,

avg ((from_amount/1e6)*axlprice) as AVG_USD_Volume

from osmosis.core.fact_swaps t1

join price t2 on t1.block_timestamp::date = t2.day

where from_currency = 'ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E'

and tx_succeeded = 'TRUE'

group by 1,2

union ALL

select date_trunc('day',block_timestamp) as date,

'Swap TO AXL' as swap_type,

count (distinct tx_id) as swaps,

count (distinct trader) as swappers,

sum (to_amount/1e6) as AXL_Volume,

sum ((to_amount/1e6)*axlprice) as USD_Volume,

avg (to_amount/1e6) as AVG_AXL_Volume,

avg ((to_amount/1e6)*axlprice) as AVG_USD_Volume

from osmosis.core.fact_swaps t1