iboo-jbj2MVMarsr‘s top 2 preferred smart contract addresses
Updated 2022-03-13
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
›
⌄
WITH new_user_table AS (
SELECT
date_trunc('day', first_tx_stamp) AS first_tx_date,
sender AS new_user
FROM
(SELECT
DISTINCT msg_value:sender AS sender,
min(block_timestamp) AS first_tx_stamp
FROM terra.msgs
WHERE
block_timestamp >= CURRENT_DATE - 90
AND tx_id IS NOT NULL
GROUP BY sender)
WHERE first_tx_date > CURRENT_DATE - 90
GROUP BY first_tx_date, new_user)
SELECT
tx_to[0] AS protocl_address,
labels.address_name AS protocol_name,
COUNT(DISTINCT tx_from[0]) AS new_users,
COUNT(tx_id) AS transactions_count
FROM terra.transactions
LEFT JOIN terra.labels labels ON tx_to[0] = labels.address
WHERE
block_timestamp >= CURRENT_DATE - 90
AND protocol_name IS NOT NULL
AND tx_from[0] IN (SELECT new_user FROM new_user_table)
AND label = 'mars'
GROUP BY protocl_Address, protocol_name
ORDER BY new_users DESC
LIMIT 2
Run a query to Download Data