kellenTHORChain Unique Swapper Addresses by Chain
    Updated 2023-01-10
    WITH t0 AS (
    SELECT DISTINCT DATE_TRUNC('month', block_timestamp)::date AS date
    , CASE WHEN to_asset LIKE '%/%' THEN 'THOR' ELSE REPLACE(SPLIT(to_asset, '.')[0]::string, 'GAIA', 'ATOM') END AS chain
    , SPLIT(memo, ':') [2] :: string AS address
    FROM thorchain.core.fact_swaps_events
    UNION
    SELECT DISTINCT DATE_TRUNC('month', block_timestamp)::date AS date
    , CASE WHEN from_asset LIKE '%/%' THEN 'THOR' ELSE REPLACE(SPLIT(from_asset, '.')[0]::string, 'GAIA', 'ATOM') END AS chain
    , from_address AS address
    FROM thorchain.core.fact_swaps_events
    ), t1 AS (
    SELECT *
    FROM t0
    WHERE (
    chain <> 'THOR'
    OR LEFT(address, 4) = 'thor'
    )
    AND date >= DATEADD('month', -18, CURRENT_DATE)
    ), t2 AS (
    SELECT chain
    , COUNT(DISTINCT address) AS tot_n_users
    FROM t1
    GROUP BY 1
    ), t3 AS (
    SELECT *
    , ROW_NUMBER() OVER (ORDER BY tot_n_users DESC) AS rank
    , CONCAT(LPAD(to_varchar(rank), 2, '0'), ': ', chain) AS label
    FROM t2
    ), t4 AS (
    SELECT t3.label
    , COUNT(DISTINCT t1.address) AS n_users
    FROM t1
    JOIN t3 ON t3.chain = t1.chain
    GROUP BY 1
    )
    SELECT *
    Run a query to Download Data