CryptoIcicleEVM - 4. The “Flippening” Comparison - Number of Active Wallets
Updated 2023-03-02
999
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
›
⌄
-- Pay by Quality Your score determines your final payout.
-- Grand Prize 35.489 NEAR (A score of 11 or 12 earns you a Grand Prize title)
-- Payout 23.659 NEAR
-- Score Multiplier 0-7 : 0% 8 : 50% 9 : 75% 10 : 100% 11 : 125% 12 : 150%
-- Payout Network Near
-- Level Intermediate
-- Difficulty Hard
-- It’s autumn in the Northern Hemisphere. What season is it in the City of NEAR?
-- Pick one of the districts from the NEAR Tournament and review how activity there has changed over the past 90 days.
-- What has changed? What trends can you spot? Is it truly “winter” in your chosen “district” or area (NFTs, DeFi, etc.)?
-- Grand Prize questions (11 or 12) will go deeper into the why - what is driving any of these changes?
-- BONUS: Post your dashboard on Twitter and tag @flipsidecrypto and any relevant accounts!
-- SQL Credit https://app.flipsidecrypto.com/velocity/queries/6eee8d62-9495-46e1-984a-f8f17aa302ee
-- Dashbord: https://app.flipsidecrypto.com/dashboard/near-citizens-fQRyBU
with arb as (
with wallet_age AS (
SELECT
from_address as address,
count(distinct tx_hash) as tx_counts,
count(distinct date(block_timestamp)) as days_active,
min(date(block_timestamp)) as first_tx,
max(date(block_timestamp)) as last_tx,
datediff('day', last_tx, getdate()) as days_last_active,
datediff('day', first_tx, getdate()) as age_today
from arbitrum.core.fact_transactions
group by 1
),
new_users_stat AS (
SELECT
first_tx,
count(distinct address) as new_users,
count(distinct case when days_active >= 7 AND days_last_active <= 7 then address else null end) as active_user,
count(distinct case when days_active = 1 then address else null end) as one_day_user,
count(distinct case when days_active >= 2 then address else null end) as two_or_more_days_user,
count(distinct case when days_active >= 5 then address else null end) as five_or_more_days_user,
Run a query to Download Data