*,

row_number()over(partition by ORIGIN_FROM_ADDRESS order by block_timestamp asc) as n

from blast.core.ez_decoded_event_logs

where CONTRACT_ADDRESS= '0x7fcb51ef936bace193b81478f9d17c8d91f94a2f'

select

date_trunc('day',BLOCK_TIMESTAMP) as day,

count(case when n=1 then ORIGIN_FROM_ADDRESS end) as "New players",

count(distinct ORIGIN_FROM_ADDRESS ) as "Total players",

"Total players"-"New players" as "Returned players",

("Returned players"/"Total players")*100 as "% of returned players"

from tb1

group by 1

order by 1 desc