SELECT block_id, --fact_refund_events.*

fact_refund_events.block_timestamp,

from_address,

POWER(10,-8) * asset_e8 AS asset_amount,

TO_CHAR(SPLIT(asset, '-')[0]) AS asset,

UPPER(SPLIT(SPLIT(memo, ':')[1], '-')[0]) AS target_asset,

memo, reason

FROM thorchain.core.fact_refund_events INNER JOIN thorchain.core.dim_block ON fact_refund_events.dim_block_id = dim_block.dim_block_id

WHERE (

(reason LIKE '%insufficient funds for outbound request%')

AND

(reason NOT LIKE '%fail to refund%')

--Specify that the 'insufficient funds' relates to the target asset.

)

QUALIFY block_id = MAX(block_id) OVER(PARTITION BY target_asset)

ORDER BY block_id DESC