theericstoneeth og - base query for active users
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
top_contracts as (
select CONTRACT_ADDRESS, ifnull(CONTRACT_NAME,ADDRESS_NAME) as CONTRACT_NAME, TOTAL_FEE
from
(select
value[0]::string as CONTRACT_ADDRESS,
value[1]::string as SYMBOL,
value[2]::string as CONTRACT_NAME,
value[3]::string as TOTAL_FEE
from (select livequery.live.udf_api('https://flipsidecrypto.xyz/api/queries/4d9e7c78-d47c-46b4-b201-08e03c23e28c/latest-run')
as response), lateral FLATTEN (input => response:data:csvData))
left join ethereum.core.dim_labels
on CONTRACT_ADDRESS=ADDRESS
),
active_users as (
select distinct FROM_ADDRESS as wallet
from ethereum.core.fact_transactions
where BLOCK_TIMESTAMP::date>=current_date-365),
top_contracts_users_raw as
(select CONTRACT_ADDRESS, CONTRACT_NAME, TOTAL_FEE,
FROM_ADDRESS as user,
min(BLOCK_TIMESTAMP) as min_date,
rank() over (partition by CONTRACT_ADDRESS order by min_date asc) as rank
from ethereum.core.fact_transactions a
join top_contracts b
on CONTRACT_ADDRESS=TO_ADDRESS
where STATUS='SUCCESS'
group by 1,2,3,4),
top_contracts_users as (
select *
from top_contracts_users_raw
join active_users
on wallet=user)
Run a query to Download Data