KaskoazulL1 Unique Users and prices since Feb1 --> AVG Update ALGO
Updated 2022-04-07
999
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
›
⌄
--Question 37: Compare the number of unique wallets that have sent a transaction since February 1st on Solana vs Ethereum vs Terra.
--How does user adoption seem to be trending for Solana compared to these other major chains?
--Does it appear that market conditions are slowing down growth or activity on the networks?
WITH ETH_WALLETS AS (
SELECT block_timestamp::date as fecha, count(distinct from_address) as ETH_wallets, count(distinct tx_id) as ETH_txs, 'ETHEREUM' as L1
FROM ethereum.transactions
WHERE fecha >= '2022-02-01' and fecha < CURRENT_DATE - 1
GROUP BY fecha
),
ETH_PRICE AS (
SELECT hour::date as fecha, avg(price) as ETH_price
FROM ethereum.token_prices_hourly
where symbol ='ETH' and fecha >= '2022-02-01' and fecha < CURRENT_DATE - 1
GROUP BY fecha
),
ETH_JOIN AS (
SELECT w.fecha, w.ETH_wallets, w.ETH_txs, p.ETH_price
FROM ETH_WALLETS w LEFT JOIN ETH_PRICE p ON w.fecha = p.fecha
),
TERRA_WALLETS AS (
SELECT block_timestamp::date as fecha, count(distinct tx_from) as TERRA_wallets, count(distinct tx_id) as TERRA_txs, 'TERRA' as L1
FROM terra.transactions
WHERE fecha >= '2022-02-01' and fecha < CURRENT_DATE -1
GROUP BY fecha
),
TERRA_PRICE AS (
SELECT block_timestamp::date as fecha, -- truncate date to days
avg(price_usd) as TERRA_PRICE -- average daily price of token
FROM terra.oracle_prices
WHERE fecha >= '2022-02-01'
AND fecha < CURRENT_DATE - 1 -- Since Feb 1st 2022 except last day
Run a query to Download Data