HackerRank SQL Solution: City Revenue

The question is:

A number of cities each have a number of agencies that estimate revenues. The average revenue of a city is defined as the average of all agencies’ estimates of revenue for a city.

Write a query to print the floor of the avarage revenue of each city. The order of output does not matter. The result should be in the following format: CITY_NAME, AVERAGE_REVENUE

Schema:

There are 2 tables: CITIES and REVENUE

CITIES:

CITY_CODE — INTEGER — The city’s PINCODE. This is a primary key.

CITY_NAME — STRING — The city’s name.

REVENUE:

CITY_CODE — INTEGER — The city’s PINCODE.

REVENUE — INTEGER — The estimated revenue.

Sample Data Tables:

Sample Output:

new york 10

london 7

paris 15

Explanation:

• new york has only one estimate, so it is the average.
• london has two estimates. The average of those is floor((5+9)/2) = 7.
• paris has only one estimate, so it is the average.

Solution:

SQL Query:

select cities.city_name as city_name, floor(avg(revenue.revenue)) as average_revenue from cities join revenue on cities.city_code = revenue.city_code group by cities.city_name;

Output:

I hope you got the answer and not have to pay on other sites for the SQL query.