Query editor
WITH hourly_eth_price AS (WITH gs AS (SELECT generate_series(DATE_TRUNC('hour',NOW())-interval '38 days',DATE_TRUNC('hour',NOW()),interval '1 hour') AS hour)SELECT gs.hour, ep.median_priceFROM gsLEFT JOIN (SELECT hour, median_price, lead(hour, 1, NOW() + interval '1 hour') OVER (ORDER BY hour ASC) AS next_hourFROM (SELECT hour, median_price FROM prices."approx_prices_from_dex_data" pWHERE "hour" > NOW() - interval '31 days'AND "contract_address" = '\x4200000000000000000000000000000000000006'AND median_price IS NOT NULL) rawdata) epON gs.hour >= ep.hourAND gs.hour < ep.next_hour), sample_txs AS (SELECT evt_tx_hash,tx_type, cat_list, projectFROM (SELECT hash AS evt_tx_hash, block_time AS evt_block_time,'ETH' AS project, 'ETH Transfer' AS tx_type, 1 AS cat_list FROM optimism.transactions WHERE value > 0 AND data IS NULL AND block_time > NOW() - interval '30 days'UNION ALL-- SELECT hash, block_time, 'ERC20' AS project, 'ERC20 Transfer' AS tx_type, 2 AS cat_list FROM optimism.transactions t WHERE get_method_id(data) = 'a9059cbb' AND block_time > NOW() - interval '30 days'-- UNION ALL-- SELECT evt_tx_hash, evt_block_time,'ERC20' AS project, 'ERC20 - Approval' AS tx_type, 3 AS cat_list FROM erc20."ERC20_evt_Approval" WHERE evt_block_time > NOW() - interval '30 days'-- UNION ALLSELECT evt_tx_hash, evt_block_time, 'Uniswap V3' AS project, 'Uniswap - Trade' AS tx_type, 4 AS cat_list FROM uniswap_v3."Pair_evt_Swap" s WHERE evt_block_time > NOW() - interval '30 days'UNION ALLSELECT hash, block_time, 'Synthetix' AS project, 'Synthetix - Issue sUSD' AS tx_type, 5 AS cat_list FROM optimism.transactions WHERE "to" = '\x8700daec35af8ff88c16bdf0418774cb3d7599b4' AND get_method_id(data) = 'af086c7e' /*issue max synths*/ AND block_time > NOW() - interval '30 days'UNION ALLSELECT evt_tx_hash, evt_block_time, 'Lyra V1' AS project, 'Lyra - Buy/Sell Option' AS tx_type, 6 AS cat_list FROM lyra_v1."OptionMarket_evt_PositionOpened" WHERE evt_block_time > NOW() - interval '30 days'UNION ALL-- SELECT evt_tx_hash, evt_block_time, 'Celer' AS project, 'Celer - Bridge Out' AS tx_type, 7 AS cat_list FROM celer."Bridge_evt_Send" WHERE evt_block_time > NOW() - interval '30 days'-- UNION ALL-- SELECT evt_tx_hash, evt_block_time, 'Hop Protocol' AS project, 'Hop - Bridge Out' AS tx_type, 8 AS cat_list FROM hop_protocol."L2_OptimismBridge_evt_TransferSent" WHERE evt_block_time > NOW() - interval '30 days'-- UNION ALLSELECT evt_tx_hash, evt_block_time, 'Perpetual Protocol' AS project, 'Perpetual Protocol - Trade' AS tx_type, 9 AS cat_list FROM perp_v2."ClearingHouse_evt_PositionChanged" WHERE evt_block_time > NOW() - interval '30 days'UNION ALLSELECT evt_tx_hash, evt_block_time, 'Quixotic' AS project, 'Quixotic - Exchange NFT' AS tx_type, 10 AS cat_list FROM quixotic."Exchange_evt_SellOrderFilled" WHERE evt_block_time > NOW() - interval '30 days'UNION ALLSELECT evt_tx_hash, evt_block_time, 'Quixotic' AS project, 'Quixotic - Exchange NFT' AS tx_type, 10 AS cat_list FROM quixotic_v2."ExchangeV2_evt_SellOrderFilled" WHERE evt_block_time > NOW() - interval '30 days'UNION ALLSELECT evt_tx_hash, evt_block_time, 'Quixotic' AS project, 'Quixotic - Exchange NFT' AS tx_type, 10 AS cat_list FROM quixotic_v3."ExchangeV3_evt_SellOrderFilled" WHERE evt_block_time > NOW() - interval '30 days') t), final_data AS (SELECT dt, num_transactions,tx_type,current_gas_fee_eth,current_gas_fee_usd, AVG(med_l1_gas_price) OVER (PARTITION BY dt) AS med_l1_gas_price,l1_equivalent_gas_fee_eth::decimal/current_gas_fee_eth::decimal AS multiple_savings_num,ROUND(l1_equivalent_gas_fee_eth::decimal/current_gas_fee_eth::decimal,0)::text||'x' AS multiple_savings,l1_equivalent_gas_fee_eth,l1_equivalent_gas_fee_usd,