GUNZ Daily Metrics Over Time copy copy

SalehGUNZ Daily Metrics Over Time copy copy

Updated 2024-10-21

-- forked from yasmin-n-d-r-h / GUNZ Daily Metrics Over Time copy @ https://flipsidecrypto.xyz/yasmin-n-d-r-h/q/OuJsNno15Ud5/gunz-daily-metrics-over-time-copy

-- WITH raw AS (

-- SELECT

-- BLOCK_TIMESTAMP,

-- TX_HASH,

-- input,

-- VALUE,

-- REGEXP_SUBSTR_ALL(SUBSTR(input, 11), '.{64}') AS seg,

-- CASE

-- WHEN '0x' || SUBSTR(seg[4]::string, 25) = '0x9a25044945b976cabbac9e7bbf096da526061f2f' THEN 'Collection'

-- ELSE NULL

-- END AS nft_collection

-- FROM

-- avalanche.core.fact_traces

-- WHERE

-- TO_ADDRESS = '0xd0996694bc687a3c3e5de6332e6e542ad774dd3f' -- salvor

-- AND BLOCK_TIMESTAMP >= DATEADD(MONTH, -1, CURRENT_DATE())

-- AND nft_collection IS NOT NULL

-- )

-- SELECT

-- DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS day,

-- nft_collection,

-- SUM(livequery.utils.udf_hex_to_int(seg[11]::string)::int / 1e18) as "total_avx_v"

-- FROM raw

-- GROUP BY day, nft_collection

-- ORDER BY day;

-- --------

-- WITH raw AS (

-- SELECT

-- FROM_ADDRESS,

-- TX_HASH,

-- INPUT,

QueryRunArchived: QueryRun has been archived