KPMG SQL Interview Question - Using ROW_NUMMBER( ) & CASE Statement

[@dugginenichandrababu9615]

Thanks for Knowledge share
select c.transaction_id,c.type, c.transaction_date ,
sum((case c.type
when 'deposit' then c.amount*1 else amount*-1 end)) over (order by c.transaction_date rows unbounded preceding ) running_sum
from trasactions c;

[@rishabhralli9151]

with cte as(
select *,
case
when type='deposit' then 1*amount else -1*amount end as new_amount
from transactions_1308)
select transaction_id,type,amount,transaction_date,
sum(new_amount) over(order by transaction_date rows between unbounded preceding and current row) as running_sum
from cte;

[@bankimdas9517]

with cte as (
select * , ROW_NUMBER() OVER(ORDER BY transaction_date) AS rn ,
(CASE
when type = 'deposit' then amount else -amount
END) AS trans
from transactions_1308
)
select transaction_id , type , amount , transaction_date ,
SUM(trans) over(order by rn asc) AS balance_amount
from cte;

[@srinivasulum414]

with cte as (
select *,row_number()over(order by transaction_date)as rno from transactions_1308
)
,cte1 as (
select *,case when type = 'deposit' then amount when type = 'withdrawal' then -1*amount end as result from cte
)
select transaction_id,type,amount,transaction_date,sum(result)over(order by rno rows between unbounded preceding and current row)as running_total from cte1

[@ashwingupta4765]

with cte as (
select transaction_id, type, transaction_date, amount,
case when type = 'deposit' then amount else -amount end as amounts
from transactions_1308)
Select *,
sum(amounts) over(order by transaction_date , amounts desc) as transaction_running
from cte

[@Mirchi_9949]

Interesting question. Thanks for sharing

[@dasubabuch1596]

with t as
(
select transaction_id,type1,case when type1 = 'deposit' then 1 else -1 end as tr, amount, transaction_date
from transactions_1308
),t1 as
(
select transaction_id,type1,amount,tr*amount as amount1, transaction_date from t
),
t2 as
(
select transaction_id,type1,amount,transaction_date,
sum(amount1)over(order by transaction_date rows between unbounded preceding and current row) as running_total
from t1
)
select * from t2;

[@VenkateshMarupaka-gn3rp]

Maybe below solution works.
WITH CTE AS (SELECT transaction_id, type, CASE WHEN type = 'deposit' THEN amount ELSE -amount END AS amount, transaction_date
FROM transactions_1308 )
SELECT *, SUM(amount) OVER(ORDER BY transaction_date, amount desc) AS running_total
FROM CTE

[@mohdnasim2575]

Thanks Sir,
Requesting you please make videos on how to use tabs in Mysql and some short cuts this is also important.

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@Ujjwalmishra-t1w]

Nice

[@hairavyadav6579]

Hi Sir ,
Please let me know this will work or not
with cte as(select *, case when type="deposit" then amount else -(amount) end as total, row_number() over(partition by transaction_date order by (select null)) rn
from trans)
select transaction_id,type,amount,transaction_date,sum(total) over(order by transaction_date ,rn) as running_total from cte;

[@theinsightminer08]

WITH CTE AS
(
SELECT
*,
CASE WHEN type = 'deposit' THEN amount ELSE -amount END AS net_amt
FROM transactions_1308
)
SELECT
transaction_id,
type,
amount,
transaction_date,
SUM(net_amt) OVER(ORDER BY transaction_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM CTE;

[@irshad9341]

Please correct if my answer is wrong
Select transection_id ,
Sum(case when type = deposite ) then amount when type = withdrawal then -amount end ) over (partition by transaction_id ,transaction_date order by transaction date) as cumulative_sum from table
Group by transection_id

[@CloudChallengers]

@irshad9341, Not correct. Please work on your query.

[@musicallywandering4617]

WITH CTE AS
(
SELECT *,
(CASE WHEN TYPE='DEPOSIT' THEN 1 ELSE -1 END)*AMOUNT AS NEW_AMOUNT
FROM TRANSACTIONS_1308
)
SELECT CTE.TRANSACTION_ID, CTE.TYPE, CTE.TRANSACTION_DATE, CTE.AMOUNT,
SUM(NEW_AMOUNT) OVER (ORDER BY TRANSACTION_DATE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL
FROM CTE;

[@manishsathe8371]

With CTE
As
(
Select *,
case when Transaction_Type='deposit' then Transaction_Amount*1
when Transaction_Type='withdrwal' then Transaction_Amount*-1
end
as Amount_For_RT
from [Transaction Details])
Select * , SUM(CTE.Amount_For_RT)Over(Order by (Select Null) rows between unbounded preceding and current row) as RT from CTE

[@ajay_sahoo0]

SELECT
*,
SUM(CASE
WHEN type = 'deposit' THEN +amount
WHEN type = 'withdrawal' THEN -amount
END) OVER(ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions_1308;