Been working on this for a couple of hours now and I wonder if there is a recommended solution for this in SQL or if I am better off putting this in my application logic instead of the database. I'd prefer the former.

```
CREATE TABLE `rate_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city` varchar(100) DEFAULT NULL,
`rate_type` varchar(45) DEFAULT NULL,
`rate` int(11) DEFAULT NULL,
`vendor` varchar(45) DEFAULT NULL,
`product` varchar(45) DEFAULT NULL,
`days_to_pickup` int(11) DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`state` varchar(45) DEFAULT NULL,
`country` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=787 DEFAULT CHARSET=latin1$$

```

I need come up with the best estimate for average rate of each product and vendor combination, given a city, state & country. Each vendor can have a different number of products. Also, the number of vendors in each city can be different. The logic needs to be 

1. Return data for records matching city, state and country, if a match is found.
2. If a match is not found, try to find results based on state & country.
3. If that doesn't return anything either, find results based on country only
4. Last resort, use all records to come up with the average rate of each product, vendor combination
5. There is additional logic for other columns, but they all follow the same pattern as location.

The first query will be something like

```
select avg(rate) rate, vendor, product from rate_info
where city = 'Boston'
and state = 'MA'
and country = 'United States'
and rate_type = 'REGULAR'
and days_to_pickup >= 4
group by vendor, product 

```

I have been playing around with COALESCE, CASE, IFNULL, but can't get the queries to work. I can do it in the application layer, but I'd have to make multiple calls to the database as there is high likelihood that the first case will not be met. 

The ideal solution would be something like

```
COALESCE(select_city_state_cntr, select_state_cntry, select_cntry);

```

Let me know if something is missing and I'll add more details as required. 

Thank you