amir007DFKChain Weekly Active Users
Updated 2023-06-07Copy Reference Fork
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
›
⌄
-- DFKChain Weekly Active Users
with dfk_chain AS
(
SELECT trn.block_timestamp::date AS date
, tx_hash
, event_name
, symbol
, amount
, amount_usd
, origin_from_address
FROM avalanche.core.fact_decoded_event_logs log
JOIN avalanche.core.ez_token_transfers trn using(tx_hash)
WHERE trn.block_timestamp::date >= current_date - interval '9 month'
AND contract_address = '0xc05e61d0e7a63d27546389b7ad62fdff5a91aace' -- Synapse: Bridge
AND event_name in ('TokenDeposit', 'TokenRedeem')
AND decoded_log:chainId = '53935' -- https://docs.pokt.network/use/public-rpc/dfk-chain/
), prices AS
(
SELECT hour::date AS date
, symbol
, avg(price) AS price
FROM avalanche.core.fact_hourly_token_prices
GROUP BY 1, 2
), avax_price AS
(
SELECT hour::date AS date
, avg(price) AS avax_price
FROM avalanche.core.fact_hourly_token_prices
WHERE symbol = 'WAVAX'
GROUP BY 1
), jewel_price AS
(
SELECT block_timestamp::date AS date
, 'JEWEL' AS symbol
, avg((amount_out * avax_price) / amount_in) AS jewel_price
FROM avalanche.core.ez_dex_swaps swp
Run a query to Download Data