trader

from osmosis.core.fact_swaps

where to_currency = 'ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E'

and block_timestamp >= current_date - 30

and tx_status = 'SUCCEEDED'),

table_2 as (select purchase_date,

block_timestamp as sold_date,

b.trader

from osmosis.core.fact_swaps a

join table_1 b on a.trader = b.trader

where from_currency = 'ibc/903A61A498756EA560B85A85132D3AEE21B5DEDD41213725D22ABF276EA6945E'

and block_timestamp >= current_date - 30

and tx_status = 'SUCCEEDED'

and block_timestamp > purchase_date),

table_3 as (select trader,

avg(datediff(day, purchase_date, sold_date)) as held_duration

from table_2

group by 1)

select case

when held_duration < 1 then 'Held less than 1 day'

when held_duration >= 1 and held_duration <= 7 then '1 day - 7 days'

when held_duration > 7 and held_duration < 31 then '8 days - 1 month'

end as duration,

count(distinct(trader)) as wallet_count,

row_number () over (order by wallet_count desc) as count,

(select avg(held_duration) from table_3) as avg_held_duration

from table_3

group by 1