WITH atomPriceTb as ( SELECT RECORDED_HOUR::date as p_date, avg(price) as atom_price FROM osmosis.core.ez_prices WHERE symbol ilike ANY ('ATOM') AND RECORDED_HOUR >= '2022-12-01' AND RECORDED_HOUR <= CURRENT_DATE - 1 GROUP by 1 ORDER by 1 ) , osmoPriceTb as ( SELECT RECORDED_HOUR::date as p_date, avg(price) as osmo_price FROM osmosis.core.ez_prices WHERE symbol ilike ANY ('OSMO') AND RECORDED_HOUR >= '2022-12-01' AND RECORDED_HOUR <= CURRENT_DATE - 1 GROUP by 1 ORDER by 1 ) , solPriceTb as ( SELECT RECORDED_HOUR::date as p_date, avg(CLOSE) as sol_price from solana.core.fact_token_prices_hourly where SYMBOL = 'SOL' and IMPUTED = FALSE AND RECORDED_HOUR >= '2022-12-01' AND RECORDED_HOUR <= CURRENT_DATE - 1 GROUP by 1 ORDER by 1 ) , nearPriceTb as ( SELECT TIMESTAMP::date as p_date, avg(price_usd) as near_price FROM near.core.fact_prices where SYMBOL = 'wNEAR' AND TIMESTAMP >= '2022-12-01' AND TIMESTAMP <= CURRENT_DATE - 1 GROUP by 1 ORDER by 1 ) , flowPriceTb as ( SELECT RECORDED_HOUR::date as p_date, avg(CLOSE) as flow_price FROM flow.core.fact_hourly_prices where token = 'Flow' AND RECORDED_HOUR >= '2022-12-01' AND RECORDED_HOUR <= CURRENT_DATE - 1 GROUP by 1 ORDER by 1 ) select BLOCK_TIMESTAMP::date date, 'Solana' as chain, count(distinct tx_id) as swap_cnt, count(distinct SWAPPER) swapper_cnt, sum(case when SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' then SWAP_FROM_AMOUNT*sol_price end) as swap_from_vol, sum(case when SWAP_TO_MINT = 'So11111111111111111111111111111111111111112' then SWAP_TO_AMOUNT*sol_price end) as swap_to_vol, sum(swap_cnt) over (order by date) as cum_swap_cnt, sum(swap_from_vol) over (order by date) as cum_swap_from_vol, sum(swap_to_vol) over (order by date) as cum_swap_to_vol from solana.core.fact_swaps s join solPriceTb p on s.BLOCK_TIMESTAMP::date = p.p_date where date >= '2022-12-01' AND date <= CURRENT_DATE - 1 group by 1 UNION select BLOCK_TIMESTAMP::date date, 'Atom' as chain, count(distinct tx_id) as swap_cnt, count(distinct TRADER) swapper_cnt, sum((case when FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then FROM_AMOUNT*atom_price end)/(pow(10, FROM_DECIMAL))) as swap_from_vol, sum((case when TO_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then TO_AMOUNT*atom_price end)/(pow(10, TO_DECIMAL))) as swap_to_vol, sum(swap_cnt) over (order by date) as cum_swap_cnt, sum(swap_from_vol) over (order by date) as cum_swap_from_vol, sum(swap_to_vol) over (order by date) as cum_swap_to_vol from osmosis.core.fact_swaps s join atomPriceTb p on s.BLOCK_TIMESTAMP::date = p.p_date where date >= '2022-12-01' AND date <= CURRENT_DATE - 1 group by 1 UNION select BLOCK_TIMESTAMP::date date, 'Osmo' as chain, count(distinct tx_id) as swap_cnt, count(distinct TRADER) swapper_cnt, sum((case when FROM_CURRENCY = 'uosmo' then FROM_AMOUNT*osmo_price end)/(1e6)) as swap_from_vol, sum((case when TO_CURRENCY = 'uosmo' then TO_AMOUNT*osmo_price end)/(1e6)) as swap_to_vol, sum(swap_cnt) over (order by date) as cum_swap_cnt, sum(swap_from_vol) over (order by date) as cum_swap_from_vol, sum(swap_to_vol) over (order by date) as cum_swap_to_vol from osmosis.core.fact_swaps s join osmoPriceTb p on s.BLOCK_TIMESTAMP::date = p.p_date where date >= '2022-12-01' AND date <= CURRENT_DATE - 1 group by 1 UNION select BLOCK_TIMESTAMP::date date, 'Near' as chain, count(distinct TX_HASH) as swap_cnt, count(distinct TRADER) swapper_cnt, sum((case when TOKEN_IN = 'wNEAR' then AMOUNT_IN*near_price end)) as swap_from_vol, sum((case when TOKEN_OUT = 'wNEAR' then AMOUNT_OUT*near_price end)) as swap_to_vol, sum(swap_cnt) over (order by date) as cum_swap_cnt, sum(swap_from_vol) over (order by date) as cum_swap_from_vol, sum(swap_to_vol) over (order by date) as cum_swap_to_vol from near.core.ez_dex_swaps s join nearPriceTb p on s.BLOCK_TIMESTAMP::date = p.p_date where date >= '2022-12-01' AND date <= CURRENT_DATE - 1 group by 1 UNION select BLOCK_TIMESTAMP::date date, 'Flow' as chain, count(distinct TX_ID) as swap_cnt, count(distinct TRADER) swapper_cnt, sum((case when TOKEN_IN_CONTRACT = 'A.1654653399040a61.FlowToken' then TOKEN_IN_AMOUNT*flow_price end)) as swap_from_vol, sum((case when TOKEN_OUT_CONTRACT = 'A.1654653399040a61.FlowToken' then TOKEN_OUT_AMOUNT*flow_price end)) as swap_to_vol, sum(swap_cnt) over (order by date) as cum_swap_cnt, sum(swap_from_vol) over (order by date) as cum_swap_from_vol, sum(swap_to_vol) over (order by date) as cum_swap_to_vol from flow.core.ez_swaps s join flowPriceTb p on s.BLOCK_TIMESTAMP::date = p.p_date where date >= '2022-12-01' AND date <= CURRENT_DATE - 1 group by 1

In the News - 11. New Year, New Activity?