Basic/Intermediate SQL Code Challenge

Author: Jaime M. Shaker
Email: [email protected]
Website: https://www.shaker.dev
LinkedIn: https://www.linkedin.com/in/jaime-shaker/

This project is an opportunity to practice your SQL skills. This project was inspired by the Braintree Analytics Code Challenge and was created to strengthen/sharpen your SQL knowledge.

❗ If you find this repository helpful, please consider giving it a ⭐. Thanks! ❗

Please Note:

This project is meant to measure/gauge/practice your technical abilities with SQL.

• All work should be done in SQL.
• Do not alter the CSV files.
• Most, if not all of these questions are intended purely as a measure of your SQL skills and not as actual questions that one would expect to answer from an employer.

An SQL Code Challenge Walkthrough has been created if you would like to follow along in a step by step fashion.

The questions are listed below with the expected results and answers hidden so you can cross reference your answers.

This repository contains all of the necessary files, datasets and directories for running a PostgresSQL server in a Docker Container. The only prerequisite is that you should already have Docker Desktop installed and running on your computer.

https://www.docker.com/products/docker-desktop/

Getting Started

• This project uses the latest version of PostgreSQL, but any SQL database may be used.
• Directories and Files details:
  • README.md: This is the file your are currently reading.
  • .gitignore: The files and directories listed in this file will be ignored by Git/GitHub.
  • docker-compose.yaml: With this yaml (Yet Another Markup Language) file you can define the services (Postgres/MySQL/Python...) and with a single command, you can spin everything up or tear it all down.
  • source_data/csv_data: This is the location of the CSV files needed to copy into our database.
  • db: In the current directory, create an empty directory named 'db'. Although this directory is ignored by Git/GitHub, PostgreSQL requires a directory to keep data persistent if you are running a Docker container. There will be no need to alter any of the files that reside there once the container starts running.
  • images: This is the location of the image files displayed in this repository.
  • walkthrough/: A directory with a beginner friendly walkthrough of all of the steps I took to complete the SQL challenge.
  • source_data/: This directory contains all the files and scripts within our Docker container.
  • source_data/csv_data: This is the location of the CSV files needed to copy/insert into our database.
  • source_data/scripts/: This directory will contain all sql scripts I used to complete the challenge.
  • source_data/csv_output/: This directory will contain all CSV files generated by some of our queries.

❕ ⭐ Start here if you want to follow along with the SQL Challenge WALKTHROUGH ⭐ ❕

SQL Code Challenge

1. Create Database, Tables and Relations.
Using the CSV files located in source_data/csv_data, create your new SQL database and tables with the properly formatted data.

• Add a numeric, auto-incrementing Primary Key to every table.
• In the countries table, add the column created_on with the current date.
• Create a one-to-one and one-to-many relationship with the countries table as the parent table.

2. List Regions and Country Count
List all of the regions and the total number of countries in each region. Order by country count in descending order and capitalize the region name.

Click to expand expected results!

Expected Results:

region	country_count
Africa	59
Americas	57
Asia	50
Europe	48
Oceania	26
Antartica	1

Click to expand answer!

Answer

SELECT 
  -- initcap() capitalizes the first letter of every word in a string.
  initcap(region) AS region,
  count(*) AS country_count
FROM
  cleaned_data.countries
GROUP BY
  -- Aggregate functions 'count()' require you to group all column fields.
  region
ORDER BY 
  country_count DESC;

3. List Sub-Regions and City Count
List all of the sub-regions and the total number of cities in each sub-region. Order by sub-region name alphabetically.

Click to expand expected results!

Expected Results:

sub_region	city_count
Australia And New Zealand	329
Central Asia	560
Eastern Asia	3164
Eastern Europe	2959
Latin America And The Caribbean	7204
Melanesia	60
Micronesia	15
Northern Africa	1152
Northern America	5844
Northern Europe	2025
Polynesia	22
Southeastern Asia	2627
Southern Asia	6848
Southern Europe	3238
Subsaharan Africa	3223
Western Asia	1400
Western Europe	3952

Click to expand answer!

Answer

SELECT 
  initcap(t1.sub_region) AS sub_region,
  count(*) AS city_count
FROM
  cleaned_data.countries AS t1
JOIN 
  cleaned_data.cities AS t2
ON
  t1.country_code_2 = t2.country_code_2
GROUP BY
  t1.sub_region
ORDER BY 
  t1.sub_region;

4. Specific Sub-Region and String Functions
List all of the countries and the total number of cities in the Northern Europe sub-region. List the country names in uppercase and order the list by the length of the country name and alphabetically in ascending order.

