-- forked from Users|Contracts-Interactive-Retention-Total @ https://flipsidecrypto.xyz/edit/queries/a4e0c0fa-1744-47f8-9559-5873fbb63631

WITH avax_tx AS

SELECT

a.BLOCK_NUMBER, a.TX_HASH, a.BLOCK_TIMESTAMP, a.FROM_ADDRESS, a.TO_ADDRESS, a.TX_FEE,

CASE WHEN CONTRACT_ADDRESS IN (SELECT distinct ADDRESS FROM avalanche.core.dim_labels) then c.PROJECT_NAME

ELSE CONTRACT_ADDRESS END AS PROJECT_NAME,

b.EVENT_INDEX, b.EVENT_NAME, b.FULL_DECODED_LOG, b.DECODED_LOG

FROM

avalanche.core.fact_transactions a JOIN

avalanche.core.fact_decoded_event_logs b ON (a.TX_HASH = b.TX_HASH)

JOIN avalanche.core.dim_labels c ON b.CONTRACT_ADDRESS = c.ADDRESS

WHERE

STATUS = 'SUCCESS' AND PROJECT_NAME = '{{Contract}}' AND a.BLOCK_TIMESTAMP::date >= '{{analyze_date_from}}' AND a.BLOCK_TIMESTAMP::date <= '{{analyze_date_until}}'

),

wallets_tab AS

SELECT

FROM_ADDRESS,

COUNT(distinct TX_HASH) as amount_transactions,

CASE WHEN amount_transactions = 1 then '1 Transactions'

WHEN amount_transactions >= 2 AND amount_transactions <= 5 then '2-5 Transactions'

WHEN amount_transactions > 5 AND amount_transactions <= 10 then '5-10 Transactions'

WHEN amount_transactions > 5 AND amount_transactions <= 10 then '5-10 Transactions'

WHEN amount_transactions > 10 AND amount_transactions <= 50 then '10-50 Transactions'

WHEN amount_transactions > 50 then '+50 Transactions' ELSE 'NA' END AS retention_type

FROM

avax_tx

WHERE BLOCK_TIMESTAMP::date >= '{{analyze_date_from}}' AND BLOCK_TIMESTAMP::date <= '{{analyze_date_until}}'

GROUP BY 1