drone-mostafa2023-06-12 02:01 AM
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
›
⌄
⌄
SELECT
DATE_TRUNC ('DAY',t1.BLOCK_TIMESTAMP) AS DATE,
/*CASE
when t1.BLOCK_TIMESTAMP >= CURRENT_DATE -7 then 'Last Week'
when t1.BLOCK_TIMESTAMP >= CURRENT_DATE -14 then '2 weeks ago'
when t1.BLOCK_TIMESTAMP >= CURRENT_DATE -30 then 'Last Month'
end as type,*/
PROJECT_NAME,
count (DISTINCT t1.TX_HASH) as txn,
count (DISTINCT t1.TX_SIGNER) as users,
sum (DEPOSIT) as Volume,
SUM (txn) OVER (PARTITION BY PROJECT_NAME ORDER BY DATE) AS CUM_TXN,
SUM (users) OVER (PARTITION BY PROJECT_NAME ORDER BY DATE) AS CUM_users
FROM near.core.fact_transfers t1
INNER JOIN near.core.fact_transactions t2 using (TX_HASH)
LEFT JOIN near.core.dim_address_labels t3 on t1.TX_RECEIVER = ADDRESS
WHERE t2.TX_RECEIVER in ('usdt.tether-token.near','dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near')
AND t1.BLOCK_TIMESTAMP >= CURRENT_DATE -30
AND STATUS = 'true'
AND LABEL_TYPE = 'cex'
GROUP BY 1,2
with
Transfer_T AS ( SELECT
tx_hash,
block_timestamp,
try_parse_json(replace(logs[0]::string, 'EVENT_JSON:', '')) AS EVENTs,
EVENTs['event']::string AS Method,
receiver_id AS Token
FROM near.core.fact_receipts
WHERE receiver_id = 'usdt.tether-token.near'
AND object_keys(status_value)[0]::string != 'Failure'
Run a query to Download Data