-- forked from Volume @ https://flipsidecrypto.xyz/edit/queries/bd0285ba-7d5a-44de-9bd7-0e8c2932aeb3 with prices as ( select date_trunc('day', recorded_hour) as day, avg( close ) as price, token_address, token_name from solana.core.ez_token_prices_hourly group by 1,3,4 ), base as ( select date_trunc('day', a.block_timestamp) as date, swap_from_mint, sum( swap_from_amount ) as swap_from_amount from solana.core.fact_events a left join solana.core.fact_swaps b on a.tx_id = b.tx_id and a.block_timestamp = b.block_timestamp where a.program_id in ( 'JUP4Fb2cqiRUcaTHdrPC8h2gNsA2ETXiPDD33WcGuJB', 'JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph', 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo', 'jupoNjAxXgZ4rjzxzPMP4oxduvQsQtZzyknqvzYNrNu' ) and a.block_timestamp >= '{{Start_Date}}' and a.block_timestamp <= '{{End_Date}}' and a.succeeded group by 1,2 ), aggs as ( select date, swap_from_amount * price as tx_id from base a join prices c on a.swap_from_mint = c.token_address and a.date = c.day having tx_id < 1e9 ), daily_tx as ( select sum( case when date < current_timestamp - interval '24 hours' then tx_id else null end ) as tx_24_hours, sum( case when date < current_timestamp - interval '7 days' then tx_id else null end ) as tx_last_week, sum( case when date < current_timestamp - interval '30 days' then tx_id else null end ) as tx_last_month, sum( case when date < current_timestamp - interval '60 days' then tx_id else null end ) as tx_last_two_months, sum( tx_id ) as tx from aggs ) select tx as "Total Volume", case when tx_24_hours = 0 then null else tx_24_hours end as "Volume 24 hours Ago", case when (tx - tx_24_hours) / tx_24_hours >= 0 then concat('+🟢', round((tx - tx_24_hours) / tx_24_hours * 100, 2), '%') when (tx - tx_24_hours) / tx_24_hours < 0 then concat('-🔴', round((tx - tx_24_hours) / tx_24_hours * 100, 2), '%') else null end as "24 hour % ∆", case when tx_last_week = 0 then null else tx_last_week end as "Volume 7 days Ago", case when (tx - tx_last_week) / tx_last_week >= 0 then concat('+🟢', round((tx - tx_last_week) / tx_last_week * 100, 2), '%') when (tx - tx_last_week) / tx_last_week < 0 then concat('-🔴', round((tx - tx_last_week) / tx_last_week * 100, 2), '%') else null end as "7 day % ∆", case when tx_last_month = 0 then null else tx_last_month end as "Volume 30 days Ago", case when (tx - tx_last_month) / tx_last_month >= 0 then concat('+🟢', round((tx - tx_last_month) / tx_last_month * 100, 2), '%') when (tx - tx_last_month) / tx_last_month < 0 then concat('-🔴', round((tx - tx_last_month) / tx_last_month * 100, 2), '%') else null end as "30 day % ∆", case when tx_last_two_months = 0 then null else tx_last_two_months end as "Volume 60 days Ago", case when (tx - tx_last_two_months) / tx_last_two_months >= 0 then concat('+🟢', round((tx - tx_last_two_months) / tx_last_two_months * 100, 2), '%') when (tx - tx_last_two_months) / tx_last_two_months < 0 then concat('-🔴', round((tx - tx_last_two_months) / tx_last_two_months * 100, 2), '%') else null end as "60 day % ∆" -- 'Swappers' as "X-axis" from daily_tx

Jupiter KPI