(select *, case when direction = 'IN' and amount_usd< pow(10,7) then amount_usd when direction = 'OUT' and amount_usd< pow(10,7) then amount_usd*(-1) else 0 end as liquidity from ethereum.dex_swaps

where platform = 'sushiswap')

GROUP by pool_address )

inner join ethereum.dex_liquidity_pools

on pool_address = pa ),

token0details as

(select * from

(select symbol as symbol0, token_address from ethereum.token_prices_hourly

group by symbol, token_address)

inner join pool_details

on token_address = token0),

final_pool_details as (

select symbol0,symbol1,pa,net_liquidity from

(select symbol as symbol1, token_address as tok1 from ethereum.token_prices_hourly

group by symbol, token_address)

inner join token0details

on token1 = tok1 ),

sushi_rewards as (SELECT * from ethereum.udm_events

where origin_function_signature='0x18fccc76' and from_address = '0xef0881ec094552b2e128cf945ef17a6752b4ec5d'),

abc as (select * from (select s, sum(amount_usd) as amt_sushi, sum(amount_token) as amt_token, min(block_timestamp) as first_reward from (select * from

(SELECT tx_id as txid, amount_usd as amount_token, symbol as s from ethereum.udm_events

where origin_function_signature='0x18fccc76' and symbol != 'SUSHI')

inner join sushi_rewards

on tx_id = txid )

group by s )

inner join final_pool_details

on s =symbol0 or s = symbol1 )

select CONCAT_WS('-',symbol0,symbol1) as pool_name ,amt_sushi as sushi_distributed,(net_liquidity/total_Liquidity) *amt_sushi as total_sushi_rewards from

(select * from

(select s as sy , sum(net_liquidity) as total_liquidity from abc

where net_liquidity> '0'

group by s )

inner join abc

on s = sy )

where net_liquidity > '0' and amt_token is not NULL