flipside

    03. Stats
    FatemeTheLady03. Stats
    Updated 2025-02-10
    WITH cte AS (
    SELECT
    TRUNC(block_timestamp, 'day') AS date,
    tx_hash,
    origin_from_address,
    POOL_NAME,
    PLATFORM,
    token_in,
    token_out,
    symbol_in,
    symbol_out,
    CASE
    WHEN amount_in_Usd IS NULL THEN amount_out_usd
    ELSE amount_in_usd
    END AS volume
    FROM
    avalanche.defi.ez_dex_swaps
    WHERE
    TRUNC(block_timestamp, 'day') >= '2024-12-01'
    )
    SELECT
    cte.date as date,
    COUNT(DISTINCT cte.tx_hash) AS "swaps",
    SUM(COUNT(DISTINCT cte.tx_hash)) OVER (ORDER BY cte.date ASC) AS "Cumulative Swaps",
    COUNT(DISTINCT cte.origin_from_address) AS "swappers",
    SUM(cte.volume) AS "Volume (USD)",
    SUM(cte.volume) / COUNT(DISTINCT cte.origin_from_address) AS "Average Volume Per Swapper",
    AVG(cte.volume) AS "Avg Volume (USD)",
    MEDIAN(cte.volume) AS "Median Volume (USD)",
    MAX(cte.volume) AS "Max volume (USD)",
    COUNT(DISTINCT cte.POOL_NAME) AS "Pools",
    ROUND(
    AVG("Volume (USD)") OVER (
    ORDER BY cte.date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
    )
    ) AS "7day moving Avg volume",
    Last run: 2 months ago
    DATE
    swaps
    Cumulative Swaps
    swappers
    Volume (USD)
    Average Volume Per Swapper
    Avg Volume (USD)
    Median Volume (USD)
    Max volume (USD)
    Pools
    7day moving Avg volume
    7day moving Avg user count
    1
    		2024-12-01 00:00:00.00049719497197427206837696.7127849.4273205873002.870161295288.8552446390.689072068376977427
    2
    		2024-12-02 00:00:00.0001170181667379263685483355.474002.3054517974079.044066647658.5651897133.8211324461605268345
    3
    		2024-12-03 00:00:00.0001018172685549821509771760.4651906.298794423647.94951024611.43901795.2810034673642718837
    4
    		2024-12-04 00:00:00.0009494836350210306484538215.6847015.1577411223755.440624385558.52045362.949914716577579204
    5
    		2024-12-05 00:00:00.000689514324539325354430543.9638008.6374219843609.088579604430.991449516.879434482123149228
    6
    		2024-12-06 00:00:00.000537774862307744254416363.4232853.352714363380.633873527499.95938725.719084159129898981
    7
    		2024-12-07 00:00:00.000416715279018450146403285.517325.832603552691.880145991295.1998854.419223774116038905
    8
    		2024-12-08 00:00:00.000416155695167334177847328.4824249.7039105543160.157228046384.121816773.399273524660698709
    9
    		2024-12-09 00:00:00.000673516368677204437479833.8160727.350612164278.112984647656.491336072.1710023812963368681
    10
    		2024-12-10 00:00:00.000770267138936552380553182.3458081.987536633384.770946981672.112316625.439543431800648342
    11
    		2024-12-11 00:00:00.000483317622247144203379331.9528468.5515047593048.616919745484.11530923.919273048810118007
    12
    		2024-12-12 00:00:00.000680008302247751340659906.4943950.4459411693421.001481136495.731085828.4811142868962227688
    13
    		2024-12-13 00:00:00.000468118770356854197810724.6428860.6251298513012.743681501436.32563548.658942673187457379
    14
    		2024-12-14 00:00:00.000369559139906568136135455.0820727.0790316692683.212217755382.035900895.58392525336317232
    15
    		2024-12-15 00:00:00.000407629547526489165669180.0225530.7720789032896.010558683396.6451499299.38212549418686987
    16
    		2024-12-16 00:00:00.0005498210097347472261570531.5935006.7627930943190.001238948285.341799508.620542654072687004
    17
    		2024-12-17 00:00:00.0006723710769717431228097688.6830695.4230493882079.153460399195.361441666.7912952392345007033
    18
    		2024-12-18 00:00:00.0007918211561538189303905903.9737111.4792978392323.0844211131961000994.9913942296535907237
    19
    		2024-12-19 00:00:00.0009715612533098047349294238.7643406.7651000372093.826549175167.57981050.6214912478929547350
    20
    		2024-12-20 00:00:00.00010802713613367535326681755.4943355.2429316521697.189145539140.8251001994.8714222461456857323
    72
    9KB
    3s