Real time Problems/Challenges

@sookie-T06sFW • Oct 23, 2024

Oct 23, 2024

1.1K

Hi everyone,

Feel proud in calling yourself as a computer/IT engineer 😒 ? Simple, in this thread - a sort of challenge will be thrown for all such engineers. The challenge will be based on live situations that software engineers face in their everyday life and solutions of such problems is not mentioned or taught directly in any engineering books. But if you use your little bit of brains and concepts taught in engineering or in any training, they can be solved. So any one calling him/her as a CS/IT engineer would like to solve simple problems I am going to throw in this thread ?

Problem : You have following two tables and you are supposed to write a simplest SQL query that would give me data as shown in Table 3

Table 1 : REF_ASSET_LIABILITY with structure as following:

ASSET_LIABILITY_ID - Primary key -NUMBER(10,0)
ASEET_LIABILITY_NAME - VARCHAR2(20)

[IMG]

Table 2 : PORTFOLIO_HOLDING with structure as following:
ID - Primary key -NUMBER(10,0)
PORTFOLIO_ID - NUMBER(10,0)
PORTFOLIO_NAME - VARCHAR2(20)
CUSTOMER_ID - VARCHAR2(10) -FOREIGN KEY[SOME OTHER TABLE]
BUY_COST -NUMBER(23,5)
MARKET_VALUE -NUMBER(23,5)
ASSET_LIABILITY_ID - NUMBER(10,0) -FOREIGN KEY[REF_ASSET_LIABILITY]

[IMG]

Now I just need to get the data from these tables in the following form
Table 3: Outcome

[IMG]

PS: Everyone is free to throw REAL TIME challenges/problems in this thread.
Note: Not direct questions should be included that can be EASILY find in any books. 😀

[EDIT]
Also in above problem a check should be made where CUSTOMER_ID=C1. Though it is not necessary but it would be better if you people include it also in the query.

Like 0 Replies 16

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

CrazyEngineers powered by Jatra Community Platform

safwan

@safwan-NH7W5Y • Oct 2, 2009

Hi , sooki

I just want one question for above given question.

fro column allocated % assets wise the data is necessary to shown as given in colum .
0
sookie

@sookie-T06sFW • Oct 2, 2009

safwan
Hi , sooki

I just want one question for above given question.

fro column allocated % assets wise the data is necessary to shown as given in colum .
Good question, I was expecting it. Thanks for at least giving it a try. 😀
No, the column should show only the values, I have shown them only for calculation purposes. I assumed people would remove that part by default. So in last column , you need to show only the calculated values only as e.g 47.37, 52.63, 46.87, 53.13. Approximation is allowed but only upto 2 decimal places.

*Thinking is it really so tough to solve or are people here so DUMB to solve 😐 *
0
Ashraf HZ

@Ash • Oct 2, 2009

If you give them a bar of Cadbury chocolate, perhaps they will be interested to solve 😀
0
vik001ind

@vik001ind-rOaCSy • Oct 2, 2009

There is problem in your question -- customer_id should be varchar rather than number as it holds the value "C1"

my answer (assuming customer_id is of type varchar)

select portfolio_name,aseet_liability_name,market_value as 'allocated_value',round(((allocated_value/(100+allocated_value))*100),2) as 'allocated %-asset wise' from ref_asset_liability,portfolio_holding where customer_id = "C1" AND ref_asset_liabilty.aseet_liability_name = portfolio_holding.asset_liability_id

I haven't executed it, so i'm not sure whether its right or wrong!
0
Manish Goyal

@manish-r2Hoep • Oct 3, 2009

vik001ind
There is problem in your question -- customer_id should be varchar rather than number as it holds the value "C1"

my answer (assuming customer_id is of type varchar)

select portfolio_name,aseet_liability_name,market_value as 'allocated_value',round(((allocated_value/(100+allocated_value))*100),2) as 'allocated %-asset wise' from ref_asset_liability,portfolio_holding where customer_id = "C1" AND ref_asset_liabilty.aseet_liability_name = portfolio_holding.asset_liability_id

I haven't executed it, so i'm not sure whether its right or wrong!
helloo vi001ind your query IS Perfect but i think there should be small modification
ie it should be like this
select portfolio_name,aseet_liability_name,market_value as 'allocated_value',round(((market_value/(100+market_value))*100),2) as 'allocated %-asset wise' from ref_asset_liability,portfolio_holding where customer_id = "C1" AND ref_asset_liabilty.aseet_liability_name = portfolio_holding.asset_liability_id;
i think it should run hope so in MySql
0
vik001ind

