Wsrtjtyk

There are 5 relations:

Event (etype, description)                      primary key - etype



City (cname, country, population)               primary key - cname



Disaster (cname, dyear, etype, casualties)      primary keys - cname, dyear



Prediction (cname, etype, casualties)           primary keys - cname, etype



Measures (etype, provider, mcost, percent)      primary keys - etype, provider

etype - type of disaster.

provider - police, fire department...

mcost - cost of those providers.

percent - the percent of the providers to avoid casualties.

I need to write a query which finds out 2 successful providers for predicted disasters in a city. The cost for both must not be higher than 1,000,000.

2 Successful providers defined as the max percent possible summed.

I have this so far:

select

from prediction, measures as m1, measures as m2

where m1.provider < m2.provider AND (m1.mcost + m2.mcost <= 1000000) 

AND m1.percent + m2.percent >= all (select

I don't know if this partial query right and what to do next.

Consider calculating two CTEs: all distinct provider pairs and the max percent of each pair. Then join the two CTEs in main query:

WITH provider_pairs AS (

       SELECT m1.provider AS provider1, m2.provider AS provider2, 

              m1.percent + m2.percent AS sum_percent

       FROM measures m1

       INNER JOIN  measures m2 ON m1.provider < m2.provider

       INNER JOIN prediction p ON p.etype = m1.etype AND p.etype = m2.etype

       WHERE (m1.mcost + m2.mcost <= 1000000) 

  ),

   max_pct AS (

       SELECT MAX(sum_percent) AS max_percent

       FROM provider_pairs p

  )



SELECT p.provider1, p.provider2

FROM provider_pairs p

INNER JOIN max_pct m ON p.sum_percent = m.max_percent

If i got the question correct, you are looking for all pairs of providers from measures table whose combined mcost is less than 100000 and whose combined percentage is the maximum

See if the following helps
One way of doing this would be as follows

1. Cartesian product the tables and filter out the self join condition


2. Sum up the percentage column and sum up the mcost colum


3. Filter out summed_up mcost to be <100000


4. order by summed_up percentage desc and get the first value.

I created a test case as follows

create table measures(provider varchar(50),mcost int, percent int);



insert into measures values('Police',30000,80);

insert into measures values('Fire Department',50000,40);

insert into measures values('Military',40000,50);

insert into measures values('Medical',45000,70);



select * from (

         select row_number() over(order by x.max_percent desc) as rnk

               ,x.*

           from (

         select a.provider

                ,b.provider

                ,greatest(a.provider,b.provider) as combo_pair

                ,a.percent+b.percent as summed_percent

                ,a.mcost+b.mcost as summed_mcost

                ,max(a.percent+b.percent) over(partition by greatest(a.provider,b.provider)) as max_percent

           from measures a

           join measures b

             on 1=1

         where a.provider <> b.provider    

           and a.mcost+b.mcost <100000 /*Check for the combined cost to be <100000*/

         )x

         where x.max_percent=x.summed_percent

)y

where y.rnk=1

Updated DEMO
https://dbfiddle.uk/?rdbms=postgres_8.4&fiddle=7db3297721500ee926c590207a1e57e7