Write SQL to sum city population by name
Company: NVIDIA
Role: Software Engineer
Category: Software Engineering Fundamentals
Difficulty: easy
Interview Round: Technical Screen
You have four relational tables:
- country(country_id, name)
- state(state_id, country_id, name)
- city(city_id, state_id, name)
- zip(zip_code, city_id, population)
Task A: Given a city name (e.g., :city_name), write SQL to return the total population for that city, where population is stored in the zip table (sum across all ZIP codes belonging to the city).
Clarify how you would handle the case where multiple cities share the same name in different states/countries.
Overview: Assesses SQL proficiency in relational joins and aggregation by asking for a total population computed from ZIP-level data and evaluates reasoning about duplicate entity names.
Community answers
Answer by mayer33
SELECT c.name as nm,
SUM(z.polulation) as population
FROM city c
JOIN zip z
ON c.city_id = z.city_id
WHERE nm = city_name
GROUP BY nm
Answer by reddy.sanjana2k
city name is given,
So join the city table with the zip table using city_id, since the population is stored at the zip level
filter the records by the given city name
Group by city_id and city_name and aggreagate to calculate the total population across all ZIP codes belonging to that city.
each matching city is returned as a separate row because the grouping is done by city_id, city_name
SELECT
c.city_id,
c.name AS city_name,
SUM(z.population) AS total_population
FROM city c
JOIN zip z
ON c.city_id = z.city_id
WHERE c.name = :city_name
GROUP BY c.city_id, c.name;
// we can also return state and country name in output
join the city, state, country, and zip tables using their foreign key relationships
filterby city_name, state_name, and country_name to uniquely identify the correct city, since the same city name can exist in different states or countries.
SUM(population) aggregates the population across all ZIP codes belonging to the selected city and group by the city, state, and country details to return population total for the given city name
he data is filtered by city_name, state_name, and country_name to uniquely identify the correct city, since the same city name can exist in different states or countries. The zip table is included because it stores the population for each ZIP code. The SUM(population) function aggregates the population across all ZIP codes belonging to the selected city. Finally, the results are grouped by the city, state, and country details to retur