Query editor
WITH protocol_transactions AS (SELECT date_trunc('day', block_time) AS block_date,block_time,block_height,id AS transaction_id,output_count,fee,virtual_size,CASEWHEN cast(hex AS VARCHAR) LIKE '%ff7f81%' THEN 'alkanes'WHEN cast(hex AS VARCHAR) LIKE '%0000000000000000__6a5d%'AND cast(hex AS VARCHAR) NOT LIKE '%ff7f81%' THEN 'runes'WHEN cast(hex AS VARCHAR) LIKE '%0063036f726401%'AND cast(hex AS VARCHAR) LIKE '%6272632d3230%' THEN 'brc-20'WHEN cast(hex AS VARCHAR) LIKE '%0063036f726401%'AND cast(hex AS VARCHAR) NOT LIKE '%6272632d3230%' THEN 'ordinals'END AS pattern_typeFROM bitcoin.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '28' DAYAND (cast(hex AS VARCHAR) LIKE '%ff7f81%'OR cast(hex AS VARCHAR) LIKE '%0000000000000000__6a5d%'OR cast(hex AS VARCHAR) LIKE '%0063036f726401%')),price AS (SELECT date_trunc('day', minute) AS price_date,AVG(price) AS avg_daily_priceFROM prices.usdWHERE symbol = 'BTC'AND blockchain IS NULLAND minute >= CURRENT_DATE - INTERVAL '28' DAYGROUP BY date_trunc('day', minute)),all_daily_totals AS (SELECT date_trunc('day', block_time) AS block_date,COUNT(*) AS total_daily_tx_countFROM bitcoin.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '28' DAYGROUP BY date_trunc('day', block_time))-- Daily aggregation querySELECT pt.block_date,pt.pattern_type,CASEWHEN pt.pattern_type = 'alkanes' THEN 'alkanes'ELSE pt.pattern_typeEND AS category,COUNT(*) AS daily_tx_count,adt.total_daily_tx_count, -- Total ALL Bitcoin transactions for that dayROUND(COUNT(*) * 100.0 / adt.total_daily_tx_count, 2) AS percentage_of_total, -- Bonus: percentage calculationSUM(pt.fee) AS daily_total_fee_sats,SUM(pt.fee * p.avg_daily_price) AS daily_total_fee_USD,AVG(pt.fee) AS avg_fee_per_tx_sats,AVG(pt.fee * p.avg_daily_price) AS avg_fee_per_tx_USD,SUM(pt.virtual_size) AS daily_total_vsize,AVG(pt.virtual_size) AS avg_vsize_per_tx,p.avg_daily_price AS btc_priceFROM protocol_transactions AS ptLEFT JOIN price AS p ON p.price_date = pt.block_dateLEFT JOIN all_daily_totals AS adt ON adt.block_date = pt.block_dateWHERE pt.pattern_type IS NOT NULL