-- forked from Total Volume USD @ https://flipsidecrypto.xyz/edit/queries/c6fbd44e-cc31-4f19-bcc9-d2fdd555a9b3

WITH

tx_ids AS (

SELECT TX_ID,

COUNT(1) AS TX_COUNT,

COUNT(DISTINCT POOL_NAME) AS POOL_COUNT

FROM thorchain.defi.fact_swaps_events

WHERE BLOCK_TIMESTAMP >= '{{start_date}}'::DATE

GROUP BY 1

, refunds AS (

SELECT TX_ID, COUNT(1) AS TX_COUNT

FROM thorchain.defi.fact_refund_events

WHERE BLOCK_TIMESTAMP >= '{{start_date}}'::DATE

GROUP BY 1

, base AS (

SELECT DISTINCT

se.TX_ID,

FIRST_VALUE(se.BLOCK_TIMESTAMP) OVER (PARTITION BY se.TX_ID ORDER BY s.FROM_AMOUNT_USD DESC) AS FROM_TIMESTAMP,

FIRST_VALUE(se.FROM_ASSET) OVER (PARTITION BY se.TX_ID ORDER BY s.FROM_AMOUNT_USD DESC) AS FROM_ASSET,

SPLIT_PART(se.MEMO, ':', 2) AS TO_ASSET,

FIRST_VALUE(s.FROM_AMOUNT_USD) OVER (PARTITION BY se.TX_ID ORDER BY s.FROM_AMOUNT_USD DESC) AS TOTAL_VOLUME_USD,

COALESCE(1.0 * s.AFFILIATE_FEE_BASIS_POINTS / 1e4 * TOTAL_VOLUME_USD, 0) AS AFFILIATE_FEE_USD

FROM thorchain.defi.fact_swaps_events AS se

JOIN thorchain.defi.fact_swaps AS s

ON se.TX_ID = s.TX_ID

AND se.FROM_ASSET = s.FROM_ASSET

AND se.TO_ASSET = s.TO_ASSET

JOIN tx_ids AS t

ON se.TX_ID = t.TX_ID

WHERE se.TX_ID NOT IN (SELECT TX_ID FROM refunds)

AND se.FROM_ASSET <> SPLIT_PART(se.MEMO, ':', 2)