Sandeshnew users growth arbi
Updated 2023-04-13
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 new_user_sushiswap_txns as
(
select
*,
case
when nonce < 3 then 'new users'
else 'experienced user'
end as "user type"
from arbitrum.core.fact_transactions
where to_address in
(
lower('0xbE811A0D44E2553d25d11CB8DC0d3F0D0E6430E6'),
lower('0xc35DADB65012eC5796536bD9864eD8773aBc74C4'),
lower('0x93395129bd3fcf49d95730D3C2737c17990fF328'),
lower('0x2f686751b19a9d91cc3d57d90150Bc767f050066'),
lower('0xCaAbdD9Cf4b61813D4a52f980d6BC1B713FE66F5'),
lower('0xaB235da7f52d35fb4551AfBa11BFB56e18774A65'),
lower('0x1b02da8cb0d097eb8d57a175b88c7d8b47997506'),
lower('0x4ab2fc6e258a0ca7175d05ff10c5cf798a672cae'),
lower('0x0689640d190b10765f09310fcfe9c670ede4e25b')
)
and block_timestamp > CURRENT_DATE - interval ' {{number_of_months}} months'
and nonce < 3
)
select
date_trunc('week',block_timestamp) as "date",
count(distinct from_address) as new_users,
(100*(new_users-(lag(new_users) over(order by "date" asc)))/(lag(new_users) over(order by "date" asc))) as growth_percentage,
case
when growth_percentage >= 0 then 'positive'
else 'negative'
end as "sign"
from new_user_sushiswap_txns
group by "date","user type"
Run a query to Download Data