Elprognerd05 - n users and txs daily
Updated 2022-11-28
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
with t1 AS (SELECT date_trunc('day', RECORDED_AT) AS date, address, symbol , avg(price) AS price_usd
FROM osmosis.core.dim_labels x
INNER JOIN osmosis.core.dim_prices y
ON y.symbol = x.PROJECT_NAME
WHERE RECORDED_AT >= '2022-11-01'
GROUP BY 1, 2, 3)
SELECT date_trunc('day', block_timestamp) AS date,
CASE WHEN (TO_CURRENCY IS NULL OR TO_CURRENCY ILIKE '%gamm/pool%') THEN 'Dive in Headfirst'
ELSE 'Wade in Carefully' END AS join_type,
COUNT(*) AS n_tx,
COUNT(DISTINCT trader) AS n_user,
sum((from_amount*price_usd)/pow(10, FROM_DECIMAL)) AS tot_Amount_USD,
avg((from_amount*price_usd)/pow(10, FROM_DECIMAL)) AS avg_Amount_USD
FROM osmosis.core.fact_swaps a INNER JOIN t1 b ON a.FROM_CURRENCY = b.address
WHERE TX_STATUS = 'SUCCEEDED' AND block_timestamp >= '2022-11-01'
GROUP BY 1, 2
ORDER BY 1 ASC
Run a query to Download Data