rajsContracts Op, Arb and ETH
    Updated 2022-08-04
    with op_contracts as
    (
    SELECT
    contract_address,
    min(block_timestamp) as joined_date,
    (case when sum(case when topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' and topics[3] is NULL
    then 1 else 0 end) > 0
    then True else False end) as is_erc20_contract,
    (case when sum(case when topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' and topics[3] is NOT NULL
    then 1 else 0 end) > 0
    then True else False end) as is_erc721_contract
    from optimism.core.fact_event_logs
    group by 1
    )
    ,

    op_daily_new_contracts as
    (
    SELECT
    date_trunc('day', joined_date) as date,
    count(*) as op_new_contracts,
    count(case when is_erc20_contract then contract_address else null end) as op_new_erc20_contracts,
    count(case when is_erc721_contract then contract_address else null end) as op_new_erc721_contracts
    from op_contracts
    group by 1
    )
    ,

    arb_contracts as
    (
    SELECT
    contract_address,
    min(block_timestamp) as joined_date,
    (case when sum(case when topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' and topics[3] is NULL
    then 1 else 0 end) > 0
    then True else False end) as is_erc20_contract,
    Run a query to Download Data