Query editor
-- Ethereum /-- Solana /-- BNB /-- Avalanche /-- Tron /-- Sei /-- Sui-- Aptos /-- ICP-- NEAR /-- Ton-- Monad xx-- Berachain xx------------------------------------------------------ GOAL-- 1. To get daily transactions count-- 2. To get gas earned per day-- 3. To get Gas Per Transactions-- 4. To get Active AddressesWITH fundamental_metrics AS(SELECTDATE_TRUNC ('day', block_time)"time",COUNT (DISTINCT (hash)) AS daily_transactions,COUNT (DISTINCT (sender)) AS daily_active_users,(AVG(gas_used / 1e18 * gas_unit_price)) AS average_gas_apt,SUM (gas_used / 1e18 * gas_unit_price) AS daily_gas_spent_aptFROM aptos.user_transactionsWHERE DATE_TRUNC ('day', block_time) > TRY_CAST('2023-12-31' AS TIMESTAMP)AND DATE_TRUNC ('day', block_time) < CURRENT_DATEGROUP BY 1),apt_price AS (SELECTDATE_TRUNC ('day', "minute") "time",AVG(price) AS daily_priceFROM prices.usdWHERE symbol = 'APT'AND DATE_TRUNC ('day', "minute") > TRY_CAST('2023-12-31' AS TIMESTAMP)AND DATE_TRUNC ('day', "minute") < CURRENT_DATEGROUP BY 1)SELECTf."time",f.daily_transactions,f.daily_active_users,f.average_gas_apt,(f.average_gas_apt * p.daily_price) AS average_gas_usd,f.daily_gas_spent_apt,(f.daily_gas_spent_apt * p.daily_price) AS daily_gas_spent_usdFROM fundamental_metrics fLEFT JOIN apt_price pON f."time" = p."time"ORDER BY "time" DESC