SELECT

day,

CASE

WHEN from_asset LIKE '%/%' OR to_asset LIKE '%/%' THEN 'Synth'

WHEN from_asset LIKE '%~%' OR to_asset LIKE '%~%' THEN 'Trade'

ELSE 'L1'

END AS TYPE,

CASE

WHEN day < '2024-07-26' THEN 'Pre-5bps min'

WHEN day >= '2024-07-26' AND day <= '2024-08-15' THEN '5bps min'

ELSE '15bps min'

END AS Type2,

SUM(volume) AS volume,

SUM(fees) AS fees,

(SUM(fees) / SUM(volume)) * 100 AS average_fee

FROM (

SELECT

block_timestamp::date AS day,

from_asset,

to_asset,

from_amount_usd AS volume,

liq_fee_asset_usd AS fees

FROM

thorchain.defi.fact_swaps

WHERE

block_timestamp >= '2024-01-01'

) sub

GROUP BY

day,

TYPE,

Type2

HAVING

TYPE = 'Trade'

ORDER BY Day DESC;