SELECT

TX_ID,

SPLIT_PART(MEMO, ':', 4) LIKE '%/%/%' AS IS_STREAMING,

COUNT(1) AS TX_COUNT

FROM

thorchain.defi.fact_swaps_events

WHERE

UPPER(SPLIT_PART(MEMO, ':', 1)) IN ('SWAP', 'S', '=')

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

AND BLOCK_TIMESTAMP < '{{end_date}}' :: DATE + INTERVAL '1 DAY'

AND SPLIT_PART(MEMO, ':', 5) IN ('t')

GROUP BY

1,

2

),

base AS (

SELECT

t.TX_ID,

se.BLOCK_TIMESTAMP :: DATE AS date,

IS_STREAMING,

LIQ_FEE_RUNE_USD AS LIQUIDITY_FEES_USD

FROM

tx_ids AS t

JOIN thorchain.defi.fact_swaps AS se ON se.TX_ID = t.TX_ID

AND se.BLOCK_TIMESTAMP >= '{{start_date}}' :: DATE

AND se.BLOCK_TIMESTAMP < '{{end_date}}' :: DATE + INTERVAL '1 DAY'

DATE,

SUM(LIQUIDITY_FEES_USD) AS TOTAL_LIQUIDITY_FEES_USD,

SUM(

CASE

WHEN IS_STREAMING THEN LIQUIDITY_FEES_USD

ELSE 0

END