-- forked from 9ed08ca7-4771-417d-b3bb-2ddbceb2b1eb with VaultTokensPolygon (token0, token1, block_number, block_timestamp) as ( select concat('0x', substr(tx.input_data, 163, 40)) as token0, concat('0x', substr(tx.input_data, 227, 40)) as token1, tx.block_number, tx.block_timestamp from polygon.core.fact_traces tr join polygon.core.fact_transactions tx on ( tr.block_timestamp >= '2022-11-01 00:00' and tx.block_number = tr.block_number and tx.block_timestamp = tr.block_timestamp and tx.tx_hash = tr.tx_hash and tx.status = 'SUCCESS' and ( tr.type = 'CREATE2' or tr.type = 'CREATE' ) and tr.tx_status = 'SUCCESS' and tx.block_timestamp >= '2022-11-01 00:00' and tr.to_address = lower('{{vault_address}}') ) ), VaultTokensOptimism (token0, token1, block_number, block_timestamp) as ( select concat('0x', substr(tx.input_data, 163, 40)) as token0, concat('0x', substr(tx.input_data, 227, 40)) as token1, tx.block_number, tx.block_timestamp from optimism.core.fact_traces tr join optimism.core.fact_transactions tx on ( tr.block_timestamp >= '2022-11-01 00:00' and tx.block_number = tr.block_number and tx.block_timestamp = tr.block_timestamp and tx.tx_hash = tr.tx_hash and tx.status = 'SUCCESS' and ( tr.type = 'CREATE2' or tr.type = 'CREATE' ) and tr.tx_status = 'SUCCESS' and tx.block_timestamp >= '2022-11-01 00:00' and tr.to_address = lower('{{vault_address}}') ) ), VaultTokens (token0, token1, block_number, block_timestamp) as ( select token0, token1, block_number, block_timestamp from VaultTokensPolygon union select token0, token1, block_number, block_timestamp from VaultTokensOptimism ), Token0Polygon (address, symbol, decimals) as ( select token0 as address, symbol, decimals from polygon.core.dim_contracts join VaultTokens on (address = token0) ), Token0Optimism (address, symbol, decimals) as ( select token0 as address, symbol, decimals from optimism.core.dim_contracts join VaultTokens on (address = token0) ), Token0 (address, symbol, decimals) as ( select address, symbol, decimals from Token0Polygon union select address, symbol, decimals from Token0Optimism ), Token1Polygon (address, symbol, decimals) as ( select token1 as address, symbol, decimals from optimism.core.dim_contracts join VaultTokens on (address = token1) ), Token1Optimism (address, symbol, decimals) as ( select token1 as address, symbol, decimals from polygon.core.dim_contracts join VaultTokens on (address = token1) ), Token1 (address, symbol, decimals) as ( select address, symbol, decimals from Token1Polygon union select address, symbol, decimals from Token1Optimism ), Scale (decimals0, decimals1) as ( select t0.decimals, t1.decimals from token0 t0 cross join token1 t1 ), Symbols (token0, token1) as ( select t0.symbol, t1.symbol from token0 t0 cross join token1 t1 ), MintsPolygon ( time, tickLower, tickUpper, amount, contract_address ) as ( select date_trunc('minute', e.block_timestamp), event_inputs:tickLower::int, event_inputs:tickUpper::int, event_inputs:amount::bigint, contract_address from polygon.core.fact_event_logs e cross join VaultTokens v where event_name = 'Mint' and event_inputs:owner::string = lower('{{vault_address}}') and e.block_number >= v.block_number and e.block_timestamp >= v.block_timestamp and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), MintsOptimism ( time, tickLower, tickUpper, amount, contract_address ) as ( select date_trunc('minute', e.block_timestamp), event_inputs:tickLower::int, event_inputs:tickUpper::int, event_inputs:amount::bigint, contract_address from optimism.core.fact_event_logs e cross join VaultTokens v where event_name = 'Mint' and event_inputs:owner::string = lower('{{vault_address}}') and e.block_number >= v.block_number and e.block_timestamp >= v.block_timestamp and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), Mints ( time, tickLower, tickUpper, amount, contract_address ) as ( select time, tickLower, tickUpper, amount, contract_address from MintsPolygon union select time, tickLower, tickUpper, amount, contract_address from MintsOptimism ), BurnsPolygon ( time, tickLower, tickUpper, amount, contract_address ) as ( select date_trunc('minute', e.block_timestamp), event_inputs:tickLower::int, event_inputs:tickUpper::int, - event_inputs:amount::bigint, contract_address from polygon.core.fact_event_logs e cross join VaultTokens v where event_name = 'Burn' and event_inputs:owner::string = lower('{{vault_address}}') and e.block_number >= v.block_number and e.block_timestamp >= v.block_timestamp and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), BurnsOptimism ( time, tickLower, tickUpper, amount, contract_address ) as ( select date_trunc('minute', e.block_timestamp), event_inputs:tickLower::int, event_inputs:tickUpper::int, - event_inputs:amount::bigint, contract_address from optimism.core.fact_event_logs e cross join VaultTokens v where event_name = 'Burn' and event_inputs:owner::string = lower('{{vault_address}}') and e.block_number >= v.block_number and e.block_timestamp >= v.block_timestamp and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), Burns ( time, tickLower, tickUpper, amount, contract_address ) as ( select time, tickLower, tickUpper, amount, contract_address from BurnsPolygon UNION select time, tickLower, tickUpper, amount, contract_address from BurnsOptimism ), LiquidityChanges (tickLower, tickUpper, amount, contract_address) as ( select tickLower, tickUpper, amount, contract_address from Mints union all select tickLower, tickUpper, amount, contract_address from Burns ), Liquidity (tickLower, tickUpper, liquidity, contract_address) as ( select tickLower, tickUpper, sum(amount), contract_address from LiquidityChanges group by tickLower, tickUpper, contract_address having sum(amount) > 0 ), Pool (address) as ( select distinct contract_address from LiquidityChanges ), Price (contract_address, value, block_timestamp) as ( select contract_address, price, block_timestamp from ( select contract_address, power(1.0001, event_inputs:tick::int) price, block_timestamp from polygon.core.fact_event_logs e join Pool p on ( e.contract_address = p.address and event_name = 'Swap' and e.block_timestamp >= least(CURRENT_DATE,'{{end_time}}'::timestamp) - interval '15 day' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ) qualify row_number() over ( partition by contract_address order by block_number desc, event_index desc ) = 1 union all select contract_address, power(1.0001, event_inputs:tick::int) price, block_timestamp from polygon.core.fact_event_logs e join Pool p on ( e.contract_address = p.address and event_name = 'Initialize' and e.block_timestamp >= '2022-11-01 00:00' and e.tx_status = 'SUCCESS' and e.event_removed = false ) union all select contract_address, power(1.0001, event_inputs:tick::int) price, block_timestamp from optimism.core.fact_event_logs e join Pool p on ( e.contract_address = p.address and event_name = 'Swap' and e.block_timestamp >= least(CURRENT_DATE,'{{end_time}}'::timestamp) - interval '15 day' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ) qualify row_number() over ( partition by contract_address order by block_number desc, event_index desc ) = 1 union all select contract_address, power(1.0001, event_inputs:tick::int) price, block_timestamp from optimism.core.fact_event_logs e join Pool p on ( e.contract_address = p.address and event_name = 'Initialize' and e.block_timestamp >= '2022-11-01 00:00' and e.tx_status = 'SUCCESS' and e.event_removed = false ) ) a qualify row_number() over ( partition by contract_address order by block_timestamp desc ) = 1 ), Ranges ( tickLower, tickUpper, Pa, Pb, liquidity, decimals0, decimals1, contract_address ) as ( select tickLower, tickUpper, power(1.0001, tickLower) as Pa, power(1.0001, tickUpper) as Pb, liquidity, decimals0, decimals1, contract_address from Liquidity cross join Scale ), Token0XfersPolygon(time, symbol, value) as ( select block_timestamp, symbol, -event_inputs:value::bigint / power(10, decimals) from polygon.core.fact_event_logs e join Token0 on (address = contract_address) where event_name = 'Transfer' and event_inputs:from::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false union all select block_timestamp, symbol, event_inputs:value::bigint / power(10, decimals) from polygon.core.fact_event_logs e join Token0 on (address = contract_address) where event_name = 'Transfer' and event_inputs:to::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), Token0XfersOptimism(time, symbol, value) as ( select block_timestamp, symbol, -event_inputs:value::bigint / power(10, decimals) from optimism.core.fact_event_logs e join Token0 on (address = contract_address) where event_name = 'Transfer' and event_inputs:from::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false union all select block_timestamp, symbol, event_inputs:value::bigint / power(10, decimals) from optimism.core.fact_event_logs e join Token0 on (address = contract_address) where event_name = 'Transfer' and event_inputs:to::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), Token0Xfers(time, symbol, value) as ( select time, symbol, value from Token0XfersPolygon union select time, symbol, value from Token0XfersOptimism ), Token1XfersPolygon(time, symbol, value) as ( select block_timestamp, symbol, -event_inputs:value::bigint / power(10, decimals) from polygon.core.fact_event_logs e join Token1 on (address = contract_address) where event_name = 'Transfer' and event_inputs:from::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false union all select block_timestamp, symbol, event_inputs:value::bigint / power(10, decimals) from polygon.core.fact_event_logs e join Token1 on (address = contract_address) where event_name = 'Transfer' and event_inputs:to::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), Token1XfersOptimism(time, symbol, value) as ( select block_timestamp, symbol, -event_inputs:value::bigint / power(10, decimals) from optimism.core.fact_event_logs e join Token1 on (address = contract_address) where event_name = 'Transfer' and event_inputs:from::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false union all select block_timestamp, symbol, event_inputs:value::bigint / power(10, decimals) from optimism.core.fact_event_logs e join Token1 on (address = contract_address) where event_name = 'Transfer' and event_inputs:to::string = lower('{{vault_address}}') and e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ), Token1Xfers(time, symbol, value) as ( select time, symbol, value from Token1XfersPolygon union select time, symbol, value from Token1XfersOptimism ), Merged0 (total) as ( select sum(value) as total from Token0Xfers union all select sum( case when value < Pa then liquidity * (sqrt(Pb) - sqrt(Pa)) / sqrt(Pb) / sqrt(Pa) when value > Pb then 0 else liquidity * (sqrt(Pb) - sqrt(value)) / sqrt(Pb) / sqrt(value) end / power(10, decimals0) ) as total from Ranges natural join Price ), Merged1 (total) as ( select sum(value) as total from Token1Xfers union all select sum( case when value < Pa then 0 when value > Pb then liquidity * (sqrt(Pb) - sqrt(Pa)) else liquidity * (sqrt(value) - sqrt(Pa)) end / power(10, decimals1) ) as total from Ranges natural join Price ), TotalToken0 (total) as ( select sum(total) from Merged0 ), TotalToken1 (total) as ( select sum(total) from Merged1 ), MostRecentPoolPrice (value) as ( select value from Price order by block_timestamp desc limit 1 ), ProviderTokenIds (provider, id, address) as ( select provider, id, address from crosschain.core.dim_asset_metadata join Token0 on ( address = token_address and (platform = 'polygon-pos' or platform = 'optimistic-ethereum') ) union all select provider, id, address from crosschain.core.dim_asset_metadata join Token1 on ( address = token_address and (platform = 'polygon-pos' or platform = 'optimistic-ethereum') ) ), MostRecentUSDPrice (address, hour, price, kind, poolPrice) as ( select address, recorded_hour, close, case when address = token0 then 'token0' else 'token1' end, value from ProviderTokenIds natural join crosschain.core.fact_hourly_prices cross join VaultTokens cross join MostRecentPoolPrice qualify row_number() over ( order by recorded_hour desc ) = 1 ), LiquidityChangesWithTime ( time, tickLower, tickUpper, liquidity, contract_address ) as ( select time, tickLower, tickUpper, amount, contract_address from Mints union all select time, tickLower, tickUpper, amount, contract_address from Burns ), MergedLiquidityWithTime ( time, tickLower, tickUpper, liquidity, contract_address ) as ( select time, tickLower, tickUpper, sum(liquidity), contract_address from LiquidityChangesWithTime group by time, tickLower, tickUpper, contract_address ), CummulativeLiquidity ( time, tickLower, tickUpper, liquidity, contract_address ) as ( select time, tickLower, tickUpper, sum(liquidity) over ( partition by tickLower, tickUpper, contract_address order by time ), contract_address from MergedLiquidityWithTime ), LiquidityWithTime ( time, Pa, Pb, liquidity, decimals0, decimals1, contract_address ) as ( select time, power(1.0001, tickLower) as Pa, power(1.0001, tickUpper) as Pb, liquidity, decimals0, decimals1, contract_address from CummulativeLiquidity cross join scale ), PoolFee (contract_address, fee) as ( select p.address, event_inputs:fee::int / 1000000.0 from polygon.core.fact_event_logs e join Pool p on ( event_inputs:pool::string = p.address and event_name = 'PoolCreated' and e.tx_status = 'SUCCESS' and e.event_removed = false ) union select p.address, event_inputs:fee::int / 1000000.0 from optimism.core.fact_event_logs e join Pool p on ( event_inputs:pool::string = p.address and event_name = 'PoolCreated' and e.tx_status = 'SUCCESS' and e.event_removed = false ) ), LPTimes (time, Pa, Pb, contract_address) as ( select distinct time, Pa, Pb, contract_address from LiquidityWithTime ), LPTimeRanges (time, next_time, contract_address, Pa, Pb) as ( select time, lead(time, 1) over ( partition by contract_address, Pa, Pb order by time ) next_time, contract_address, Pa, Pb from LPTimes ), LPTimeRangesWithEnd (time, next_time, contract_address, Pa, Pb) as ( select time, CASE when next_time is null then least(CURRENT_DATE,'{{end_time}}'::timestamp) else next_time end next_time, contract_address, Pa, Pb from LPTimeRanges ), BlockTimes (time) as ( select distinct date_trunc('minute', block_timestamp) from polygon.core.fact_blocks where block_timestamp >= ( select min(time) from LPTimes ) union select distinct date_trunc('minute', block_timestamp) from optimism.core.fact_blocks where block_timestamp >= ( select min(time) from LPTimes ) ), LPTimeRangesFilledIn (time, actual_time, contract_address, Pa, Pb) as ( select l.time, b.time, contract_address, Pa, Pb from LPTimeRangesWithEnd l join BlockTimes b on ( b.time >= l.time and b.time < l.next_time ) ), LPForwardFilled ( time, contract_address, Pa, Pb, liquidity, decimals0, decimals1 ) as ( select f.actual_time, l.contract_address, Pa, Pb, liquidity, decimals0, decimals1 from LPTimeRangesFilledIn f natural join LiquidityWithTime l ), SwapsPolygon (time, contract_address, prevSqrtPrice, sqrtPrice) as ( select date_trunc('minute', block_timestamp), contract_address, lag(event_inputs:sqrtPriceX96::double, 1) over ( partition by contract_address order by e.block_number, e.event_index ) / power(2, 96) prevSqrtPrice, event_inputs:sqrtPriceX96::double / power(2, 96) sqrtPrice from polygon.core.fact_event_logs e join Pool p on ( e.contract_address = p.address and e.block_timestamp >= '2022-11-01 00:00' and event_name = 'Swap' and e.block_timestamp > ( select min(time) - interval '1 day' from LPTimes ) and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ) ), SwapsOptimism (time, contract_address, prevSqrtPrice, sqrtPrice) as ( select date_trunc('minute', block_timestamp), contract_address, lag(event_inputs:sqrtPriceX96::double, 1) over ( partition by contract_address order by e.block_number, e.event_index ) / power(2, 96) prevSqrtPrice, event_inputs:sqrtPriceX96::double / power(2, 96) sqrtPrice from optimism.core.fact_event_logs e join Pool p on ( e.contract_address = p.address and e.block_timestamp >= '2022-11-01 00:00' and event_name = 'Swap' and e.block_timestamp > ( select min(time) - interval '1 day' from LPTimes ) and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false ) ), Swaps (time, contract_address, prevSqrtPrice, sqrtPrice) as ( select time, contract_address, prevSqrtPrice, sqrtPrice from SwapsPolygon union select time, contract_address, prevSqrtPrice, sqrtPrice from SwapsOptimism ), InRangeSwaps ( time, contract_address, prevSqrtPrice, sqrtPrice, sqrtPa, sqrtPb, liquidity ) as ( select time, contract_address, prevSqrtPrice, sqrtPrice, sqrt(Pa) sqrtPa, sqrt(Pb) sqrtPb, liquidity from LPForwardFilled natural join Swaps -- overlapping ranges where sqrt(Pa) <= case when sqrtPrice > prevSqrtPrice then sqrtPrice else prevSqrtPrice end and case when prevSqrtPrice < sqrtPrice then prevSqrtPrice else sqrtPrice end <= sqrt(Pb) ), PartialSwaps ( time, contract_address, prevSqrtPrice, sqrtPrice, liquidity ) as ( select time, contract_address, case when prevSqrtPrice > sqrtPb then sqrtPb when prevSqrtPrice < sqrtPa then sqrtPa else prevSqrtPrice end prevSqrtPrice, case when sqrtPrice > sqrtPb then sqrtPb when sqrtPrice < sqrtPa then sqrtPa else sqrtPrice end sqrtPrice, liquidity from InRangeSwaps ), LogSetManagerFeeBPSPolygon (block_number, block_time, event, vault, amount) as ( select block_number, block_timestamp, 'LogSetManagerFeeBPS' as event, contract_address vault, ethereum.public.udf_hex_to_int (data) from polygon.core.fact_event_logs e where e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false and contract_address = lower('{{vault_address}}') and topics[0] = '0xa73ff0774d4c8681f79fa6546f34bab7a3037f3b3aab405401a1fd8c6e5ca3d6' -- LogSetManagerFeeBPS(uint16)' order by block_number desc, event_index desc limit 1 ), LogSetManagerFeeBPSOptimism (block_number, block_time, event, vault, amount) as ( select block_number, block_timestamp, 'LogSetManagerFeeBPS' as event, contract_address vault, ethereum.public.udf_hex_to_int (data) from optimism.core.fact_event_logs e where e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false and contract_address = lower('{{vault_address}}') and topics[0] = '0xa73ff0774d4c8681f79fa6546f34bab7a3037f3b3aab405401a1fd8c6e5ca3d6' -- LogSetManagerFeeBPS(uint16)' order by block_number desc, event_index desc limit 1 ), LogSetManagerFeeBPS (block_number, block_time, event, vault, amount) as ( select block_number, block_time, event, vault, amount from LogSetManagerFeeBPSPolygon union select block_number, block_time, event, vault, amount from LogSetManagerFeeBPSOptimism ), Amount0Fees (fee0) as ( select sum( fee * liquidity * (1 / sqrtPrice - 1 / prevSqrtPrice) / power(10, decimals0) * CASE WHEN amount is null then 0 else amount END / 10000.0 ) from PartialSwaps cross join Scale natural join PoolFee full join LogSetManagerFeeBPS on true where sqrtPrice <= prevSqrtPrice ), Amount1Fees (fee1) as ( select sum( fee * liquidity * (sqrtPrice - prevSqrtPrice) / power(10, decimals1) * CASE WHEN amount is null then 0 else amount END / 10000 ) from PartialSwaps cross join Scale natural join PoolFee full join LogSetManagerFeeBPS on true where sqrtPrice > prevSqrtPrice ), LogWithdrawManagerBalancePolygon(manager0, manager1) as ( select sum(ethereum.public.udf_hex_to_int (SUBSTRING(data, 3, 64))), sum(ethereum.public.udf_hex_to_int (SUBSTRING(data, 67, 64))) from polygon.core.fact_event_logs e where e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false and contract_address = lower('{{vault_address}}') and topics[0] = '0xa292e28c648da34e20b372054caab5f0359198b3b4d5f0ef9945d4616e15dc97' -- LogWithdrawManagerBalance(uint256,uint256) ), LogWithdrawManagerBalanceOptimism(manager0, manager1) as ( select sum(ethereum.public.udf_hex_to_int (SUBSTRING(data, 3, 64))), sum(ethereum.public.udf_hex_to_int (SUBSTRING(data, 67, 64))) from optimism.core.fact_event_logs e where e.block_timestamp >= '2022-11-01 00:00' and e.block_timestamp < '{{end_time}}' and e.tx_status = 'SUCCESS' and e.event_removed = false and contract_address = lower('{{vault_address}}') and topics[0] = '0xa292e28c648da34e20b372054caab5f0359198b3b4d5f0ef9945d4616e15dc97' -- LogWithdrawManagerBalance(uint256,uint256) ), LogWithdrawManagerBalance(manager0, manager1) as ( select manager0, manager1 from LogWithdrawManagerBalancePolygon union select manager0, manager1 from LogWithdrawManagerBalanceOptimism ), Fees(fee0, fee1, feeUSD) as ( select fee0 - CASE WHEN manager0 is null then 0 else manager0 END, fee1 - CASE WHEN manager1 is null then 0 else manager1 END, round(CASE WHEN kind = 'token0' then (fee0 - CASE WHEN manager0 is null then 0 else manager0 END + (fee1 - CASE WHEN manager1 is null then 0 else manager1 END) / poolprice) * price ELSE ((fee0 - CASE WHEN manager0 is null then 0 else manager0 END) * poolPrice + fee1 - CASE WHEN manager1 is null then 0 else manager1 END) * price END, 2) as feeUSD from Amount0Fees cross join Amount1Fees cross join MostRecentUSDPrice cross join Symbols full join LogWithdrawManagerBalance on true ) select t0.symbol as token0, (tt0.total - fee0) as amount0, round(CASE WHEN kind = 'token0' then (tt0.total - fee0) * price ELSE (tt0.total - fee0) * poolprice * power(10, t0.decimals - t1.decimals) * price END, 2) as value0usd, t1.symbol as token1, (tt1.total- fee1) as amount1, round(CASE WHEN kind = 'token0' then (tt1.total- fee1) / poolprice * power(10, t1.decimals - t0.decimals) * price ELSE (tt1.total- fee1) * price END, 2) as value1usd, round((CASE WHEN kind = 'token0' then (tt0.total - fee0) * price ELSE (tt0.total - fee0) * poolprice * power(10, t0.decimals - t1.decimals) * price END + CASE WHEN kind = 'token0' then (tt1.total- fee1) / poolprice * power(10, t1.decimals - t0.decimals) * price ELSE (tt1.total- fee1) * price END), 2) as totalusd from TotalToken0 tt0 cross join TotalToken1 tt1 cross join Token0 t0 cross join Token1 t1 cross join MostRecentUSDPrice u cross join Fees;

0xsvenska