check_skedOptimism User Data copy
    Updated 2024-02-05
    WITH
    query1 AS (
    SELECT
    DATE_TRUNC('day', ft.block_timestamp) AS dt,
    COUNT(
    DISTINCT CASE
    WHEN dl.label_type = 'dex' THEN ft.from_address
    END
    ) AS dex_count,
    COUNT(
    DISTINCT CASE
    WHEN dl.label_type = 'defi' THEN ft.from_address
    END
    ) AS defi_count,
    COUNT(
    DISTINCT CASE
    WHEN dl.label_type = 'dapp' THEN ft.from_address
    END
    ) AS dapp_count,
    COUNT(
    DISTINCT CASE
    WHEN dl.label_type = 'nft' THEN ft.from_address
    END
    ) AS nft_count,
    COUNT(
    DISTINCT CASE
    WHEN dl.label_type = 'layer2' THEN ft.from_address
    END
    ) AS layer2_count
    FROM
    optimism.core.fact_transactions ft
    JOIN optimism.core.dim_labels dl ON ft.to_address = dl.address
    WHERE
    ft.block_timestamp >= CAST('2023-08-01' AS TIMESTAMP)
    AND dl.label_type IN ('dex', 'defi', 'dapp', 'nft', 'layer2')
    AND from_address IN (
    Last run: over 1 year ago
    DT
    DEX_COUNT
    DEFI_COUNT
    DAPP_COUNT
    NFT_COUNT
    LAYER2_COUNT
    INTERACTIONS_1
    INTERACTIONS_2_TO_5
    INTERACTIONS_5_TO_7
    INTERACTIONS_7_TO_10
    INTERACTIONS_10_PLUS
    DEFI_USERS
    SMA_30D_DEX_COUNT
    SMA_30D_DEFI_COUNT
    SMA_30D_DAPP_COUNT
    SMA_30D_NFT_COUNT
    SMA_30D_LAYER2_COUNT
    SMA_30D_INTERACTIONS_1
    SMA_30D_INTERACTIONS_2_TO_5
    SMA_30D_INTERACTIONS_5_TO_7
    SMA_30D_INTERACTIONS_7_TO_10
    SMA_30D_INTERACTIONS_10_PLUS
    1
    2023-08-02 00:00:00.000110000100011100001000
    2
    2023-08-03 00:00:00.000221000110021.51.50.500010.500
    3
    2023-08-10 00:00:00.000110000100011.3331.3330.33300010.33300
    4
    2023-08-11 00:00:00.000111000100011.251.250.500010.2500
    5
    2023-08-13 00:00:00.000110000100011.21.20.400010.200
    6
    2023-08-14 00:00:00.000110000100011.1661.1660.33300010.16600
    7
    2023-08-15 00:00:00.000110002000021.1421.1420.285000.2850.8570.14200
    8
    2023-08-16 00:00:00.000110000100011.1251.1250.25000.250.8750.12500
    9
    2023-08-17 00:00:00.000110100010011.1111.1110.2220.11100.2220.7770.22200
    10
    2023-08-18 00:00:00.000111001100021.11.10.30.100.30.80.200
    11
    2023-08-21 00:00:00.00000100100001110.3630.0900.3630.7270.18100
    12
    2023-08-22 00:00:00.0001000010000110.9160.3330.08300.4160.6660.16600
    13
    2023-08-24 00:00:00.000001000100010.9230.8460.3840.07600.3840.6920.15300
    14
    2023-08-25 00:00:00.000100001000010.9280.7850.3570.07100.4280.6420.14200
    15
    2023-08-26 00:00:00.000002002000020.8660.7330.4660.06600.5330.60.13300
    16
    2023-08-28 00:00:00.000001001000010.8120.6870.50.06200.5620.5620.12500
    17
    2023-08-31 00:00:00.000011000100010.7640.7050.5290.05800.5290.5880.11700
    18
    2023-09-01 00:00:00.000011001100020.7220.7220.5550.05500.5550.6110.11100
    19
    2023-09-02 00:00:00.000000101000010.6840.6840.5260.10500.5780.5780.10500
    20
    2023-09-03 00:00:00.000101002000020.70.650.550.100.650.550.100
    ...
    139
    12KB
    14s