with price as ( select date_trunc('day', hour) as date, avg(price) as price_usd from crosschain.price.ez_prices_hourly where blockchain = 'ethereum' and token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' group by 1 ), base as ( SELECT e.block_timestamp, e.origin_from_address, e.tx_hash, value as amount, amount * price_usd as amount_usd, tx_fee, utils.udf_hex_to_string(substring(data, 129)) AS domain_name from ink.core.fact_event_logs e left join ink.core.fact_transactions t on e.tx_hash = t.tx_hash left join price on e.block_timestamp::date = date where e.tx_succeeded = TRUE and topics[0] = '0x53946bf984072f5888fcb2d7d2b0587c8efeb9187d958f21809711cf00b4b4a5' and origin_to_address = '0xfb2cd41a8aec89efbb19575c6c48d872ce97a0a5' ), t1 AS ( SELECT origin_from_address AS user, MIN(block_timestamp) AS min_platform_tx FROM base GROUP BY origin_from_address ), t2 AS ( SELECT a.user, a.min_platform_tx, MIN(b.block_timestamp) AS min_tx FROM t1 a LEFT JOIN ink.core.fact_transactions b ON a.user = b.from_address GROUP BY a.user, a.min_platform_tx ), t3 AS ( SELECT user, DATEDIFF('day', min_tx, min_platform_tx) AS age_wallets FROM t2 WHERE min_tx IS NOT NULL ) SELECT CASE WHEN age_wallets < 1 THEN 'A : < 1 days' WHEN age_wallets < 10 THEN 'B : < 1-10 days' WHEN age_wallets < 30 THEN 'C : < 10-30 days' WHEN age_wallets < 90 THEN 'D : < 30-90 days' WHEN age_wallets < 180 THEN 'E : < 90-180 days' WHEN age_wallets < 365 THEN 'F : < 180-365 days' ELSE 'G : over 1 year' END AS "type", COUNT(DISTINCT user) AS user_count, 100.0 * COUNT(DISTINCT user) / SUM(COUNT(DISTINCT user)) OVER () AS wallet_count_percentage FROM t3 GROUP BY 1 ORDER BY 1;

ZNS Connect On INK