SELECT

DATE_TRUNC('day', block_timestamp) AS date,

CASE

WHEN affiliate_address IN ('t', 'T', 'thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk') THEN 'THORSwap'

WHEN affiliate_address IN ('wr', 'thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8') THEN 'THORWallet'

WHEN affiliate_address IN ('ti', 'te', 'tr', 'td') THEN 'TrustWallet'

WHEN affiliate_address = 'tl' THEN 'TS Ledger'

WHEN affiliate_address = 'cb' THEN 'Team CoinBot'

WHEN affiliate_address = 'dx' THEN 'Asgardex'

WHEN affiliate_address = 'ss' THEN 'ShapeShift'

WHEN affiliate_address = 'xdf' THEN 'xDEFI'

WHEN affiliate_address = 'rg' THEN 'Rango'

WHEN affiliate_address = 'ej' THEN 'Edge Wallet'

WHEN affiliate_address = 'ds' THEN 'DefiSpot'

WHEN affiliate_address = 'lifi' then 'LiFi'

WHEN affiliate_address = 'oky' then 'OneKey Wallet'

WHEN affiliate_address = 'sy' then 'Symbiosis'

WHEN affiliate_address = 'vi' then 'Vultisig'

WHEN affiliate_address = 'cakewallet' then 'Cake Wallet'

WHEN affiliate_address = 'lends' then 'Lends'

WHEN affiliate_address IS NULL THEN 'No Affiliate'

END AS Affiliates,

COUNT(DISTINCT tx_id) AS swaps,

COUNT(DISTINCT from_address) AS users,

SUM(

CASE

WHEN from_amount_usd IS NOT NULL THEN from_amount_usd

ELSE to_amount_usd

END

) AS swap_volume

FROM thorchain.defi.fact_swaps

WHERE

block_timestamp :: date >= '{{Start_Date}}'

AND block_timestamp :: date <= '{{End_Date}}'

GROUP BY 1, 2