select block_timestamp::date as fecha,

tx_id,

receiver as wallet_paid,

created_at,

row_number() over (partition by wallet_paid order by fecha) as rank

from algorand.payment_transaction pt

inner join algorand.account a

on pt.receiver = a.address

where sender = 'TLR47MQCEIC6HSSYLXEI7NJIINWJESIT3XROAYC2DUEFMSRQ6HBVJ3ZWLE'

and amount < 10000

),

DAYS as (

select w.wallet_paid,

w.fecha as date_paid,

b.block_timestamp::date as date_created,

timestampdiff ('days', date_created, date_paid) as days_paid_after_creation

--count (distinct wallet_paid) as wallets_created

from WALLETS_PAID w

inner join algorand.block b

on w.created_at = b.block_id

where w.rank = 1

order by 4

select count(wallet_paid),

case

when days_paid_after_creation = 0 then 'Create and submit (0 days)'

when days_paid_after_creation > 0 and days_paid_after_creation < 4 then 'Prepared creation (0-4 days)'

when days_paid_after_creation >= 4 and days_paid_after_creation < 21 then 'Waited before participating (4-21 days)'

when days_paid_after_creation >= 21 then 'Prior user (+21 days)'

end as class,

date_created,

date_paid

from DAYS