SQL_Coding_Challenge

SQL_Coding_Challenge copied to clipboard

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

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;

Expected Results:

Results located in /source_data/csv_output/output.csv

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();