zakkisyed3.Mirror Total Transaction Fees per Asset
Updated 2021-11-24
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
›
⌄
SELECT DATE(block_timestamp) as day, a.address_name as asset, SUM(msg_value:execute_msg:mint:asset:amount::float / POW(10,6) * p.price_usd) as total_minted_USD, COUNT(*) as total_transactions, total_minted_USD/total_transactions as avg_txn_fee
FROM terra.msgs t
JOIN (
SELECT address, address_name
FROM terra.labels
WHERE label = 'mirror' AND address_name LIKE 'm%'
) a
ON a.address = msg_value:execute_msg:mint:asset:info:token:contract_addr::string
JOIN (
SELECT DATE(block_timestamp) as date, currency, price_usd FROM terra.oracle_prices
WHERE currency in (
SELECT address
FROM terra.labels
WHERE label = 'mirror' AND address_name LIKE 'm%'
)
AND block_timestamp >= current_date-30
--MONTH(block_timestamp) = MONTH(current_date())
) p
ON p.currency = a.address AND p.date = DATE(block_timestamp)
WHERE msg_value:contract::string = 'terra1wfz7h3aqf4cjmjcvc6s8lxdhh7k30nkczyf0mj'
AND msg_value:execute_msg:mint IS NOT NULL
AND block_timestamp >= current_date-30
--MONTH(block_timestamp) = MONTH(current_date())
GROUP BY 1, 2
ORDER BY avg_txn_fee DESC
Run a query to Download Data