0xHaM-dPower Users Stats copy
Updated 2024-06-29
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
›
⌄
-- forked from Power Users Stats @ https://flipsidecrypto.xyz/edit/queries/f9c5a124-5c22-44db-8ed7-aca06805f566
-- forked from Retention Pareto Demo @ https://flipsidecrypto.xyz/edit/queries/f7d660eb-1b8e-44b6-af22-ae5f36c627ad
-- forked from Retention Pareto Demo @ https://flipsidecrypto.xyz/edit/queries/33632b7d-807e-4489-b94a-53e98630f840
-- forked from Retention Pareto Demo @ https://flipsidecrypto.xyz/edit/queries/33d40d53-3aa8-4681-843b-44055cdd488d
-- forked from Playwo / Users Total @ https://flipsidecrypto.xyz/Playwo/q/jnbOOVFcO0wH/users-total
with all_claims as (
SELECT
tx.TX_HASH,
tx.block_timestamp as block_timestamp,
tr.to_Address as clamer,
RAW_AMOUNT/pow(10,18) as amount
FROM
blast.core.fact_transactions tx
INNER join blast.core.fact_token_transfers tr on tx.tx_hash = tr.tx_hash
and tr.contract_address = lower('0xb1a5700fA2358173Fe465e6eA4Ff52E36e88E2ad')
and tx.to_Address = '0xf7be503166828fe8565c520d66645ac6a06bbdd7'
and tx.ORIGIN_FUNCTION_SIGNATURE != '0xca1d209d'
WHERE
tx.status = 'SUCCESS'
)
,
get_volume_per_user as (
select
clamer,
sum (amount) as total_volume
from all_claims
group by 1
)
-- forked from 0xDataWolf / Retention Pareto Demo @ https://flipsidecrypto.xyz/0xDataWolf/q/6IUCfIcpSTMC/retention-pareto-demo
, get_cumulative_count as (
select
QueryRunArchived: QueryRun has been archived