Most donor to acceptor ratio

Question

There is a blood bank which maintains two tables: DONOR that contains information on the people who are willing to donate blood and ACCEPTOR, the people who are in need of blood. The bank wants to conduct a survey and find out the city that has the best and the worst donor sum amount/acceptor sum amount ratio. Both ratios are unique. That is, exactly one city has the best ratio and exactly one city has the worst ratio. The donor sum amount is the total amount of blood, regardless of blood group, that people are ready to donate. The acceptor sum amount is the total amount of blood needed by that city.

There must be exactly two rows that denote the best and the worst ratios. The order of the row does not matter. Each row must contain the following attributes: The say name (CITY). The ratio (donor sum amount/acceptor sum amount ), correct to 4 decimal places.

the schema tables is given below:

These are the two tables

select city, max(d.'abc') from(select CITY, (sum (a. 'amount')/sum (b. amount')) abc from 'donor' a join 'acceptor' b on a.'city'=b.'city' group by a.'city')d

Here is my approach, it's telling me I have some manual error and I can't figure it out.

Answer 1

Your errors include using single quotes when you should be using backticks and sum (b. amount') - amount has closing quote(which should be a backtick) but no opening backtick. NB you don't need backticks at all unless identifiers are reserved words. When to use single quotes, double quotes, and backticks in MySQL

Answer 2

Tried this query, it's working fine. However, I still feel there can be a shorter answer to this.

select * from
(select *, row_number() over (order by abc) as rn from
(select a.city, (sum(a.amount)/sum(b. amount)) abc 
from donor as a 
join 
acc as b on a.city=b.city 
group by a.city)d)e where e.rn=1
union
(select * from 
(select *, row_number() over (order by abc desc) as rn from
(select a.city, (sum(a.amount)/sum(b. amount)) abc 
from donor as a 
join 
acc as b on a.city=b.city 
group by a.city)d)e where e.rn=1);