Mrftiwittering-sapphire copy copy
Updated 2025-01-21
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
›
⌄
-- forked from wittering-sapphire copy @ https://flipsidecrypto.xyz/studio/queries/3e35469b-81db-4c57-beb8-235b9133ed01
WITH datatbl AS (
SELECT
b.tx_hash,
b.origin_from_address AS "Address",
'0x' || SUBSTR(b.DATA, 129, 144) AS domain_name_hex,
a.VALUE AS "Total $BERA paid"
FROM
berachain.testnet.fact_transactions a
JOIN berachain.testnet.fact_event_logs b ON a.tx_hash = b.tx_hash
WHERE
b.contract_address = '0xf180136ddc9e4f8c9b5a9fe59e2b1f07265c5d4d'
AND b.origin_to_address = '0xf180136ddc9e4f8c9b5a9fe59e2b1f07265c5d4d'
AND b.origin_from_address NOT IN ('0x0000000000000000000000000000000000000000')
),
extracted_names AS (
SELECT
tx_hash,
"Address",
TO_VARCHAR(TO_BINARY(SUBSTR(domain_name_hex, 3), 'HEX'), 'UTF-8') AS domain_name,
"Total $BERA paid"
FROM
datatbl
)
SELECT
"Address",
COUNT(DISTINCT domain_name) AS "Total ZNS domains minted",
SUM("Total $BERA paid") AS "Total $BERA paid"
FROM
extracted_names
WHERE
domain_name IS NOT NULL
AND domain_name <> ''
GROUP BY
"Address"
Auto-refreshes every 12 hours
QueryRunArchived: QueryRun has been archived