Click to expand expected results!

Expected Results:

country_name	city_count
JERSEY	1
LATVIA	39
NORWAY	127
SWEDEN	148
DENMARK	75
ESTONIA	20
FINLAND	142
ICELAND	12
IRELAND	64
LITHUANIA	61
ISLE OF MAN	2
FAROE ISLANDS	29
UNITED KINGDOM	1305

Click to expand answer!

Answer

SELECT 
  -- upper() returns your string in uppercase.
  upper(t1.country_name) AS country_name,
  count(*) AS city_count
FROM
  cleaned_data.countries AS t1
JOIN 
  cleaned_data.cities AS t2
ON 
  t1.country_code_2 = t2.country_code_2
WHERE
  t1.sub_region = 'northern europe'
GROUP BY 
  t1.country_name
ORDER BY 
  -- length() returns the number or characters in a string including spaces.  
  length(t1.country_name), t1.country_name;

5. List Specific Countries by Year
List all of the countries and the total number of cities in the Southern Europe sub-region that were inserted in 2021. Capitalize the country names and order alphabetically by the LAST letter of the country name and the number of cities.

Click to expand expected results!

Expected Results:

country_name	city_count
Andorra	5
Albania	11
Bosnia And Herzegovina	15
Croatia	22
North Macedonia	28
Malta	32
Serbia	58
Slovenia	74
Greece	64
Portugal	109
Spain	302
San Marino	2
Montenegro	12
Italy	542

Click to expand answer!

Answer

SELECT 
  initcap(t1.country_name) AS country_name,
  count(*) AS city_count
FROM
  cleaned_data.countries AS t1
JOIN 
  cleaned_data.cities AS t2
ON 
  t1.country_code_2 = t2.country_code_2
WHERE
  t1.sub_region = 'southern europe'
AND
  -- extract() & date_part() functions allow you to breakdown dates and timestamps to individual years, month, days, hours.....
  EXTRACT('year' FROM t2.insert_date) = 2021
GROUP BY 
  t1.country_name
ORDER BY 
  -- substring() function returns a specific portion of a string.
  substring(t1.country_name,length(t1.country_name),1), city_count;

6. List Anti-Join
List all of the countries in the region of Asia that did NOT have a city with an inserted date from June 2021 through Sept 2021.

Click to expand expected results!

Expected Results:

country_name
Brunei Darussalam
Kuwait
Macao
Singapore

Click to expand answer!

Answer

SELECT 
  -- Distinct will only return unique values
  DISTINCT initcap(t1.country_name) AS country_name
FROM
  cleaned_data.countries AS t1
-- Left join will return all matching values from the left table (cleaned_data.countries) and
-- only the matching values from the right table (cleaned_tables.cities) resulting in NULLS where
-- there are no matches in the left table.
LEFT JOIN 
  cleaned_data.cities AS t2
ON 
  t1.country_code_2 = t2.country_code_2
AND
  t2.insert_date BETWEEN '2021-06-01' AND '2021-10-01'
WHERE
  t1.region = 'asia'
-- Only return values that did NOT have a match with the countries table.
AND 
  t2.country_code_2 IS NULL;

7. Reversable Names
List the country, city name, population and city name length for the city names that are palindromes in the Western Asia sub-region. Format the population with a thousands separator (1,000) and format the length of the city name in roman numerals. Order by the length of the city name in descending order and alphabetically in ascending order.

Click to expand expected results!

Expected Results:

country_name	city_name	population	roman_numeral_length
Yemen	Hajjah	46,568	VI
Syrian Arab Republic	Hamah	696,863	V
Turkey	Kavak	21,692	V
Turkey	Kinik	29,803	V
Turkey	Tut	10,161	III

Click to expand answer!

Answer

SELECT 
  initcap(t1.country_name) AS country_name,
  initcap(t2.city_name) AS city_name,
  -- to_char() takes non-string data types and returns them as strings.
  to_char(t2.population, '999G999') AS population,
  to_char(length(t2.city_name), 'RN') AS roman_numeral_length
FROM
  cleaned_data.countries AS t1
JOIN 
  cleaned_data.cities AS t2
ON 
  t1.country_code_2 = t2.country_code_2
WHERE
  t2.city_name = reverse(t2.city_name)
AND
  t1.sub_region = 'western asia'
ORDER BY 
  length(t2.city_name) DESC, t2.city_name ASC;

8. Search with Wildcard and Case
List all of the countries that end in 'stan'. Make your query case-insensitive and list whether the total population of the cities listed is an odd or even number for cities inserted in 2022. Order by whether the population value is odd or even in ascending order and country name in alphabetical order.

