niloout
Updated 2022-10-19
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
›
⌄
WITH
ACTIVE AS (SELECT DATE_TRUNC('WEEK',BLOCK_TIMESTAMP) AS WEEKLY, COUNT ( DISTINCT BLOCK_TIMESTAMP::DATE) as N_DAY, TX_FROM AS WALLET
FROM osmosis.core.fact_transactions WHERE BLOCK_TIMESTAMP >= CURRENT_DATE - interval'6 MONTH' GROUP BY WEEKLY,WALLET HAVING N_DAY >= 4),
TOP10 AS (SELECT COUNT (DISTINCT TX_ID) as txn,CURRENCY
FROM osmosis.core.fact_transfers WHERE SENDER IN (SELECT WALLET FROM ACTIVE) AND TRANSFER_TYPE = 'IBC_TRANSFER_OUT' and TX_STATUS = 'SUCCEEDED'
AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval'6 MONTH'
GROUP BY 2 order BY 1 DESC limit 10)
SELECT DATE_TRUNC('DAY',BLOCK_TIMESTAMP) AS IADTE, PROJECT_NAME, COUNT (DISTINCT TX_ID) AS TXN_IN, COUNT (DISTINCT SENDER) AS USERS_IN
FROM osmosis.core.fact_transfers LEFT JOIN osmosis.core.dim_labels ON ADDRESS = CURRENCY
WHERE SENDER IN (SELECT WALLET FROM ACTIVE) AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval'6 MONTH' AND BLOCK_TIMESTAMP < CURRENT_DATE
AND TRANSFER_TYPE = 'IBC_TRANSFER_OUT' AND CURRENCY IN (SELECT CURRENCY FROM TOP10) AND TX_STATUS = 'SUCCEEDED' GROUP BY 1,2
Run a query to Download Data