Series
SQL on HackerRank
HackerRank SQL exercises solved and explained with their real tables, with a diagram of each query showing which rows each clause lets through. From SELECT and WHERE to Project Planning: windows and CTEs.
Entries
- SELECT and WHEREThe starting queries on the CITY table: selecting columns, filtering with WHERE and chaining conditions, plus the first aggregations with COUNT, SUM and AVG.
- JOIN between CITY and COUNTRYJoining two tables on their key: an INNER JOIN between CITY and COUNTRY, with an interactive diagram to see which rows survive and which drop out.
- STATION: names and patterns (1)First exercises on the STATION table: DISTINCT, counts, and filtering city names by the vowel they start or end with using SUBSTRING, LEFT and RIGHT.
- STATION: names and patterns (2)A second round on STATION: negations with NOT IN, combining conditions on the first and last letter of each name, then summing, truncating and finding the maximum and minimum of LAT_N.
- STATION: geometry and mediansThe last three STATION exercises, where the query stops being filtering and becomes maths: Manhattan distance, Euclidean distance and the median.
- STUDENTS and EMPLOYEECompound ordering, aggregations with CASE and salary calculations: the exercises on the STUDENTS, GRADES and EMPLOYEE tables.
- Triangles and prime numbersThe pattern exercises: drawing triangles with loops in SQL Server and listing prime numbers on a single row, where SQL is used almost like an imperative language, and classifying triangles with CASE WHEN.
- OCCUPATIONS: formatting and pivotingShaping the result: building strings with CONCAT, LEFT and LOWER, and pivoting a table into four columns with ROW_NUMBER and CASE WHEN, because MySQL has no PIVOT.
- Hierarchies: binary tree and org chartWhen one row depends on another: classifying the nodes of a binary tree by joining the table to itself, and counting five levels of an org chart without duplicating records.
- Subqueries and HAVINGFiltering on something you only know after grouping: maximums over counts, values that appear only once, summing each group's maximum with a window function, and a correlated subquery that compares each row against its own group.
- Project Planning: windows and CTEsGrouping consecutive tasks into projects: LAG to see the previous date, a running sum with SUM OVER that numbers each project, and a CTE that groups them and sorts them by duration.
