SELECT block_id, --fact_refund_events.*

fact_refund_events.block_timestamp,

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

REPLACE(SPLIT(asset, '-')[0], '/', '.') AS L1_asset

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 asset LIKE '%.%' --L1s only in this query.

QUALIFY (asset_amount = MAX(asset_amount) OVER(PARTITION BY L1_asset))

AND (block_id = MAX(block_id) OVER(PARTITION BY L1_asset, asset_amount))

ORDER BY block_id DESC