i_danRONKE: Daily Metrics
Updated 2025-04-03
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
›
⌄
With main_data AS (
SELECT
block_timestamp
, tx_hash
, is_mint
, origin_function_signature
, origin_from_address
, quantity
, token_id
FROM ronin.nft.ez_nft_transfers
WHERE
contract_address = lower('0x810b6d1374ac7ba0e83612e7d49f49a13f1de019')
AND is_mint = 'FALSE'
--GROUP BY 1
--ORDER BY 2 DESC
--LIMIT 1000
)
SELECT
date_trunc(day, block_timestamp) AS "Day"
, COUNT(tx_hash) "Total Transactions"
, COUNT(DISTINCT origin_from_address) AS "Total Users"
, COUNT(CASE WHEN origin_function_signature = '0x95a4ec00' THEN tx_hash
WHEN origin_function_signature = '0x00000000' THEN tx_hash
WHEN origin_function_signature = '0x17246d5d' THEN tx_hash
WHEN origin_function_signature = '0x2beee4c7' THEN tx_hash END) AS "Sales"
, COUNT(CASE WHEN origin_function_signature = '0x42842e0e' THEN tx_hash END) AS "Wallet Transfers"
-- , COUNT(CASE WHEN origin_function_signature = '0x17246d5d' THEN tx_hash END) AS "Offers Accepted"
-- , "Direct Sales" + "Offers Accepted" AS "Total Sales"
FROM main_data
GROUP BY 1
ORDER BY 1 DESC