Query editor
WITH latest_day AS (SELECT MAX(block_date) AS dFROM ethereum.transactions),periods AS (SELECTd,d - INTERVAL '6' DAY AS cur_start,d - INTERVAL '13' DAY AS prev_start,d - INTERVAL '7' DAY AS prev_endFROM latest_day),btc_tx AS (SELECTSUM(CASE WHEN t.block_date BETWEEN p.cur_start AND p.d THEN 1 ELSE 0 END) AS cur,SUM(CASE WHEN t.block_date BETWEEN p.prev_start AND p.prev_end THEN 1 ELSE 0 END) AS prevFROM bitcoin.transactions tCROSS JOIN periods pWHERE t.block_date BETWEEN (SELECT prev_start FROM periods) AND (SELECT d FROM periods)),eth_tx AS (SELECTSUM(CASE WHEN t.block_date BETWEEN p.cur_start AND p.d THEN 1 ELSE 0 END) AS cur,SUM(CASE WHEN t.block_date BETWEEN p.prev_start AND p.prev_end THEN 1 ELSE 0 END) AS prevFROM ethereum.transactions tCROSS JOIN periods pWHERE t.block_date BETWEEN (SELECT prev_start FROM periods) AND (SELECT d FROM periods)),btc_cex_addresses AS (SELECT DISTINCT from_utf8(address) AS addressFROM cex.addressesWHERE blockchain = 'bitcoin'),btc_cex_daily AS (SELECTday,SUM(inflow) AS inflow,SUM(outflow) AS outflow,SUM(inflow - outflow) AS netFROM (SELECT o.block_date AS day, SUM(o.value) AS inflow, 0.0 AS outflowFROM bitcoin.outputs oJOIN btc_cex_addresses a ON o.address = a.addressWHERE o.block_date BETWEEN (SELECT prev_start FROM periods) AND (SELECT d FROM periods)GROUP BY 1UNION ALLSELECT i.block_date AS day, 0.0 AS inflow, SUM(i.value) AS outflowFROM bitcoin.inputs iJOIN btc_cex_addresses a ON i.address = a.addressWHERE i.block_date BETWEEN (SELECT prev_start FROM periods) AND (SELECT d FROM periods)GROUP BY 1) xGROUP BY 1),btc_cex_week AS (SELECTSUM(CASE WHEN day BETWEEN p.cur_start AND p.d THEN net ELSE 0 END) AS cur,SUM(CASE WHEN day BETWEEN p.prev_start AND p.prev_end THEN net ELSE 0 END) AS prev