check_skedStarknet
Updated 2023-09-14
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
›
⌄
WITH TxsCTE AS (
SELECT
date_trunc('day', timestamp) AS dt,
COUNT(DISTINCT tx_hash) AS txs
FROM
external.tokenflow_starknet.decoded_events
WHERE
chain_id = 'mainnet'
AND timestamp >= CAST('2022-12-08' AS TIMESTAMP)
GROUP BY 1
),
TvlCTE AS (
SELECT
date AS dt,
tvl_usd AS tvl
FROM
external.defillama.fact_chain_tvl
WHERE
chain = 'Starknet'
GROUP BY 1, 2
)
SELECT
COALESCE(TxsCTE.dt, TvlCTE.dt) AS dt,
COALESCE(txs, 0) AS txs,
COALESCE(tvl, 0) AS tvl,
AVG(COALESCE(txs, 0)) OVER (ORDER BY COALESCE(TxsCTE.dt, TvlCTE.dt) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sma_7_days_txs,
AVG(COALESCE(tvl, 0)) OVER (ORDER BY COALESCE(TxsCTE.dt, TvlCTE.dt) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sma_7_days_tvl
FROM
TxsCTE
LEFT JOIN
TvlCTE ON TxsCTE.dt = TvlCTE.dt
ORDER BY COALESCE(TxsCTE.dt, TvlCTE.dt);
Run a query to Download Data