SELECT 'ibc/0CD3A0285E1341859B5E86B6AB7682F023D03E97607CCC1DC95706411D866DF7' AS ibc, 'DAI' AS label UNION

SELECT 'ibc/F292A17CF920E3462C816CBE6B042E779F676CAB59096904C4C1C966413E3DF5' AS ibc, 'DAI' AS label UNION

SELECT 'ibc/8242AD24008032E457D2E12D46588FD39FB54FB29680C6C7663D296B383C37C4' AS ibc, 'USDT' AS label UNION

SELECT 'ibc/9F9B07EF9AD291167CF5700628145DE1DEB777C2CFC7907553B24446515F6D0E' AS ibc, 'USDC' AS label UNION

SELECT 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858' AS ibc, 'USDC' AS label UNION

SELECT 'ibc/BE1BB42D4BE3C30D50B68D7C41DB4DFCE9678E8EF8C539F6E6A9345048894FCC' AS ibc, 'USTC' AS label

),

from_swaps AS (

SELECT label, sum(from_amount) / POWER(10, from_decimal) AS volume, DATE_TRUNC('day', block_timestamp) AS time

FROM osmosis.core.fact_swaps

JOIN stablecoins ON from_currency = ibc

WHERE time > TO_DATE('2022-04-01')

GROUP BY from_decimal, label, time

),

to_swaps AS (

SELECT label, sum(to_amount) / POWER(10, to_decimal) AS volume, DATE_TRUNC('day', block_timestamp) AS time

FROM osmosis.core.fact_swaps

JOIN stablecoins ON to_currency = ibc

WHERE time > TO_DATE('2022-04-01')

GROUP BY to_decimal, label, time

),

swaps AS (

SELECT COALESCE(avg(price), 1) AS price, label, volume, time FROM

(SELECT * FROM from_swaps UNION

SELECT * FROM to_swaps)

LEFT JOIN osmosis.core.dim_prices ON symbol ILIKE label AND DATE_TRUNC('day', recorded_at) = time

GROUP BY label, volume, time

SELECT label, time, sum(volume * price) AS volume_usd FROM swaps

GROUP BY label, time