Click to expand expected results!

Expected Results:

country_name	total_population	odd_or_even
Afghanistan	6,006,530	Even
Kazakhstan	4,298,264	Even
Kyrgyzstan	1,017,644	Even
Pakistan	26,344,480	Even
Tajikistan	2,720,953	Odd
Turkmenistan	419,607	Odd
Uzbekistan	3,035,547	Odd

Click to expand answer!

Answer

SELECT
  initcap(t1.country_name) AS country_name,
  to_char(sum(t2.population), '99G999G999') total_population,
  CASE
  	WHEN (sum(t2.population) % 2) = 0
  		THEN 'Even'
  	ELSE 
  		'Odd'
  END AS odd_or_even
FROM
  cleaned_data.countries AS t1
JOIN 
  cleaned_data.cities AS t2
ON 
  t1.country_code_2 = t2.country_code_2
WHERE
  t1.country_name ILIKE '%stan'
AND 
  EXTRACT('year' FROM t2.insert_date) = 2022
GROUP BY
  t1.country_name
ORDER BY 
  odd_or_even, country_name;

9. Ranking Regions
List the third most populated city ranked by region WITHOUT using limit or offset. List the region name, city name, population and order the results by region.

Click to expand expected results!

Expected Results:

region	city_name	third_largest_pop
Africa	Kinshasa	12,836,000
Americas	New York	18,972,871
Asia	Delhi	32,226,000
Europe	Paris	11,060,000
Oceania	Brisbane	2,360,241

Click to expand answer!

Answer

WITH get_city_rank_cte AS (
  SELECT
  	t1.region,
  	t2.city_name,
  	t2.population AS third_largest_pop,
  	DENSE_RANK() OVER (PARTITION BY t1.region ORDER BY t2.population DESC) AS rnk
  FROM
  	cleaned_data.countries AS t1
  JOIN 
  	cleaned_data.cities AS t2
  ON 
  	t1.country_code_2 = t2.country_code_2
  WHERE 
  	t2.population IS NOT NULL
  GROUP BY
  	t1.region,
  	t2.city_name,
  	t2.population
)
SELECT
  initcap(region) AS region,
  initcap(city_name) AS city_name,
  to_char(third_largest_pop, '99G999G999') AS third_largest_pop
FROM
  get_city_rank_cte
WHERE
  rnk = 3;

10. Using Buckets
List the bottom third of all countries in the Western Asia sub-region that speak Arabic. Include the row number and country name. Order by row number.

Click to expand expected results!

Expected Results:

row_id	country_name
9	saudi arabia
10	syrian arab republic
11	united arab emirates
12	yemen

Click to expand answer!

Answer

WITH get_ntile_cte AS (
  SELECT
  	ROW_NUMBER() OVER (ORDER BY t1.country_name) AS rn,
  	t1.country_name,
  	-- ntile() window functions returns groups of data section into 'buckets'.
  	NTILE(3) OVER (ORDER BY t1.country_name) AS nt
  FROM
  	cleaned_data.countries AS t1
  JOIN 
  	cleaned_data.languages AS t2
  ON
  	t1.country_code_2 = t2.country_code_2
  WHERE
  	t1.sub_region = 'western asia'
  AND 
  	t2.language = 'arabic'
)
SELECT
  rn AS row_id,
  country_name
FROM
  get_ntile_cte
WHERE
  nt = 3;

11. Using Arrays
Create a query that lists country name, capital name, population, languages spoken and currency name for countries in the Northen Africa sub-region. There can be multiple currency names and languages spoken per country. Add multiple values for the same field into an array.

Click to expand expected results!

Expected Results:

country_name	city_name	population	languages	currencies
algeria	algiers	3415811	{french,arabic,kabyle}	algerian dinar
egypt	cairo	20296000	{arabic}	egyptian pound
libya	tripoli	1293016	{arabic}	libyan dinar
morocco	rabat	572717	{arabic,tachelhit,moroccan tamazight,french}	moroccan dirham
sudan	khartoum	7869000	{arabic,english}	sudanese pound
tunisia	tunis	1056247	{french,arabic}	tunisian dinar

Click to expand answer!

Answer

