I have recently attended an interview on SQL. Below is the question:

Write a query to retrieve city name, state name, city population and return the following calculations along with the before mentioned fields.

1. Average city population in the state.
2. Difference between the city population and average city population in that state.

City Table:

I have tried below query but I didnot get desired output. Can anyone help me with correct query?

select * from city_table;

select state_name, sum(population)/count(city_name) as average_city_pop
from city_table
group by state_name;

CodePudding user response：

You need to explore sql more, learn about aggregation functions and joins. Your query is correct in obtaining the average population. You need to join it to the original table to get your result.

with avg_city_pop as (select state_name, avg(population) as avg from city group by state_name)
select c.*, acp.avg as average_city_population, abs(acp.avg-c.population) as difference  from city c inner join avg_city_pop acp on c.state_name = acp.state_name;

try it out here

CodePudding user response：

For MySQL versions before 8.0, we can use a correlated subquery to get the average population for each state, which can alss be used to calculate the gap between city population and state average population. Note, the abs function is optionally used to always return a positive number. Omit it if necessary.

select city_name,state_name,(select avg(population) from city where state_name=c.state_name group by state_name ) as average_pop ,
abs(population - (select avg(population) from city where state_name=c.state_name group by state_name )) as gap_pop 
from city c;

CodePudding user response：

select 
   city_name,
   state_name,
   population,
   avg(population) over (partition by state_name) as Avg_pop,
   population-avg(population) over (partition by state_name) as Difference
from city;

see sqlfilldle: https://www.db-fiddle.com/f/tVxdH6bQvsu48umWgLmrQB/0

This needs MySQL 8.0 , because over_clause is available since 8.0.

output: