SELECT

date_trunc('week', block_timestamp) as week,

amount_in_usd,

amount_out_usd

FROM base.defi.ez_dex_swaps

WHERE contract_address = lower('0x0FAc819628a7F612AbAc1CaD939768058cc0170c')

AND block_timestamp >= '2024-07-01'

),

weekly_fees AS (

SELECT

week,

SUM(GREATEST(COALESCE(amount_in_usd, 0), COALESCE(amount_out_usd, 0))) * 0.0005 as weekly_fee, -- 0.05% fee

COUNT(*) as swap_count,

SUM(amount_in_usd) as total_in_usd,

SUM(amount_out_usd) as total_out_usd

FROM pool_swaps

GROUP BY 1

SELECT

week,

swap_count,

total_in_usd,

total_out_usd,

weekly_fee as raw_fees,

weekly_fee * 7 * 52 as annualized_fees, -- Annualizing the weekly fee

weekly_fee * 50 as annualized_fees_50x

FROM weekly_fees

ORDER BY week DESC;