Real time Problems/Challenges

Replies

• 

  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 .
• 

  sookie

  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 😐 *
• 

  Ashraf HZ

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

  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!
• 

  Manish Goyal

  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
• 

  vik001ind

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

  sookie

  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"
• 

  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
• 

  sookie

  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. 😀
• 

  Gurjeet Singh

  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.......
• 

  vik001ind

  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.
• 

  sookie

  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.
• 

  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_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.
• 

  safwan

  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. 😒
• 

  sookie

  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.
• 

  sookie

  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. 😀