SocioCryptoAverage daily smart contracts
    Updated 2023-04-13
    SELECT
    CASE WHEN date between '2023-01-07' AND '2023-01-13' THEN 'before'
    WHEN date between '2023-01-15' AND '2023-01-21' THEN 'after'
    ELSE 'announcement' END as periods,
    avg(n_contracts) as avg_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 '2023-01-07' AND '2023-01-21'
    GROUP BY date
    )
    GROUP BY periods