date(block_timestamp) as day,

count(DISTINCT tx_id) as SWAPS,

count(DISTINCT SWAPPER) as SWAPPERS,

sum(

CASE when SWAP_FROM_MINT LIKE 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' then SWAP_FROM_AMOUNT * BONK_price

else SWAP_TO_AMOUNT * BONK_price end

) as swap_volume_usd

FROM solana.defi.fact_swaps

LEFT outer JOIN (

SELECT

date_trunc('day',recorded_hour) as date,

median(CASE WHEN symbol = 'SOL' then close end) as SOL_price,

median(CASE WHEN symbol = 'BONK' then close end) as BONK_price,

median(CASE WHEN symbol = 'BONK' then close end) / median(CASE WHEN symbol = 'SOL' then close end) as BONK_SOL_price

FROM solana.price.fact_token_prices_hourly

WHERE (symbol = 'BONK' or symbol = 'SOL')--token_address = '6dhTynDkYsVM7cbF7TKfC9DWB636TcEM935fq7JzL2ES' and decimals = 9

AND date>='2022-12-30'

GROUP by date

ORDER BY date DESC

) on date(block_timestamp) = date

WHERE SWAP_FROM_MINT LIKE 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'

OR SWAP_to_MINT LIKE 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'

GROUP BY 1

HAVING day BETWEEN '{{ start_date }}' and '{{ end_date }}'