SELECT swapper

, tx_id

, block_timestamp::date AS date

, CASE

WHEN swap_program ILIKE 'orca%' THEN 'Orca'

WHEN swap_program ILIKE 'jupiter%' THEN 'Jupiter'

WHEN swap_program ILIKE 'raydium%' THEN 'Raydium'

ELSE 'Other' END AS exchange

, CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN 1 ELSE 0 END AS n_swaps_to_bonk

, CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN 0 ELSE 1 END AS n_swaps_from_bonk

, CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE -swap_from_amount END AS net_swap_amout

, CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE swap_from_amount END AS tot_swap_amout

, swap_to_mint

, swap_from_amount

FROM solana.core.fact_swaps

WHERE block_timestamp::date >= CURRENT_DATE - 7

AND (

swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'

OR swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'

)

AND succeeded

), t1 AS (

SELECT swapper

, COUNT(1) AS n_swaps

, SUM(n_swaps_to_bonk) AS n_swaps_to_bonk

, SUM(n_swaps_from_bonk) AS n_swaps_from_bonk

, SUM(net_swap_amout) AS net_swap_amout

, SUM(tot_swap_amout) AS tot_swap_amout

FROM t0

GROUP BY 1

), t2 AS (

SELECT t1.*

, ROUND(100 * t1.net_swap_amout / (SUM(net_swap_amout) OVER ()), 3) AS percent_of_total_net_sales

FROM t1

WHERE net_swap_amout < 0