MLDZMNnewusers2
Updated 2023-05-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
›
⌄
with t1 as (select
distinct SWAPPER as users,
CONCAT(q.SYMBOL,' -> ',z.SYMBOL) as asset_pairs,
min(block_timestamp) as first_appear
from solana.core.fact_swaps x
JOIN solana.core.dim_tokens z on x.SWAP_to_MINT=z.TOKEN_ADDRESS
join solana.core.dim_tokens q on x.SWAP_from_MINT=q.TOKEN_ADDRESS
where SUCCEEDED='TRUE'
and PROGRAM_ID ilike 'JUP%'
group by 1,2
)
SELECT
date_trunc('{{Time_basis}}',first_appear) as date,
asset_pairs,
count(distinct users) as new_users
--sum(new_users) over (order by date) as Total_new_users
from t1
where first_appear>=current_date-{{Time_period_days}}
group by 1,2 having asset_pairs is not null
QUALIFY row_number() OVER (partition by date ORDER BY new_users DESC) <= 5
Run a query to Download Data