with no_pools as (

select

tx_id,

count(DISTINCT pool_name) as n_pools

from thorchain.defi.fact_swaps s

where tx_id not in (

select tx_id

from thorchain.defi.fact_refund_events

)

group by 1

)

,

affiliate_mapping as (

select

date_trunc('{{Time_Interval}}', block_timestamp) as date,

CASE

WHEN affiliate_address ilike any ('t','t/-', 'T','ts','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk','thor17suv0n437snv68axkx64whutkrvefv7pzq7xep') THEN 'Thorswap'

WHEN affiliate_address ilike any ('ti', 'te','tr', 'td') THEN 'Trust Wallet'

WHEN affiliate_address ilike any ('dx','thor1ap5vn4svwkpch2c9jm7hlpr2pj47e62xwpcvtw') THEN 'Asgardex'

WHEN affiliate_address ilike any ('wr','thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8') THEN 'Thorwallet'

WHEN affiliate_address ilike any ('va', 'vi','v0') THEN 'Vultisig'

WHEN affiliate_address ilike any ('zcx-com','0xbami','unizen-utxo') THEN 'Unizen'

WHEN affiliate_address ilike any ('lifi','lifi/-_') THEN 'Lifi'

WHEN affiliate_address ilike any ('okw','okw/-_') THEN 'OKX Wallet'

WHEN affiliate_address ilike any ('oky','OKY') THEN 'OneKey'

WHEN affiliate_address ilike any ('xdf','XDF') THEN 'XDEFI'

WHEN affiliate_address ilike 'ds' THEN 'Defispot'

WHEN affiliate_address ilike 'rg' THEN 'Rango'

WHEN affiliate_address ilike 'ss' THEN 'Shapeshift'

WHEN affiliate_address ilike 'lends' THEN 'Lends'

WHEN affiliate_address ilike 'bp' THEN 'BitPay'

WHEN affiliate_address ilike 'dcf' THEN 'Decentralfi'

WHEN affiliate_address ilike 'tl' THEN 'Thorswap Ledger'

WHEN affiliate_address ilike 'ej' THEN 'Edge Wallet'