block_timestamp::DATE,

COUNT(1) AS swap_count,

SUM(from_amount_usd * affiliate_fee_basis_points / 1e5) AS affiliate_fees_usd,

SUM(liq_fee_rune_usd) AS liquidity_fees_usd,

SUM((from_amount_usd * affiliate_fee_basis_points / 1e5) + liq_fee_rune_usd) AS total_fees_usd,

SUM(from_amount_usd * affiliate_fee_basis_points / 1e5)

/ SUM((from_amount_usd * affiliate_fee_basis_points / 1e5) + liq_fee_rune_usd) AS affiliate_fees_pct,

SUM(liq_fee_rune_usd)

/ SUM((from_amount_usd * affiliate_fee_basis_points / 1e5) + liq_fee_rune_usd) AS liq_fees_pct,

CASE

WHEN SUM(liq_fee_rune_usd) = 0 THEN 0

ELSE SUM(from_amount_usd * affiliate_fee_basis_points / 1e5) / SUM(liq_fee_rune_usd)

END AS affiliate_fee_to_liquidity_fee_ratio

FROM thorchain.defi.fact_swaps

WHERE

block_timestamp >= DATEADD(day, -1*{{days_behind}}::INT, CURRENT_DATE)

AND affiliate_address IS NOT NULL

AND affiliate_address <> '0'

GROUP BY 1

ORDER BY 1