Query editor
withstatic as (select'{{timebox}}' as timebox, split('{{blockchain}}', ',') as blockchains, split('{{project}}', ',') as projects, timestamp '{{time from}}' as ts_from, timestamp '{{time to}}' as ts_to, split('{{mode}}', ',') as selected_modes), pmms as (selectaddress as maker, namefrom query_7479439 -- pmms), swaps as (select *, date_trunc(timebox, now()) as current, date_trunc(timebox, block_time) as time, get_href(get_chain_explorer_tx_hash(blockchain, cast(tx_hash as varchar)), cast(tx_hash as varchar)) as tx_linkfrom (select *from oneinch.project_swaps, staticwhere trueand block_time > static.ts_fromand block_time < static.ts_toand if(array_position(static.blockchains, '= all =') > 0, true, array_position(blockchains, blockchain) > 0)and if(array_position(static.projects, '= all =') > 0, true, array_position(projects, project) > 0)and amount_usd is not null)left join pmms using(maker)where if(array_position(selected_modes, '= all =') > 0, true, array_position(selected_modes, if(mode = 'intra-chain: intents: user limit order' and name is not null, 'intra-chain: intents: contracts only', mode)) > 0)), agg as (selecttime, project, count(*) as swaps, count(distinct user) as users, sum(amount_usd) as volume, approx_percentile(amount_usd, 0.5) as amount_median, max_by(format('%s $%s %s', blockchain, format_number(amount_usd), tx_link), amount_usd) as tx, any_value(time = current) as currentfrom swapsgroup by 1, 2)-- output --select *, if(current, 0, if(swaps < swaps_previos * 10, cast(swaps - swaps_previos as double) / swaps_previos)) as swaps_changing, if(current, 0, if(users < users_previos * 10, cast(users - users_previos as double) / users_previos)) as users_changing, if(current, 0, if(volume < volume_previos * 10, cast(volume - volume_previos as double) / volume_previos)) as volume_changingfrom (select *