OPTUM LeetCode Medium 1555 “Bank Account Summary" Interview SQL Question Explanation | EDS

[@reachrishav]

Excellent way of explaining this tricky question! Thanks for uploading :)

[@shravank1147]

thankyou

[@gurnoorsingh1875]

Thank you Sir

[@andreanlobo7373]

I used a UNION and then a CTE. I'll share my solution below
WITH CTE AS (
SELECT
user_id,
SUM(amount) as balance
FROM
(SELECT
paid_by as user_id,
- (amount) as amount
FROM transactions
UNION ALL
SELECT
paid_to as user_id,
amount as amount
FROM transactions) a
GROUP BY 1)
select
a.user_id,
a.user_name,
COALESCE((a.credit + b.balance),a.credit) as credit,
CASE WHEN COALESCE((a.credit + b.balance),a.credit) < 0 THEN 'Yes'
ELSE 'No' END as credit_limit_breached
from users a
left join CTE b
on a.user_id = b.user_id
order by user_id

[@nabinagoswami1261]

With CTE AS(
Select user_id,username,(T1+T2) as credit from(
Select A.user_id,A.username,A.Total_Amount as T1,B.Total_Amount as T2 from(
Select user_id,username,(credit-Amount) as Total_Amount from(
Select U.user_id,U.username,U.credit,IFNULL(T.amount,0) as Amount
from users U
Left Join transaction T
ON U.user_id=T.paid_by
) as PDF ) as A
Left Join
(Select user_id,username,Amount as Total_Amount from(
Select U.user_id,U.username,U.credit,IFNULL(T.amount,0) as Amount
from users U
Left Join transaction T
ON U.user_id=T.paid_to
) as XYZ )B
ON A.user_id=B.user_id) as ABC)
Select user_id,username,credit, Case
When Credit