cos_uniadd_tb AS (SELECT COUNT(DISTINCT TX_FROM) AS unique_address FROM cosmos.core.fact_transactions),

tb AS (

SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS dt,

'cosmos' AS name,

COUNT(*) AS tx,

COUNT(DISTINCT TX_FROM) AS dau,

AVG(GAS_USED) AS avg_gas_price,

AVG(unique_address) AS unique_address,

NULL AS contracts_deployed

FROM cosmos.core.fact_transactions OUTER JOIN cos_uniadd_tb

WHERE DATE(BLOCK_TIMESTAMP) = CURRENT_DATE - 1 AND TX_SUCCEEDED = TRUE

GROUP BY dt

)

SELECT * FROM tb ORDER BY name, dt