jackguy2023-09-30 10:31 AM
    Updated 2024-12-03
    with tab0 as (
    SELECT
    address,
    label
    from cosmos.gov.fact_validators
    ), tab1 as (
    SELECT
    tx_id as tx1,
    msg_index as mi1
    FROM cosmos.core.fact_msg_attributes
    WHERE msg_type LIKE 'send_packet'
    AND ATTRIBUTE_VALUE LIKE 'channel-391'
    ), tab2 as (
    SELECT
    block_timestamp as bt,
    tx_id as txs,
    TRY_CAST(PARSE_JSON(ATTRIBUTE_VALUE):amount::STRING AS FLOAT) / power(10, 6) as amount
    FROM cosmos.core.fact_msg_attributes
    LEFT outer JOIN tab1
    on tx1 = tx_id
    WHERE ATTRIBUTE_KEY LIKE 'packet_data'
    AND msg_type LIKE 'send_packet'
    AND LEFT(PARSE_JSON(ATTRIBUTE_VALUE):denom::STRING,
    CHARINDEX('/', PARSE_JSON(ATTRIBUTE_VALUE):denom::STRING) - 1) in (SELECT address from tab0) --2073
    ), tab3 as (
    SELECT
    DISTINCT tx_from
    FROM cosmos.core.fact_transactions
    LEFT outer JOIN tab2
    on txs = tx_id
    WHERE tx_id in (SELECT txs from tab2)
    )

    SELECT
    *,
    sum(new_users) over (ORDER BY first_week) as total_users
    Last run: 2 months ago
    FIRST_WEEK
    NEW_USERS
    TOTAL_USERS
    1
    2024-02-05 00:00:00.00083427772
    2
    2024-11-25 00:00:00.000834277
    3
    2021-02-22 00:00:00.000234507
    4
    2022-05-09 00:00:00.00029510526
    5
    2021-11-01 00:00:00.0001223857
    6
    2021-08-23 00:00:00.0001062286
    7
    2021-03-29 00:00:00.000571021
    8
    2022-02-07 00:00:00.0003357005
    9
    2024-10-14 00:00:00.0001234188
    10
    2024-10-28 00:00:00.000834206
    11
    2021-11-22 00:00:00.0001124248
    12
    2023-06-05 00:00:00.0005115823
    13
    2024-07-22 00:00:00.0005233910
    14
    2022-08-01 00:00:00.0005811509
    15
    2021-05-31 00:00:00.000431435
    16
    2022-06-13 00:00:00.0007710959
    17
    2021-02-15 00:00:00.000273273
    18
    2021-12-06 00:00:00.0001194517
    19
    2023-01-16 00:00:00.0004613222
    20
    2023-08-14 00:00:00.0003116161
    ...
    198
    7KB
    93s