winnie-fsavax activity copy
Updated 2023-06-07
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 adriaparcerisas / avax activity @ https://flipsidecrypto.xyz/adriaparcerisas/q/dau-avax-B_x0Sk
WITH
active_users as (
SELECT
trunc(x.block_timestamp,'week') as date,
label_type,
count(distinct from_address) as n_users,
count(distinct x.tx_hash) as txs,
sum(tx_fee) as fees,
avg(tx_fee) as avg_tx_fee
from avalanche.core.fact_transactions x
join avalanche.core.fact_decoded_event_logs y on x.tx_hash=y.tx_hash
join avalanche.core.dim_labels z on y.contract_address=z.address
group by 1,2
),
tx_per_user as (
SELECT
distinct from_address,
trunc(x.block_timestamp,'week') as date,
label_type,
count(distinct x.tx_hash) as transactions
from avalanche.core.fact_transactions x
join avalanche.core.fact_decoded_event_logs y on x.tx_hash=y.tx_hash
join avalanche.core.dim_labels z on y.contract_address=z.address
group by 1,2,3
)
select x.*,avg(transactions) as avg_txs_per_user
from active_users x
join tx_per_user y on x.date=y.date and x.label_type=y.label_type
where x.date>=current_date-interval '{{Months}} MONTHS'
group by 1,2,3,4,5,6
order by 1 asc
Run a query to Download Data