synth_transfers AS (

SELECT DISTINCT from_address, to_address

FROM thorchain.core.fact_transfer_events

WHERE (from_address NOT IN ('thor1g98cy3n9mmjrpn0sxmn63lztelera37n8n67c0', 'thor1v8ppstuf6e3x0r4glqc68d5jqcs2tf38cg2q6y'))

AND (to_address NOT IN ('thor1g98cy3n9mmjrpn0sxmn63lztelera37n8n67c0', 'thor1v8ppstuf6e3x0r4glqc68d5jqcs2tf38cg2q6y'))

AND (asset = 'BTC/BTC')

),

extracted_rune AS (

--Note that this is an estimate, not taking into account network fees or missing identical rows in Flipside tables.

SELECT DATE(fact_swaps_events.block_timestamp) AS date,

SPLIT(pool_name, '-')[0] AS pool,

(CASE WHEN to_asset = 'THOR.RUNE' THEN from_address

WHEN SPLIT(memo, ':')[2] = '' THEN from_address

ELSE SPLIT(memo, ':')[2] END) AS address,

POWER(10,-8) * SUM(IFF(to_asset = 'THOR.RUNE', to_e8, 0) - IFF(from_asset = 'THOR.RUNE', from_e8, 0))

AS extracted_rune_from_synths,

POWER(10,-8) * SUM(IFF(from_asset = 'THOR.RUNE', to_e8, 0) - IFF(to_asset = 'THOR.RUNE', from_e8, 0))

AS net_minted_synth

FROM (thorchain.core.fact_swaps_events INNER JOIN thorchain.core.dim_block

ON fact_swaps_events.dim_block_id = dim_block.dim_block_id)

WHERE ((from_asset LIKE '%/%') OR (to_asset LIKE '%/%'))

-- AND (pool IN ('BTC.BTC', 'ETH.ETH', 'ETH.USDC', 'BNB.BUSD'))

AND (block_id BETWEEN 4610718 and 8195055)

AND (pool = 'BTC.BTC')

AND ((address IN (SELECT from_address FROM synth_transfers))

OR (address IN (SELECT to_address FROM synth_transfers)))

GROUP BY pool, date, address

),

cumulative_extracted_rune AS (

SELECT DISTINCT date, pool, address,

SUM(extracted_rune_from_synths) OVER(PARTITION BY pool, address ORDER BY date ASC) AS cumulative_extracted_rune_from_synths,

SUM(net_minted_synth) OVER(PARTITION BY pool, address ORDER BY date ASC) AS cumulative_net_minted_synth,