Sql interview question

Transaction_tbl table has four columns CusrId,TranID,TranAmt and Trandate.

User has to display all these fileds alng with Maxiumn TranAmt for each CustID and ratio of TranAmt

and Maximum TanAmt for each Transaction.

o/p:

1001 20001 10000 80000 0.1250 2020-04-25

1001 20002 15000 80000 0.1875 2020-04-25

1001 20003 80000 80000 1.0000 2020-04-25

1001 20004 20000 80000 0.2500 2020-04-25

1002 30001 7000 22000 0.3182 2020-04-25

1002 30002 15000 22000 0.6818 2020-04-25

1002 30003 22000 22000 1.0000 2020-04-25

Create table Transaction_tbl

(

 CustID int,

 TranID int,

 TranAmt int,

 TranDate Date

 );

Insert into Transaction_tbl values (1001,20001,10000,20200425);

Insert into Transaction_tbl values (1001,20002,15000,20200425);

Insert into Transaction_tbl values (1001,20003,80000,20200425);

Insert into Transaction_tbl values (1001,20004,20000,20200425);

Insert into Transaction_tbl values (1002,30001,7000,20200425);

Insert into Transaction_tbl values (1002,30002,15000,20200425);

Insert into Transaction_tbl values (1002,30003,22000,20200425);

select * from Transaction_tbl;

1001 20001 10000 2020-04-25

1001 20002 15000 2020-04-25

1001 20003 80000 2020-04-25

1001 20004 20000 2020-04-25

1002 30001 7000 2020-04-25

1002 30002 15000 2020-04-25

1002 30003 22000 2020-04-25

Inner querywill be excuted first

select CustID,max(TranAmt) as maxAmt from Transaction_tbl group by custId;

1001 80000

1002 22000

select A.custid,A.tranid,A.TranAmt,B.maxAmt,A.TranAmt/B.MaxAmt as ratio,trandate from Transaction_tbl A

join 

(select CustID,max(TranAmt) as maxAmt from Transaction_tbl group by custId)B

on A.custid = B.custid ;

1001 20001 10000 80000 0.1250 2020-04-25

1001 20002 15000 80000 0.1875 2020-04-25

1001 20003 80000 80000 1.0000 2020-04-25

1001 20004 20000 80000 0.2500 2020-04-25

1002 30001 7000 22000 0.3182 2020-04-25

1002 30002 15000 22000 0.6818 2020-04-25

1002 30003 22000 22000 1.0000 2020-04-25

With cte (custid,tranid,tranamt) As

(

select custid,tranid,tranamt from Transaction_tbl

),

max_cte (custid,maxamt) AS

(

select custid, max(tranamt) as maxamt from Transaction_tbl group by custid 

)

select A.custid,A.tranid,A.tranamt,B.maxamt,(A.tranamt/B.maxamt) AS ratio from cte A

join max_cte B

on 

A.custid = B.custid;

1001 20001 10000 80000 0.1250

1001 20002 15000 80000 0.1875

1001 20003 80000 80000 1.0000

1001 20004 20000 80000 0.2500

1002 30001 7000 22000 0.3182

1002 30002 15000 22000 0.6818

1002 30003 22000 22000 1.0000