CryptoIcicleNFTx-2.User Activity
Updated 2022-06-11
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
›
⌄
-- Payout 0.053 ETH
-- Grand Prize 0.16 ETH
-- Level Beginner
-- Q2. Analyze user activity providing and removing liquidity from NFTX vaults.
-- Which three vaults have seen the most activity over the past 60 days?
-- Are there any similarities between these vaults or NFT projects? Explain and visualize your findings.
-- Hint: fact_event_logs is probably the best table to use here.
with vaults as (
select
event_inputs:vaultId as vault_id,
event_inputs:assetAddress as vault_address
from ethereum.core.fact_event_logs
where
event_name = 'VaultInit'
group by vault_id, vault_address
)
select
block_timestamp::date as date,
v.vault_id,
v.vault_address,
count(distinct tx_hash) as n_txns
from ethereum.core.fact_event_logs l
join vaults v on l.contract_address = vault_address
where block_timestamp >= CURRENT_DATE - 60
and event_name in ('Transfer','Mint','Redeem')
and vault_id <> '467'
group by vault_id, date, v.vault_address
order by n_txns desc
Run a query to Download Data