kellenTHORChain Unique Swapper Addresses by Chain
Updated 2023-01-10
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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