Query editor
WITH fees_data AS (SELECT COALESCE(bs.blob_submitter_label, CASE WHEN bs.is_blobscription THEN 'Inscriptions' END, 'Unknown') AS blob_submitter, SUM(pu.price * CAST(bs.execution_fee_wei AS DOUBLE) / 1e18)FILTER (WHERE bs.block_time > NOW() - INTERVAL '3' DAY) AS mainnet_fee_usd_3d, SUM(pu.price * CAST(bs.execution_fee_wei AS DOUBLE) / 1e18) AS mainnet_fee_usd_total, SUM(pu.price * CAST(bs.blob_fee_wei AS DOUBLE) / 1e18)FILTER (WHERE bs.block_time > NOW() - INTERVAL '3' DAY) AS submission_fee_usd_3d, SUM(pu.price * CAST(bs.blob_fee_wei AS DOUBLE) / 1e18) AS submission_fee_usd_total, SUM(pu.price * CAST(bs.total_fee_wei AS DOUBLE) / 1e18)FILTER (WHERE bs.block_time > NOW() - INTERVAL '3' DAY) AS total_fee_usd_3d, SUM(pu.price * CAST(bs.total_fee_wei AS DOUBLE) / 1e18) AS total_fee_usd_totalFROM dune.dragonfly.ethereum_blob_submissions bsLEFT JOIN prices.usd pu ON pu.blockchain IS NULLAND pu.symbol = 'ETH'AND pu.minute = date_trunc('minute', bs.block_time)GROUP BY 1), submitter_links AS (SELECT entity, MIN(NULLIF(x_username, '')) AS x_usernameFROM dune.dragonfly.blobs_submittersGROUP BY 1), blob_time_intervals AS (WITH time_diffs AS (SELECT COALESCE(blob_submitter_label, CASE WHEN is_blobscription THEN 'Inscriptions' ELSE NULL END, 'Unknown') AS blob_submitter, date_diff('second', LAG(beacon_slot_time) OVER (PARTITION BY COALESCE(blob_submitter_label, CASE WHEN is_blobscription THEN 'Inscriptions' ELSE 'Unknown' END) ORDER BY beacon_slot_time), beacon_slot_time) AS time_intervalFROM (SELECT DISTINCT beacon_slot_time, blob_submitter_label, is_blobscriptionFROM dune.dragonfly.ethereum_blobsWHERE beacon_slot_time > NOW() - interval '1' day) unique_times)SELECT blob_submitter, CASEWHEN AVG(time_interval) < 60 THEN CONCAT(CAST(CAST(AVG(time_interval) AS int) AS VARCHAR), 's')ELSE CONCAT(CAST(CAST(AVG(time_interval) / 60 AS int) AS VARCHAR), 'm ',CAST(CAST(AVG(time_interval) % 60 AS int) AS VARCHAR), 's')END AS avg_time_intervalFROM time_diffsWHERE time_interval IS NOT NULLGROUP BY blob_submitter), data AS (SELECT CASE WHEN MAX(b.beacon_slot_time) > NOW() - interval '12' hour AND COUNT(*) >= 1 THEN '✅' ELSE '❌' END AS actively_posting, CASE WHEN b.blob_submitter_label IS NOT NULL THEN b.blob_submitter_labelWHEN is_blobscription THEN 'Inscriptions'ELSE 'Unknown' END AS raw_blob_submitter, CASE WHEN b.blob_submitter_label IS NOT NULL THEN COALESCE(get_href(bs.x_username, b.blob_submitter_label), b.blob_submitter_label)WHEN is_blobscription THEN 'Inscriptions'ELSE 'Unknown' END AS blob_submitter, COUNT(*) AS blob_count