Interview Ready Questions on Group By and Window Functions in SQL: Exploring the World Population Dataset.

1. Introduction -

• Database: MS SQL SERVER

• We have data from 20 countries on which we are performing the Grouping and Advance SQL functions like Window Functions.

• For grouping follow - FWGH (From - Where - Group BY - Having) Sequence.

2. Create & Insert Statement -

Creating Database

create database Country;
use Country;

Creating Table

-- Create table
CREATE TABLE Country (
    id INT,
    code VARCHAR(2),
    name VARCHAR(100),
    area INT,
    area_land INT,
    area_water INT,
    population BIGINT,
    population_growth DECIMAL(4, 2),
    birth_rate DECIMAL(5, 2),
    death_rate DECIMAL(5, 2),
    migration_rate DECIMAL(5, 2),
    continent VARCHAR(50) 
);

Insert Statement

INSERT INTO Country (id, code, name, area, area_land, area_water, population, population_growth, birth_rate, death_rate, migration_rate, continent)
VALUES
    (1, 'af', 'Afghanistan', 652230, 652230, 0, 32564342, 2.32, 38.57, 13.89, 1.51, 'Asia'),
    (2, 'al', 'Albania', 28748, 27398, 1350, 3029278, 0.3, 12.92, 6.58, 3.3, 'Europe'),
    (3, 'ag', 'Algeria', 2381741, 2381741, 0, 39542166, 1.84, 23.67, 4.31, 0.92, 'Africa'),
    (4, 'an', 'Andorra', 468, 468, 0, 85580, 0.12, 8.13, 6.96, 0.0, 'Europe'),
    (5, 'ao', 'Angola', 1246700, 1246700, 0, 19625353, 2.78, 38.78, 11.49, 0.46, 'Africa'),
    (6, 'aq', 'Antarctica', 14000000, 280000, 13720000, 1106, NULL, NULL, NULL, NULL, 'Antarctica'),
    (7, 'ar', 'Argentina', 2780400, 2736690, 43710, 43431886, 0.93, 16.64, 7.33, 0.0, 'South America'),
    (8, 'am', 'Armenia', 29800, 28203, 1597, 3056382, 0.2, 13.61, 9.34, 0.0, 'Asia'),
    (9, 'aw', 'Aruba', 180, 180, 0, 112162, 1.22, 12.67, 8.68, 8.09, 'North America'),
    (10, 'au', 'Australia', 7692024, 7682300, 9724, 22751014, 1.05, 12.15, 7.14, 5.65, 'Oceania'),
    (11, 'at', 'Austria', 83858, 82445, 1413, 8665550, 0.55, 9.41, 9.42, 5.56, 'Europe'),
    (12, 'az', 'Azerbaijan', 86600, 82629, 3971, 9780780, 0.96, 16.64, 7.07, 0.0, 'Asia'),
    (13, 'bs', 'Bahamas', 13880, 10010, 3870, 324597, 0.95, 15.5, 7.29, 2.01, 'North America'),
    (14, 'bh', 'Bahrain', 760, 760, 0, 1346613, 4.51, 15.43, 2.71, 13.31, 'Asia'),
    (15, 'bd', 'Bangladesh', 147570, 130170, 17400, 156186882, 1.6, 19.68, 5.96, 0.0, 'Asia'),
    (16, 'bb', 'Barbados', 430, 430, 0, 290604, 0.31, 11.87, 8.69, 0.0, 'North America'),
    (17, 'by', 'Belarus', 207600, 202900, 4700, 9589689, 0.2, 10.7, 13.36, 0.7, 'Europe'),
    (18, 'be', 'Belgium', 30528, 30280, 248, 11323973, 0.66, 11.98, 9.54, 2.91, 'Europe'),
    (19, 'bz', 'Belize', 22966, 22806, 160, 347369, 1.97, 24.86, 5.29, 0.0, 'North America'),
    (20, 'bj', 'Benin', 112622, 110622, 2000, 10448647, 2.78, 38.85, 8.21, 0.0, 'Africa');

3. Table Preview

Query -

select * from Country2;

Output -

4. Questions

Question 1. - --Find the total population of each continent.

• Query:

select sum(population) total_population, continent
from Country2
group by continent;

• Output

---

Question 2. - Calculate the average area_land for countries in each continent.

• Query:

select avg(area_land) avg_land_area, continent 
from country2
group by continent;

• Output

---

Question 3. - List the continents with 5 or more than 5 countries.

• Query:

select continent, count(name) as country_count 
from Country2 
group by continent
having count(name) >= 5;

• Output

---

Question 4. - Show the highest population among countries for each continent.

• Query:

select foo.name, foo.population, foo.continent from
(select max(population) over(partition by continent) as max_pop,
population, name, continent
from Country2) foo
where foo.max_pop = foo.population

• Output

---

Question 6. - Display the continents where the average birth rate is higher than 20.

• Query:

select avg(birth_rate) avg_birth_rate, continent
from Country2
group by continent
having avg(birth_rate) > 20

• Output

---

Question 7. - List the continents with at least two country having a death rate above 10.

• Query:

select continent from Country2
where death_rate > 10
group by continent
having count(name)>=1

• Output

---

Question 8. - Show the continents with more than 3 countries and a total population greater than 100 million.

• Query:

select continent,sum(population) sum_pop,count(name) country_count
from Country2
group by continent
having sum(population) > 100000000
and
count(name)>3

• Output

---

Question 9. - Find the average population growth rate for countries in each continent.

• Query:

select avg(population_growth) avg_population, continent
from Country2
group by continent

• Output

---

Question 10. - Find the continents where the average migration rate is less than 1.

• Query:

select continent, avg(migration_rate) avg_mig_rate
from Country2
group by continent
having avg(migration_rate)<1

• Output

---

Question 11. - Query name of countries where birth_rate is less than avg_birth rate of continent.

• Query:

select a.name,a.birth_rate,
a.avg_birthrate,continent 
from(
    select 
    avg(birth_rate) over(partition by continent)
    avg_birthrate, continent,birth_rate,name
from Country2
) a
where a.birth_rate < a.avg_birthrate

• Output

---

Question 12. - Query Countries which have maximum population growth in their continent.

• Query:

select foo.name, 
foo.Max_pop_growth, 
foo.population_growth,
foo.continent 
from 
(
select max(population_growth) over(partition by continent) Max_pop_growth,
name,continent,
population_growth
from Country2
) foo
where foo.Max_pop_growth = foo.population_growth

• Output

---

Question 13. - For each country, calculate the percentage of the total population it represents within its continent.

• Query:

select foo.name,foo.continent,
foo.population,
foo.sum_pop as continent_tot_pop,
(foo.population* 100)/foo.sum_pop  as percentage_by_continent 
from
(select continent,
name,population,
sum(population) over(partition by continent) sum_pop from Country2) foo;

• Output

---

Question 14. - Rank the countries in each continent based on their population.

• Query:

select name,continent,population,
Rank() over(partition by continent order by population) as country_rank 
from Country2;

• Output

---

Question 15. - Calculate the cumulative population for each continent in ascending order based on their population.

• Query:

select continent,population,
sum(population) over(partition by continent order by population) sum_pop 
from Country2;

• Output

---

Question 16. - Lead Lag Questions.

• Query:

select name, area,
lag(area) over(order by name) as previous_area,
lead(area) over(order by name) as next_area
from Country2;

• Output