Basic HackerRank SQL Challenges#
Unless otherwise noted, assume these examples where tested on MySQL and MySQL CLI. I prefer MariaDB and PostgreSQL by it seems HackerRank does not provide any of those (as of June 18, 2023 at least).
Also, we’ll try to stick to standard-compliant (and shy away from vendor-specific features and syntax) SQL whenever possible for max portability.
RackerRank SQL challenges come in three main levels of difficulty:
This page deals with the basic ones.
Revising The Select Query I#
SELECT id , name , countrycode , district , population FROM city WHERE population >= 100000 AND countrycode = 'USA';
We can replace all column names with the star
*, but this is more readable as all column names are explicitly stated in the query.
Also, we could use
LIKE in place of
= for this case:
… AND countrycode LIKE 'USA'.
Revising the Select Query II#
SELECT name FROM city WHERE population > 120000 AND countrycode LIKE 'USA';
SELECT id , name , countrycode , district , population FROM city;
Again, writing the column names explicitly makes it more self-documenting than simply using
Select By ID#
SELECT id , name , countrycode , district , population FROM city WHERE id = 1661;
Japanese Cities’ Attributes#
SELECT name FROM city WHERE countrycode = 'JPN';
Weather Observation Station 1#
SELECT city , state FROM station;
Weather Observation Station 2#
SELECT ROUND(SUM(lat_n), 2) AS lat , ROUND(SUM(long_w), 2) AS lon FROM station;
So we basically round the result of the sum and rename the output columns as
SUM any numeric column, e.g.:
SELECT SUM(id) FROM users;
ROUND(value, num_decimal_places), e.g.:
mydb=# SELECT ROUND(3.141592653589793, 5) AS my_pi; my_pi --------- 3.14159
Weather Observation Station 3#
SELECT DISTINCT city FROM station WHERE id % 2 = 0;
SELECT DISTINCT to avoid duplicates.
For the “even IDs”, the old, battle-tested tested integer division compared with 0 does the trick. It seems
% has higher precedence than
=, but we could wrap the remainder division in parentheses too if we wanted to make sure or make it more explicit/clear:
SELECT DISTINCT city FROM station WHERE (id % 2) = 0;
Weather Observation Station 4#
SELECT (COUNT(city) - COUNT(DISTINCT city)) AS count FROM station;
Looks like they consider only the city name (
city) column to determine if the city is the same or not.
COUNT(city) returns some number, and
COUNT(DISTINCT city) can potentially return another number, which we subtract from the first, producing the correct result expected by the challenge.
Note we didn’t call
DISTINCT as a function, with parentheses, like
Assume this table and data:
CREATE TABLE users ( id INTEGER PRIMARY KEY , name VARCHAR(128) NOT NULL ); INSERT INTO users ( id , name ) VALUES (1, 'Yoda') , (2, 'Ahsoka tano') , (3, 'Aayla Secura') , (4, 'Leia') , (5, 'Leia');
Then see how it is possible to use both
DISTINCT name or
> SELECT id, name FROM users; id | name ----+-------------- 1 | Yoda 2 | Ahsoka tano 3 | Aayla Secura 4 | Leia 5 | Leia (5 rows) > SELECT COUNT(name) FROM users; count ------- 5 (1 row) parentheses -------------- > SELECT COUNT(DISTINCT(name)) FROM users; count ------- 4 (1 row) no parentheses ------------- > SELECT COUNT(DISTINCT name) FROM users; count ------- 4 (1 row) parentheses -------------- > SELECT (COUNT(name) - COUNT(DISTINCT(name))) AS count FROM users; count ------- 1 (1 row) no parentheses ------------- > SELECT (COUNT(name) - COUNT(DISTINCT name)) AS count FROM users; count ------- 1 (1 row)
I tested this on both PostgreSQL and MariaDB and both vendors accept both syntaxes.
Weather Observation Station 6#
Works on DB vendors whose collation allows case insensitive matches:
SELECT DISTINCT city FROM station WHERE city LIKE 'a%' OR city LIKE 'e%' OR city LIKE 'i%' OR city LIKE 'o%' OR city LIKE 'u%';
This approach extracts the first char of the
title column and uses
IN to compare.
It relies on the fact that cities start with an uppercase letter:
SELECT DISTINCT title FROM entries WHERE SUBSTR(title, 1, 1) IN('A', 'E', 'I', 'O', 'U');
And this one, similar to the above, just lowercases the first char of the title before doing the comparison:
SELECT DISTINCT title FROM entries WHERE LOWER(SUBSTR(title, 1, 1)) IN('a', 'e', 'i', 'o', 'u');
Weather Observation Station 5#
UNION ALL of two queries#
One approach is to use two queries: one for the city name length, and another one for the min city name length, limit by 1, and union the results to create a single resulting tabular structure:
(SELECT city , LENGTH(city) AS len_city FROM station ORDER BY len_city ASC, city DESC LIMIT 1) UNION ALL (SELECT city , LENGTH(city) AS len_city FROM station ORDER BY len_city DESC, city DESC LIMIT 1);
WHERE IN union of subquery#
Or creating a sub-table to select the lengths from.
First, select the max and min lengths:
SELECT MAX(LENGTH(city)) FROM station UNION ALL SELECT MIN(LENGTH(city)) FROM station;
It returns a tabular structures with the max and min lengths, which can be used in a
SELECT city , LENGTH(city) FROM station WHERE LENGTH(city) IN ( SELECT MAX(LENGTH(city)) FROM station UNION ALL SELECT MIN(LENGTH(city)) FROM station ) ORDER BY LENGTH(city) DESC, city;