Crazy_KidAge of Synth Holders
Updated 2022-04-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
›
⌄
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