anitjoin-needed.md

SELECT
    sum(sum_tx_ether),
    sum(avg_tx_gas_cost)
FROM
(
    SELECT
        SUM(value / POWER(10, 18)) AS sum_tx_ether,
        AVG(gas_price * (receipt_gas_used / POWER(10, 18))) AS avg_tx_gas_cost,
        toStartOfDay(block_timestamp) AS tx_date
    FROM transactions
    WHERE (receipt_status = 1) AND (value > 0) AND (block_timestamp > '2018-01-01')
    GROUP BY tx_date
)

┌─sum(sum_tx_ether)─┬─sum(avg_tx_gas_cost)─┐
│ 4534755546.475251 │   4.5691305103099875 │
└───────────────────┴──────────────────────┘

SELECT
    sum(sum_tx_ether),
    sum(avg_tx_gas_cost)
FROM
(
    SELECT
        SUM(value / POWER(10, 18)) AS sum_tx_ether,
        AVG(gas_price * (receipt_gas_used / POWER(10, 18))) AS avg_tx_gas_cost,
        toStartOfDay(block_timestamp) AS tx_date
    FROM transactions
    ANY LEFT JOIN blocks ON block_number = blocks.number
    WHERE (receipt_status = 1) AND (value > 0) AND (block_timestamp > '2018-01-01')
    GROUP BY tx_date
)

┌─sum(sum_tx_ether)─┬─sum(avg_tx_gas_cost)─┐
│ 4534755546.475264 │   4.5691305103100035 │
└───────────────────┴──────────────────────┘