daus as (

SELECT

distinct sender as users,

trunc(block_timestamp,'week') as weeks,

count(distinct trunc(block_timestamp,'day')) as active_days

from aptos.core.fact_transactions

group by 1,2

having active_days>=4

),

daus2 AS (

SELECT

DISTINCT sender AS users,

payload_function,

min(TRUNC(block_timestamp, 'week')) AS debut

FROM aptos.core.fact_transactions where users in (select distinct users from daus)

GROUP BY 1, 2

),

final AS (

SELECT

debut,

CASE

WHEN payload_function ILIKE '%vote%' THEN 'Vote'

WHEN payload_function ILIKE '%transfer%' THEN 'Transfer'

WHEN payload_function ILIKE '%swap%' THEN 'Swap'

WHEN payload_function ILIKE '%reward%' OR payload_function ILIKE '%claim%' THEN 'Claim'

WHEN payload_function ILIKE '%eragon%' THEN 'Eragon-Checkin'

WHEN payload_function ILIKE '%Milestone%' THEN 'IncrementMilestone'

WHEN payload_function ILIKE '%Mint%' THEN 'Mint'

WHEN payload_function ILIKE '%Liquidity%' OR payload_function ILIKE '%Deposit%' OR payload_function ILIKE '%Withdraw%' THEN 'Liquidity'

WHEN payload_function ILIKE '%stake%' THEN 'Staking'

WHEN payload_function ILIKE '%token%' OR payload_function ILIKE '%price%' THEN 'Token'

when payload_function ilike '%Tapos%' then 'Tapos Game'

when payload_function ilike '%main::tap%' then 'Tap'

ELSE payload_function

END AS payload_functions,