SocioCryptodaily smart contracts
    Updated 2023-01-18
    SELECT 'terra' as chain,
    avg(n_contracts) as avg_n_active_contracts
    FROM
    (SELECT
    date_Trunc('day',block_timestamp) as date,
    COUNT(DISTINCT tm.attribute_value) as n_contracts
    FROM terra.core.fact_msg_attributes tm
    LEFT JOIN terra.core.dim_address_labels tl
    ON tm.attribute_value = tl.address
    WHERE tm.attribute_key IN ('_contract_address', 'contract_addr', 'contract_address', 'contract_name', 'contract')
    AND date between '2022-05-26' AND '2022-12-31'
    GROUP BY date
    )
    UNION
    SELECT 'near' as chain,
    avg(n_contracts) as avg_n_active_contracts
    FROM
    (SELECT
    date_Trunc('day',x.block_timestamp) as date,
    count(DISTINCT receiver_id) as n_contracts
    FROM near.core.fact_actions_events x
    LEFT JOIN near.core.fact_receipts y
    ON x.tx_hash=y.tx_hash
    WHERE action_name = 'DeployContract'
    AND date between '2022-05-26' AND '2022-12-31'
    GROUP by date
    )
    UNION
    SELECT 'ethereum' as chain,
    avg(n_contracts) as avg_n_active_contracts
    FROM
    (SELECT
    date_trunc('day',block_timestamp) as date,
    count(DISTINCT contract_address) as n_contracts
    FROM ethereum.core.fact_event_logs
    WHERE date between '2022-05-26' AND '2022-12-31'