from ethereum.nft_events

where contract_address = '0x9a534628b4062e123ce7ee2222ec20b86e16ca8f' --mekaverse

and event_type = 'sale' and price is not null

),

first_sale as (select token_id, max(price) as price_1st, price_usd as max_price_usd, date_sold as date_sold_max

from raw

where sale_number in (1,2)

group by 1,3,4), --a

min_sale as (select token_id, min(price) as min_sold, price_usd as min_price_usd, date_sold as date_sold_min

from raw

where sale_number > 1

group by 1,3,4), --c

final as (select a.token_id, a.price_1st, c.min_sold, (a.price_1st - c.min_sold) as net_price, concat('-',net_price) as nett_price, (c.min_price_usd - a.max_price_usd) as net_price_usd,

a.date_sold_max, c.date_sold_min, (c.date_sold_min - a.date_sold_max) as days_between_sold,

CASE

when c.min_sold < a.price_1st then 'paperhands'

else 'no-paperhand' end as min_sale

from first_sale a

join min_sale c on a.token_id = c.token_id

order by 4 desc

limit 100)

select days_between_sold, count(token_id)

from final

group by 1