permaryDaily unique contract upload and invocation
    Updated 5 days ago
    with daily_operations as (
    select
    date_trunc('day', closed_at) as operation_date,
    soroban_operation_type,
    count(distinct case
    when soroban_operation_type = 'upload_wasm' then contract_code_hash
    when soroban_operation_type = 'invoke_contract' then contract_id
    end) as unique_count
    from stellar.core.fact_operations
    where soroban_operation_type in ('upload_wasm', 'invoke_contract')
    and closed_at >= dateadd('month', -12, current_timestamp) -- fetch last 12 months
    group by 1, 2
    )
    select
    operation_date,
    sum(case when soroban_operation_type = 'upload_wasm' then unique_count else 0 end) as daily_unique_wasm_uploads,
    sum(case when soroban_operation_type = 'invoke_contract' then unique_count else 0 end) as daily_unique_contract_invocations,
    sum(sum(case when soroban_operation_type = 'upload_wasm' then unique_count else 0 end))
    over (order by operation_date) as cumulative_unique_wasm_uploads,
    sum(sum(case when soroban_operation_type = 'invoke_contract' then unique_count else 0 end))
    over (order by operation_date) as cumulative_unique_contract_invocations
    from daily_operations
    group by 1
    order by operation_date desc;

    Last run: 5 days ago
    OPERATION_DATE
    DAILY_UNIQUE_WASM_UPLOADS
    DAILY_UNIQUE_CONTRACT_INVOCATIONS
    CUMULATIVE_UNIQUE_WASM_UPLOADS
    CUMULATIVE_UNIQUE_CONTRACT_INVOCATIONS
    1
    2025-03-13 00:00:00.000113585019324
    2
    2025-03-12 00:00:00.000719084919189
    3
    2025-03-11 00:00:00.0001318384218999
    4
    2025-03-10 00:00:00.000420082918816
    5
    2025-03-09 00:00:00.000115482518616
    6
    2025-03-08 00:00:00.000116582418462
    7
    2025-03-07 00:00:00.000317682318297
    8
    2025-03-06 00:00:00.000217482018121
    9
    2025-03-05 00:00:00.000215481817947
    10
    2025-03-04 00:00:00.000315281617793
    11
    2025-03-03 00:00:00.000013381317641
    12
    2025-03-02 00:00:00.000112581317508
    13
    2025-03-01 00:00:00.000112381217383
    14
    2025-02-28 00:00:00.000112881117260
    15
    2025-02-27 00:00:00.000112181017132
    16
    2025-02-26 00:00:00.000112280917011
    17
    2025-02-25 00:00:00.000314080816889
    18
    2025-02-24 00:00:00.000012480516749
    19
    2025-02-23 00:00:00.000011480516625
    20
    2025-02-22 00:00:00.000111180516511
    ...
    366
    15KB
    2s