flipside

    weekly
    datavortexweekly
    Updated 2025-04-22
    WITH EthereumDailyPrices AS (
    SELECT
    DATE_TRUNC('week', hour) AS week_start
    ,AVG(price) AS avg_weekly_price
    FROM ethereum.price.ez_prices_hourly
    WHERE token_address = LOWER('0x54991328ab43c7d5d31c19d1b9fa048e77b5cd16')
    GROUP BY DATE_TRUNC('week', hour)
    ),
    EthereumClassifiedSwaps AS (
    SELECT
    block_timestamp
    ,DATE_TRUNC('week', block_timestamp) AS week_start
    ,tx_hash
    ,origin_from_address AS wallet
    ,contract_address AS contract
    ,(decoded_log:amount0) / 1e18 AS soil_amount_tokens
    ,CASE
    WHEN CAST(decoded_log:amount0 AS DECIMAL) < 0 THEN 'Buy SOIL'
    WHEN CAST(decoded_log:amount0 AS DECIMAL) > 0 THEN 'Sell SOIL'
    ELSE 'Unknown'
    END AS buy_soil_or_sell_soil
    ,ABS((decoded_log:amount0) / 1e18) * dp.avg_weekly_price AS volume_usd
    FROM ethereum.core.ez_decoded_event_logs
    LEFT JOIN EthereumDailyPrices dp ON DATE_TRUNC('week', block_timestamp) = dp.week_start
    WHERE contract_address = LOWER('0x7090Cc8DFA04b0BBAc6520063c9c1ea677C368a0')
    AND event_name = 'Swap'
    ),
    PolygonDailyPrices AS (
    SELECT
    DATE_TRUNC('week', hour) AS week_start
    ,AVG(price) AS avg_weekly_price
    FROM polygon.price.ez_prices_hourly
    WHERE token_address = LOWER('0x43C73b90E0C2A355784dCf0Da12f477729b31e77')
    GROUP BY DATE_TRUNC('week', hour)
    ),
    PolygonClassifiedSwaps AS (
    Last run: 29 days ago
    week_starting
    weekly wallets
    weekly swaps
    weekly volume usd
    cumulative wallets
    cumulative swaps
    cumulative volume usd
    1
    		2023-10-09 00:00:00.0002691670317696.2439736382691670317696.243973638
    2
    		2023-10-16 00:00:00.000110651103474.2332515963792321421170.477225234
    3
    		2023-10-23 00:00:00.0006630041383.5674889394452621462554.044714173
    4
    		2023-10-30 00:00:00.0008146471431.511058655263085533985.555772823
    5
    		2023-11-06 00:00:00.0008038451235.2275373756063469585220.783310198
    6
    		2023-11-13 00:00:00.0005121824698.8003784556573687609919.583688653
    7
    		2023-11-20 00:00:00.0002291667261461.7366149518865354871381.320303605
    8
    		2023-11-27 00:00:00.0001671226228593.302705355105365801099974.62300896
    9
    		2023-12-04 00:00:00.0002181133226590.65362235127177131326565.27663131
    10
    		2023-12-11 00:00:00.000184708129102.64244074145584211455667.91907205
    11
    		2023-12-18 00:00:00.0002581609289286.6548912941713100301744954.57396334
    12
    		2023-12-25 00:00:00.0002481328266818.0721802561961113582011772.6461436
    13
    		2024-01-01 00:00:00.0003371503332274.3332103552298128612344046.97935395
    14
    		2024-01-08 00:00:00.000237841226800.4501565222535137022570847.42951048
    15
    		2024-01-15 00:00:00.000169673140782.2917852992704143752711629.72129578
    16
    		2024-01-22 00:00:00.0002681208200317.283135462972155832911947.00443124
    17
    		2024-01-29 00:00:00.000182529106148.7604764133154161123018095.76490765
    18
    		2024-02-05 00:00:00.000208671124002.6622173243362167833142098.42712497
    19
    		2024-02-12 00:00:00.000199641136327.6699299983561174243278426.09705497
    20
    		2024-02-19 00:00:00.000189661136712.9260227853750180853415139.02307776
    81
    6KB
    134s