@vik001ind-rOaCSy • Oct 3, 2009

That was a silly mistake. damn it!
hit me, someone plz !! !
0
sookie

@sookie-T06sFW • Oct 3, 2009

Hey guys, well done 😁

@vik Thanks for correcting out my problem. Actually I was supposed to write C1 as 1 and C2 as 2 in the PORTFOLIO_HOLDING table but for better understanding of problem , I marked it as C1 , C2 because reference table for CUSTOMER details is not shown in the problem in order to avoid complications in the problem. Anyway, I am editing my mistake. :sshhh:

Now coming to the problem in actual -
You people are dividing the market value by (100 +market value) but in actual the problem is like that market_value should be divided by (sum of total market values in same portfolio)

Hint: Grouping should be done based on "Portfolios"
0
vik001ind

@vik001ind-rOaCSy • Oct 3, 2009

select portfolio_name,aseet_liability_name,market_value as "allocated_value",round(((market_value/(sum(market_value)))*100),2) as "allocated %-asset wise" from ref_asset_liability,portfolio_holding where customer_id = 'C1' AND ref_asset_liabilty.aseet_liability_name = portfolio_holding.aseet_liability_id group by portfolio_name
0
sookie

@sookie-T06sFW • Oct 3, 2009

vik001ind
select portfolio_name,aseet_liability_name,market_value as "allocated_value",round(((market_value/(sum(market_value)))*100),2) as "allocated %-asset wise" from ref_asset_liability,portfolio_holding where customer_id = 'C1' AND ref_asset_liabilty.aseet_liability_name = portfolio_holding.aseet_liability_id group by portfolio_name
Good attempt but dude it will give syntax error as group by function is not used correctly.

PS: Really liking your spirit of trying. Keep it up. 😀
0
Gurjeet Singh

@gurjeet-LUX7B1 • Oct 3, 2009

sookie
Good attempt but dude it will give syntax error as group by function is not used correctly.

PS: Really liking your spirit of trying. Keep it up. 😀

dude can u plz check the datatype of the both tables as the data in the first table is numeric and u enter 1,2,3 so on and now in the second table u are shwing the values in the asset_liablity id which is A1,A2...so on which is var char..so first of all data type mismatch in foregin key as compare to primary key.......
0
vik001ind

@vik001ind-rOaCSy • Oct 3, 2009

cooldudeiet
dude can u plz check the datatype of the both tables as the data in the first table is numeric and u enter 1,2,3 so on and now in the second table u are shwing the values in the asset_liablity id which is A1,A2...so on which is var char..so first of all data type mismatch in foregin key as compare to primary key.......
Its correct!
checkout the condition, i'm giving in my sql query.
0
sookie

@sookie-T06sFW • Oct 3, 2009

cooldudeiet
dude can u plz check the datatype of the both tables as the data in the first table is numeric and u enter 1,2,3 so on and now in the second table u are shwing the values in the asset_liablity id which is A1,A2...so on which is var char..so first of all data type mismatch in foregin key as compare to primary key.......
Thanks for pointing out, "My Big Apologies" for that. Looks like I posted wrong image url. 😔

Hope now everything is fine.
0
vik001ind

@vik001ind-rOaCSy • Oct 3, 2009

select portfolio_name,aseet_liability_name,market_value as 'allocated_value',round(((market_value/(sum(market_value)))*100),2) as 'allocated %-asset wise' from ref_asset_liability,portfolio_holding where customer_id = "C1" AND ref_asset_liabilty.aseet_liability_id = portfolio_holding.aseet_liability_id group by portfolio_name,portfolio_id

I have screwed my head for many hours over this question, suggest some hint, if this is wrong.
0
safwan

@safwan-NH7W5Y • Oct 3, 2009

SQL< select portfolio_name,asset_liability_name,market_value as "allocated_value",market_value/sum(market_value)*100 as "aloocateed % -assetwise" from ref_asset_liability_name,portfolio_holding where customer_id='C1' and ref_asset_liability.asset_liability_name=portfolio_holding.asset_liability_id group by asset_liability_name

well this was my try but oops I saw I think my answer is wrong 😔but never mind . I will try next answer. 😒
0
sookie

@sookie-T06sFW • Oct 4, 2009

