SELECT

date_trunc('day', block_timestamp) as date,

count(distinct tx_id) as tranId,

borrower as UserAdd,

borrows_contract_symbol AS underlying_symbol,

tx_id,

sum(loan_amount) AS token_amount,

sum(loan_amount_usd) AS amount_in_usd,

sum(sum(loan_amount_usd)) OVER (PARTITION BY borrower ORDER BY borrower ) as Total,

'Borrow' as UserAction

FROM compound.borrows

WHERE block_timestamp >= CURRENT_DATE -30

GROUP BY date, UserAdd,

borrows_contract_symbol, UserAction,tx_id

order by Total desc

,

cte_lend as (

SELECT

date_trunc('day', block_timestamp) as date,

count(distinct tx_id) as tranId,

supplier as UserAdd ,

supplied_symbol AS underlying_symbol,

tx_id,

sum(supplied_base_asset) AS token_amount,

sum(supplied_base_asset_usd) AS amount_in_usd,

sum(sum(supplied_base_asset_usd)) OVER (PARTITION BY supplier ORDER BY supplier ) as Total,

'Supply' AS UserAction

FROM compound.deposits

WHERE block_timestamp >= CURRENT_DATE -30

GROUP BY date, UserAdd,

underlying_symbol, UserAction, tx_id

order by Total desc

select * from cte_lend

union all

select * from cte_borrow