main as (

select

tx_id,

block_timestamp,

swapper,

swap_from_symbol as symbol_in,

swap_to_symbol as symbol_out,

nvl(swap_from_amount_usd, swap_to_amount_usd) as amount_usd

from

solana.defi.ez_dex_swaps

where

swap_program = 'pump.fun'

and year(block_timestamp) >= 2024

),

first_swap as (

select

swapper,

min(block_timestamp) as first_swap_date

from

main

group by

swapper

),

retention as (

select

a.swapper,

a.block_timestamp as subsequent_swap_date,

b.first_swap_date,

datediff('day', b.first_swap_date, a.block_timestamp) as days_since_first_swap

from

main a

join