Correcting you people's mistake only a bit [rest of the brainy part still left] -

In group by add following fields
portfolio_name, asset_liability_name, market_value .
So query will look like this

select portfolio_name,aseet_liability_name,market_value as 'allocated_value',round(((market_value/(sum(market_value)))*100),2) as 'allocated %-asset wise' from ref_asset_liability,portfolio_holding where customer_id = "C1" AND ref_asset_liabilty.aseet_liability_id = portfolio_holding.aseet_liability_id group by portfolio_name, asset_liability_name, market_value
In group by function you need to add all those columns that are being fetched using select . Now if you are taking market_value also in group by then grouping will be done based on market_value also . So % allocation part will always return "1" as a value. So you need to brainstorm at that part .

Hint: Use group by but at correct place. If you can do so, its ok otherwise wait for the solution.
0
sookie

@sookie-T06sFW • Nov 1, 2009

Hi everyone,

Thanks a lot for the attempts you all have shown for the problem here. Wait is over now - here is the solution for those who are interested to know [AS PER ME] .

My Apologies, for making the bugs full problem :sshhh: . In table 2 PORTFOLIO_HOLDING, as per the data PORTFOLIO_ID cannot be Primary Key. So I have added a separate column "ID" in that table and made it as primary key.

So revised table structures along with data are as follows:
Table 1: REF_ASSET_LIABILITY

Table 2: PORTFOLIO_HOLDING

Now we need the output something like below

So if we write simple query as

"SELECT PORTFOLIO_HOLDING.PORTFOLIO_NAME, REF_ASSET_LIABILITY.ASSET_LIABILITY_NAME,
PORTFOLIO_HOLDING.MARKET_VALUE AS "ALLOCATED_VALUE"
FROM PORTFOLIO_HOLDING PORTFOLIO_HOLDING,REF_ASSET_LIABILITY REF_ASSET_LIABILITY
WHERE PORTFOLIO_HOLDING.ASSET_LIABILITY_ID = REF_ASSET_LIABILITY.ASSET_LIABILITY_ID"
Output will give only first 3 columns of actual output. So, we need to think for Column no 4.

Now if I write simple query like following

"SELECT INNER_PORTFOLIO_HOLDING.PORTFOLIO_ID, SUM(INNER_PORTFOLIO_HOLDING.MARKET_VALUE)
FROM PORTFOLIO_HOLDING INNER_PORTFOLIO_HOLDING,REF_ASSET_LIABILITY REF_ASSET_LIABILITY
WHERE INNER_PORTFOLIO_HOLDING.ASSET_LIABILITY_ID = REF_ASSET_LIABILITY.ASSET_LIABILITY_ID
GROUP BY INNER_PORTFOLIO_HOLDING.PORTFOLIO_ID
"
I will get output like following - showing the total sum by which I need to divide column3 [Of Actual Output table] .

Now, if I divide Column3 by above query by just adding one simple condition more, I will get the desired output. So final query will be

"SELECT PORTFOLIO_HOLDING.PORTFOLIO_NAME, REF_ASSET_LIABILITY.ASSET_LIABILITY_NAME,
PORTFOLIO_HOLDING.MARKET_VALUE AS "ALLOCATED_VALUE",
ROUND(PORTFOLIO_HOLDING.MARKET_VALUE*100/
(SELECT SUM(INNER_PORTFOLIO_HOLDING.MARKET_VALUE)
FROM PORTFOLIO_HOLDING INNER_PORTFOLIO_HOLDING,REF_ASSET_LIABILITY REF_ASSET_LIABILITY
WHERE INNER_PORTFOLIO_HOLDING.ASSET_LIABILITY_ID = REF_ASSET_LIABILITY.ASSET_LIABILITY_ID
AND INNER_PORTFOLIO_HOLDING.PORTFOLIO_ID =PORTFOLIO_HOLDING.PORTFOLIO_ID
GROUP BY INNER_PORTFOLIO_HOLDING.PORTFOLIO_ID
),2) AS "ALLOCATED%-ASSET_WISE"
FROM PORTFOLIO_HOLDING PORTFOLIO_HOLDING,REF_ASSET_LIABILITY REF_ASSET_LIABILITY
WHERE PORTFOLIO_HOLDING.ASSET_LIABILITY_ID = REF_ASSET_LIABILITY.ASSET_LIABILITY_ID"
Everyone is free to correct me if any better optimized query is possible for this problem. 😀
0