-- Open analytics bounties are all about deep dive dashboards into one specific topic.
-- For this bounty, submissions should compare ETH + Solana in some way.
-- Pay by Quality
-- Your score determines your final payout.
-- Grand Prize 150 USDC (A score of 11 or 12 earns you a Grand Prize title)
-- Payout 100 USDC
-- Score Multiplier 0-7 : 0% 8 : 50% 9 : 75% 10 : 100% 11 : 125% 12 : 150%
-- Payout Network Ethereum
-- Level Intermediate
-- Difficulty Hard
with
swap_txns as (
select
symbol_in as f_token,
symbol_out as to_token,
concat(f_token , '-->', to_token) as pair,
*
from ethereum.core.ez_dex_swaps
where block_timestamp >= '{{start_date}}'
and symbol_out in ('USDC', 'USDT')
)
select
pair,
count(distinct tx_hash) as n_txns,
count(distinct origin_from_address) as n_swappers,
sum(IFF(amount_out > pow(10,9), amount_out/pow(10,18), amount_out)) as amount
from swap_txns
where amount_out > 0
and pair is not null
group by pair