WITH get_row_values AS (
  SELECT
  	t1.country_name,
  	t2.city_name,
  	t2.population,
  	-- array_agg() aggregates multiple values and returns them in 'array' format.
  	array_agg(t3.LANGUAGE) AS languages,
  	t4.currency_name AS currencies
  FROM
  	cleaned_data.countries AS t1
  JOIN
  	cleaned_data.cities AS t2
  ON 
  	t1.country_code_2 = t2.country_code_2
  JOIN
  	cleaned_data.languages AS t3
  ON 
  	t1.country_code_2 = t3.country_code_2
  JOIN
  	cleaned_data.currencies AS t4
  ON 
  	t1.country_code_2 = t4.country_code_2
  WHERE
  	t1.sub_region = 'northern africa'
  AND
  	t2.capital = TRUE
  GROUP BY
  	t1.country_name,
  	t2.city_name,
  	t2.population,
  	t4.currency_name
)
SELECT
  *
FROM
  get_row_values;

12. Using Case and Percentages
Produce a query that returns the city names for cities in the U.S. that were inserted on April, 28th 2022. List how many vowels and consonants are present in the city name and concatnate their percentage to the their respective count in parenthesis.

Click to expand expected results!

Expected Results:

city_name	vowel_count_perc	consonants_count_perc
standish	2 (25.00%)	6 (75%)
grand forks	2 (18.18%)	9 (81.82%)
camano	3 (50.00%)	3 (50%)
cedar hills	3 (27.27%)	8 (72.73%)
gladstone	3 (33.33%)	6 (66.67%)
whitehall	3 (33.33%)	6 (66.67%)
homewood	4 (50.00%)	4 (50%)
willowbrook	4 (36.36%)	7 (63.64%)
port salerno	4 (33.33%)	8 (66.67%)
vadnais heights	5 (33.33%)	10 (66.67%)
jeffersonville	5 (35.71%)	9 (64.29%)

Click to expand answer!

Answer

WITH get_letter_count AS (
  SELECT
  	t1.city_name,
  	length(t1.city_name) string_length,
  	-- regexp_replace() returns a vlue that has been manipulated by a regular expression.
  	length(regexp_replace(t1.city_name, '[aeiou]', '', 'gi')) AS consonant_count
  FROM
  	cleaned_data.cities AS t1
  WHERE
  	t1.insert_date = '2022-04-28'
  AND
  	t1.country_code_2 in ('us')
),
get_letter_diff AS (
  SELECT
  	city_name,
  	string_length - consonant_count AS vowels,
  	round(100 * (string_length - consonant_count) / string_length::NUMERIC, 2) AS vowel_perc,
  	consonant_count AS consonants,
  	round( 100 * (consonant_count)::NUMERIC / string_length, 2)::float AS consonants_perc
  FROM
  	get_letter_count
)
SELECT 
  city_name,
  vowels || ' (' || vowel_perc || '%)' AS vowel_count_perc,
  consonants || ' (' || consonants_perc || '%)' AS consonants_count_perc
FROM
  get_letter_diff
ORDER BY 
  vowels;

13. Most Consecutive Days
List the most consecutive inserted dates and the capitalized city names for cities in Canada that where inserted in April 2022.

Click to expand expected results!

Expected Results:

most_consecutive_dates	city_name
2022-04-22	South Dundas
2022-04-23	La Prairie
2022-04-24	Elliot Lake
2022-04-25	Lachute

Click to expand answer!

Answer

DROP TABLE IF EXISTS get_dates;
CREATE TEMP TABLE get_dates AS (
  SELECT
  	DISTINCT ON (insert_date) insert_date AS insert_date,
  	city_name
  FROM
  	cleaned_data.cities
  WHERE
  	country_code_2 = 'ca'
  AND
  	insert_date BETWEEN '2022-04-01' AND '2022-04-30'
  ORDER BY
  	insert_date
);

DROP TABLE IF EXISTS get_diff;
CREATE TEMP TABLE get_diff AS (
  SELECT
  	city_name,
  	insert_date,
  	EXTRACT('day' FROM insert_date) - ROW_NUMBER() OVER (ORDER BY insert_date) AS diff
  FROM
  	get_dates
);


DROP TABLE IF EXISTS get_diff_count;
CREATE TEMP TABLE get_diff_count AS (
  SELECT
  	city_name,
  	insert_date,
  	count(*) OVER (PARTITION BY diff) AS diff_count
  FROM
  	get_diff
);


WITH get_rank AS (
  SELECT
  	DENSE_RANK() OVER (ORDER BY diff_count desc) AS rnk,
  	insert_date,
  	city_name
  FROM
  	get_diff_count
)
SELECT
  insert_date AS most_consecutive_dates,
  initcap(city_name) AS city_name
FROM
  get_rank
WHERE
  rnk = 1
ORDER BY 
  insert_date;

