WITH recent_day_volume AS (

SELECT

MAX(DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS most_recent_date,

SUM(CASE

WHEN SWAP_FROM_MINT = '{{CA}}' THEN swap_from_amount_usd

WHEN SWAP_TO_MINT = '{{CA}}' THEN swap_to_amount_usd

ELSE 0

END) AS most_recent_volume

FROM solana.defi.ez_dex_swaps

WHERE

SWAP_FROM_MINT = '{{CA}}'

OR SWAP_TO_MINT = '{{CA}}'

GROUP BY DATE_TRUNC('day', BLOCK_TIMESTAMP)

ORDER BY most_recent_date DESC

LIMIT 1

SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date,

'{{CA}}' AS token,

SUM(swap_from_amount_usd) AS buyvolume,

SUM(swap_to_amount_usd) AS sellvolume,

SUM(swap_from_amount_usd) + SUM(swap_to_amount_usd) AS volume,

(SUM(swap_from_amount_usd) + SUM(swap_to_amount_usd)) * 0.0025 AS value_leak,

((SUM(swap_from_amount_usd) + SUM(swap_to_amount_usd)) * 0.0025) *

('{{24 hour Raydium Volume}}' / (SELECT most_recent_volume FROM recent_day_volume)) AS adjusted_value_leak,

SUM((SUM(swap_from_amount_usd) + SUM(swap_to_amount_usd)) * 0.0025) OVER (PARTITION BY '{{CA}}' ORDER BY DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS cumulative_value_leak,

SUM(((SUM(swap_from_amount_usd) + SUM(swap_to_amount_usd)) * 0.0025) *

('{{24 hour Raydium Volume}}' / (SELECT most_recent_volume FROM recent_day_volume))) OVER (PARTITION BY '{{CA}}' ORDER BY DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS adjusted_cumulative_value_leak

FROM solana.defi.ez_dex_swaps

WHERE

SWAP_FROM_MINT = '{{CA}}'

OR SWAP_TO_MINT = '{{CA}}'

GROUP BY DATE_TRUNC('day', BLOCK_TIMESTAMP)

HAVING value_leak IS NOT NULL