Population Density

#10368 #Deloitte 

You are working on a data analysis project at Deloitte where you need to analyze a dataset containing information

about various cities. Your task is to calculate the population density of these cities, rounded to the nearest integer, and identify the cities with the minimum and maximum densities.

The population density should be calculated as (Population / Area).

The output should contain 'city', 'country', 'density'.

table: cities_population


Input:

STEP 1: Get the min and max density using over clause.

select city,country
,convert(int ,population/area) as density,
min([population]/area) over () as minDen,
max([population]/area) over () as maxDen
from cities_population
where area>0

Output:

STEP 2: Filter the records based on the condition where density matched with minDen and maxDen.

Final Solution:

with cte as(
select city,country
,convert(int ,population/area) as density,
min([population]/area) over () as minDen,
max([population]/area) over () as maxDen
from cities_population
where area>0
)
select city,country,density from cte
where density = minDen or density = maxDen
order by density

Final Output:


SQL Script:

USE [StrataScratch]
GO
/****** Object:  Table [dbo].[cities_population]    Script Date: 22-03-2024 13:46:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cities_population](
	[city] [varchar](50) NULL,
	[country] [varchar](50) NULL,
	[population] [int] NULL,
	[area] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Metropolis', N'Countryland', 1000000, 500)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Smallville', N'Countryland', 50000, 1000)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Coastcity', N'Oceanland', 300000, 0)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Starcity', N'Mountainous', 600000, 600)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Gotham', N'Islander', 1500000, 300)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Rivertown', N'Plainsland', 100000, 5000)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Lakecity', N'Forestland', 100000, 5000)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Hilltown', N'Hillside', 200000, 450)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Forestville', N'Forestland', 500000, 700)
GO
INSERT [dbo].[cities_population] ([city], [country], [population], [area]) VALUES (N'Oceanview', N'Seaside', 800000, 0)
GO

Comments (0)
Questions List