14. Month over Month in View
Create a view that lists the month-year, the number of cities inserted for that month, a running city count total and the month over month percentage growth for 2021.

Format the cities count and the running total with the thousands separator and format the month over month growth with a plus symbol and percentage symbol

Example:

month_year	cities_inserted	running_total	month_over_month
Feb-2021	1,291	2,762	+87.76%

Click to expand expected results!

Expected Results:

month_year	cities_inserted	running_total	month_over_month
Jan-2021	1,471	1,471
Feb-2021	1,291	2,762	+87.76%
Mar-2021	1,485	4,247	+53.77%
Apr-2021	1,508	5,755	+35.51%
May-2021	1,509	7,264	+26.22%
Jun-2021	1,395	8,659	+19.20%
Jul-2021	1,394	10,053	+16.10%
Aug-2021	1,481	11,534	+14.73%
Sep-2021	1,452	12,986	+12.59%
Oct-2021	1,446	14,432	+11.14%
Nov-2021	1,378	15,810	+9.55%
Dec-2021	1,472	17,282	+9.31%

Click to expand answer!

Answer

DROP VIEW IF EXISTS cleaned_data.view_2021_growth;
CREATE VIEW cleaned_data.view_2021_growth AS (
  WITH get_month_count AS (
  	SELECT
  		date_trunc('month', insert_date) as single_month,
  	  	count(*) AS monthly_count
  	FROM 
  		cleaned_data.cities
  	WHERE 
  		EXTRACT('year' FROM insert_date) = 2021
  	GROUP BY 
  		single_month
  	ORDER BY 
  		single_month
  ),
  get_running_total AS (
  	SELECT
  		single_month::date,
  	  	monthly_count,
  	  	sum(monthly_count) OVER (ORDER BY single_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_num_cities
  	FROM
  		get_month_count
  ),
  get_month_over_month AS (
  	SELECT
  		single_month,
  		monthly_count,
  		total_num_cities,
  		round(100.0 * ((total_num_cities - Lag(total_num_cities, 1) OVER (ORDER BY single_month)) / Lag(total_num_cities, 1) OVER (ORDER BY single_month))::NUMERIC, 2) AS month_over_month
  	FROM
  		get_running_total
  )
  SELECT
  	to_char(single_month, 'Mon-YYYY') AS month_year,
  	to_char(monthly_count, '9G999') AS cities_inserted,
  	to_char(total_num_cities, '99G999') AS running_total,
  	to_char(month_over_month, 'sg99.99') || '%' AS month_over_month
  FROM
  	get_month_over_month
);

SELECT 
  *
FROM 
  cleaned_data.view_2021_growth;

15. Stored Procedure to CSV
Create and call a stored procedure that lists a unique row id number, insert date, country name, city name, population and languages spoken for countries in the Latin America and Caribbean sub-region that were inserted on either '2022-04-09', '2022-04-28' or '2022-08-11'.

Order by the insert date and output the results (including headers) to a CSV file located in /source_data/csv_output/ .

Click to expand expected results!

Expected Results:

Results located in /source_data/csv_output/output.csv

Click to expand answer!

Answer

CREATE OR REPLACE PROCEDURE cleaned_data.sproc_output ()
LANGUAGE plpgsql
AS 
$sproc$
  BEGIN
  	COPY (
  		SELECT
  			ROW_NUMBER() OVER (ORDER BY t1.insert_date) AS row_id,
  			t1.insert_date,
  			t2.country_name,
  			t1.city_name,
  			t1.population,
  			array_agg(t3.language) AS languages
  		FROM
  			cleaned_data.cities AS t1
  		JOIN
  			cleaned_data.countries AS t2
  		ON 
  			t1.country_code_2 = t2.country_code_2
  		LEFT JOIN
  			cleaned_data.languages AS t3
  		ON 
  			t2.country_code_2 = t3.country_code_2
  		WHERE
  			t2.sub_region = 'latin america and the caribbean'
  		AND
  			t1.insert_date IN ('2022-04-09', '2022-04-28', '2022-08-11')
  		GROUP BY 
  			t1.insert_date,
  			t2.country_name,
  			t1.city_name,
  			t1.population
  		ORDER BY
  			t1.insert_date
  		)
  	TO '/var/lib/postgresql/source_data/csv_output/output.csv' DELIMITER ',' CSV HEADER;
  END
$sproc$;

-- Call the stored procedure
CALL cleaned_data.sproc_output();

❕ Start here if you want to follow along with the SQL Challenge WALKTHROUGH ❕

❗ If you found this repository helpful, please consider giving it a ⭐. Thanks! ❗