SELECT

a.BLOCK_NUMBER as BLOCK_NUMBER, b.POSITION as POSITION, a.TX_HASH as TX_HASH, a.PLATFORM as PLATFORM, a.ORIGIN_FROM_ADDRESS as ORIGIN_FROM_ADDRESS, a.ORIGIN_TO_ADDRESS as ORIGIN_TO_ADDRESS,

a.CONTRACT_ADDRESS as CONTRACT_ADDRESS, a.AMOUNT_IN as AMOUNT_IN, a.AMOUNT_IN_USD as AMOUNT_IN_USD, a.AMOUNT_OUT as AMOUNT_OUT, a.AMOUNT_OUT_USD as AMOUNT_OUT_USD, a.SYMBOL_IN as SYMBOL_IN, a.SYMBOL_OUT as SYMBOL_OUT, b.GAS_PRICE as GAS_PRICE, b.GAS_USED as GAS_USED

from ethereum.core.ez_dex_swaps a join ethereum.core.fact_transactions b

on a.BLOCK_NUMBER = b.BLOCK_NUMBER and a.TX_HASH = b.TX_HASH

WHERE a.PLATFORM = 'uniswap-v2' or a.PLATFORM = 'uniswap-v3'),

df_1 as (

SELECT BLOCK_NUMBER, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS, count (DISTINCT TX_HASH) as tx_count

FROM df

GROUP BY BLOCK_NUMBER, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS

HAVING tx_count = 2

),

df_potential_miners as (

SELECT df.BLOCK_NUMBER, POSITION, TX_HASH, PLATFORM, ORIGIN_FROM_ADDRESS, df.ORIGIN_TO_ADDRESS, df.CONTRACT_ADDRESS, AMOUNT_IN, AMOUNT_IN_USD, AMOUNT_OUT, AMOUNT_OUT_USD, SYMBOL_IN, SYMBOL_OUT, GAS_PRICE, GAS_USED

FROM df INNER JOIN df_1 ON df.BLOCK_NUMBER = df_1.BLOCK_NUMBER and df.ORIGIN_TO_ADDRESS = df_1.ORIGIN_TO_ADDRESS and df.CONTRACT_ADDRESS = df_1.CONTRACT_ADDRESS

),

min_max as (

SELECT BLOCK_NUMBER, ORIGIN_TO_ADDRESS, CONTRACT_ADDRESS, min(POSITION) as min_position, max(POSITION) as max_position

FROM df_potential_miners

GROUP BY BLOCK_NUMBER, ORIGIN_to_ADDRESS, CONTRACT_ADDRESS),

potential_miners as (

SELECT

CASE

WHEN POSITION = min_max.min_position THEN 'FRONTRUN'

WHEN POSITION = min_max.max_position THEN 'BACKRUN'

END AS NAME_OF_POSITION,

df_potential_miners.BLOCK_NUMBER, POSITION,

TX_HASH, PLATFORM, ORIGIN_FROM_ADDRESS, df_potential_miners.ORIGIN_TO_ADDRESS, df_potential_miners.CONTRACT_ADDRESS, AMOUNT_IN, AMOUNT_OUT, AMOUNT_IN_USD, AMOUNT_OUT_USD, SYMBOL_IN, SYMBOL_OUT, GAS_PRICE, GAS_USED

FROM df_potential_miners JOIN min_max ON df_potential_miners.BLOCK_NUMBER = min_max.BLOCK_NUMBER and df_potential_miners.ORIGIN_TO_ADDRESS = min_max.ORIGIN_TO_ADDRESS and df_potential_miners.CONTRACT_ADDRESS = min_max.CONTRACT_ADDRESS),