sqlsql-serverjoin

ER diagram to SQL query

enter image description here

In a test, I was asked for the query to

"show the Net Effective Rent (NER) for all future tenants. This query should be structured to allow further analysis by Province, City, Property, Unit Type and NER/ft2. Make any necessary assumptions".

For which I wrote:

select T.rent*T.leaseterm+M.Charge_Amt*M.duration as NER
from tenant T 
join property P on T.property_ID = P.property_ID
join moveincharges M on M.tenant_ID = T.tenant_ID
join unit U on T.unit_ID = U.unit_ID
where T.status = 2;

I was told that's not the best answer. Was I right? How could I improve the solution?

*NO Data set nor expected result was given for testing the correctness of the solution. The information provided should be sufficient to develop a solution. Think it is a take-home interview test.

Solution

  • I didn't see any use of Property or Unit table in the calculation. So, I have removed those from the query.

    select T.rent * T.leaseterm + M.Charge_Amt * M.duration as NER
from tenant T 
inner join moveincharges M on M.tenant_ID = T.tenant_ID
where T.status = 2;

    You might get provice,city,property and unit_type wise average ner.

    select  P.provice,p.city,p.property,u.unit_type,avg( T.rent*T.leaseterm+M.Charge_Amt*M.duration) as NER
from tenant T 
left join property P on T.property_ID = P.property_ID
inner join moveincharges M on M.tenant_ID = T.tenant_ID
left join unit U on T.unit_ID = U.unit_ID
where T.status = 2;
group by P.provice,p.city,p.property,u.unit_type