SELECT

PLATFORM,

SWAPPER,

COUNT(*) AS swap_count,

SUM(AMOUNT_IN_USD) AS total_volume,

AVG(AMOUNT_IN_USD) AS avg_swap_size,

AVG(GAS_USED * GAS_UNIT_PRICE / POW(10, 3)) AS avg_swap_cost_usd,

AVG(DATEDIFF(second, eds.BLOCK_TIMESTAMP, eds.INSERTED_TIMESTAMP)) AS avg_swap_speed_seconds

FROM

aptos.defi.ez_dex_swaps eds join aptos.core.fact_transactions ft on eds.tx_hash=ft.tx_hash

WHERE

eds.BLOCK_TIMESTAMP >= DATEADD(day, -{{days}}, CURRENT_DATE)

GROUP BY

PLATFORM, SWAPPER

),

platform_scores AS (

SELECT

PLATFORM,

SUM(total_volume) AS total_volume,

COUNT(*) AS total_swaps,

COUNT(DISTINCT SWAPPER) AS unique_traders,

AVG(swap_count) AS avg_swaps_per_user,

AVG(avg_swap_cost_usd) AS avg_swap_cost_usd,

AVG(avg_swap_speed_seconds) AS avg_swap_speed_seconds

FROM

platform_metrics

GROUP BY

PLATFORM

),

ranked_scores AS ( --pondera en funcio de rellevancia

SELECT

PLATFORM,

RANK() OVER (ORDER BY total_volume DESC) * 0.1 AS volume_score,

RANK() OVER (ORDER BY total_swaps DESC) * 0.1 AS swap_count_score,

RANK() OVER (ORDER BY unique_traders DESC) * 0.1 AS trader_score,