Crazy_KidAge of Synth Holders
    Updated 2022-04-12
    WITH burn AS (SELECT DISTINCT from_address as burner
    FROM thorchain.swaps
    WHERE from_asset LIKE '%/%'),

    mint_hold as (SELECT from_address as holder, to_asset as asset, date(block_timestamp) as mint_date
    FROM thorchain.swaps
    WHERE to_asset LIKE '%/%' AND from_address NOT IN (SELECT burner FROM burn)),

    data1 AS (SELECT * FROM ( SELECT *, row_number() over (partition BY holder ORDER BY mint_date) r FROM mint_hold ) T
    WHERE T.r=1
    ORDER BY mint_date),

    data2 as (SELECT *, (CURRENT_DATE - mint_date) as hold_days FROM data1)

    SELECT asset, hold_days, COUNT(holder) as no_of_holders
    FROM data2
    GROUP BY asset, hold_days
    ORDER BY hold_days DESC
    Run a query to Download Data