0xHaM-ddetailed-violet
Updated 2024-12-17
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 timeframe AS (
SELECT
date_day AS date
FROM crosschain.core.dim_dates
),
get_spentFee_per_user as (
select
FROM_ADDRESS as user,
sum(TX_FEE) as total_spent_fee
from avalanche.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2024-01-01'
group by 1
)
-- forked from 0xDataWolf / Retention Pareto Demo @ https://flipsidecrypto.xyz/0xDataWolf/q/6IUCfIcpSTMC/retention-pareto-demo
, get_cumulative_count as (
select
* , sum(total_spent_fee) over(order by total_spent_fee desc) as cumulative_count
from get_spentFee_per_user
)
, get_pct_of_cumulative_count as(
select
*, cumulative_count / max(cumulative_count) over() as pct_of_cumu_count
from get_cumulative_count
)
, pareto_label as(
select
*
, case when pct_of_cumu_count <= 0.8 then 'power' else 'normie' end as label
, row_number() over(order by total_spent_fee desc) as ranker -- for sanity check
from get_pct_of_cumulative_count
)
select
QueryRunArchived: QueryRun has been archived