trader,

-- from_symbol, to_symbol,

COUNT(*) as swaps,

(SUM(to_value) - SUM(from_value)) AS total_profit,

(SUM(to_value) - SUM(from_value)) / SUM(from_value) AS normalized_total_profit,

(SUM(to_value) - SUM(from_value)) / COUNT(*) AS average_profit_per_swap

-- SUM(from_value) / SUM(from_amount) AS avg_from_price,

-- SUM(to_value) / SUM(to_amount) AS avg_to_price

(

SELECT

trader,

block_timestamp,

P1.symbol AS from_symbol,

from_currency,

(from_amount / from_decimal) as _from_amount,

P1.price as from_price,

P2.symbol AS to_symbol,

to_currency,

(to_amount / to_decimal) as _to_amount,

P2.price AS to_price,

((P1.price * from_amount) / from_decimal) AS from_value,

((P2.price * to_amount) / to_decimal) AS to_value,

((P2.price * to_amount) / to_decimal) - ((P1.price * from_amount) / from_decimal) AS swap_price,

LAG(swap_price) OVER (

PARTITION BY

trader,

from_currency,

to_currency

ORDER BY

block_timestamp

) as previous_swap_price

FROM

osmosis.core.fact_swaps S

JOIN osmosis.core.ez_prices P1 ON (