SELECT

a.BLOCK_TIMESTAMP as BLOCK_TIMESTAMP, 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,

sum(a.AMOUNT_IN) as AMOUNT_IN, sum(a.AMOUNT_IN_USD) as AMOUNT_IN_USD, sum(a.AMOUNT_OUT) as AMOUNT_OUT, sum(a.AMOUNT_OUT_USD) as AMOUNT_OUT_USD, a.SYMBOL_IN as SYMBOL_IN, a.SYMBOL_OUT as SYMBOL_OUT, SUM(b.GAS_PRICE) as GAS_PRICE, sum(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'

GROUP BY a.BLOCK_TIMESTAMP, a.BLOCK_NUMBER, b.POSITION, a.TX_HASH, a.PLATFORM, a.ORIGIN_FROM_ADDRESS, a.ORIGIN_TO_ADDRESS,a.CONTRACT_ADDRESS, a.SYMBOL_IN, a.SYMBOL_OUT),

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, CONTRACT_ADDRESS, TX_HASH, min(POSITION) as min_position, max(POSITION) as max_position

FROM df_potential_miners

GROUP BY BLOCK_NUMBER, CONTRACT_ADDRESS, TX_HASH),

query_at as (

SELECT

df_potential_miners.BLOCK_NUMBER, df_potential_miners.PLATFORM, df_potential_miners.CONTRACT_ADDRESS, df_potential_miners.ORIGIN_TO_ADDRESS,

df_potential_miners.ORIGIN_FROM_ADDRESS, df_potential_miners.TX_HASH, min_position, max_position,

CASE WHEN POSITION = min_position then amount_in end as COST,

CASE WHEN POSITION = max_position then amount_out end as REVENUE,

CASE WHEN POSITION = min_position then SYMBOL_IN end as SYMBOL_COST,

CASE WHEN POSITION = max_position then SYMBOL_OUT end as SYMBOL_REVENUE