SQL_Coding_Challenge

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