HackerRank SQL Challenge 2

Updated date: 2025-01-02

The following are the solutions to the SQL challenges on HackerRank.

Type of Triangle

SELECT
    CASE
        WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
        WHEN A = B AND B = C THEN 'Equilateral'
        WHEN A = B OR A = C OR B = C THEN 'Isosceles'
        ELSE 'Scalene'
    END AS triangle_type
FROM TRIANGLES;

The PADS

SELECT
    CONCAT(name, '(', LEFT(Occupation, 1), ')') AS formatted_name
FROM OCCUPATIONS
ORDER BY name ASC;

SELECT
    CONCAT('There are a total of ', COUNT(*), ' ', LOWER(Occupation), 's.') AS summary
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(*) ASC, Occupation ASC;

Occupations

MySQL built-in functions used here:

• MAX(): Returns the maximum value in a set of values.
• CASE: Evaluates a list of conditions and returns one of multiple possible result expressions.
• ROW_NUMBER: Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.
• PARTITION BY: PARTITION BY clause indicates how to divide the query rows into groups. The window function result for a given row is based on the rows of the partition that contains the row. If PARTITION BY is omitted, there is a single partition consisting of all query rows.

To solve this question, we first need to assign a row number to each row within the partition of the Occupation column. Here is the query to assign a row number to each row within the partition of the Occupation column:

Query:

SELECT
    name,
    Occupation,
    ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY name) AS row_num
FROM OCCUPATIONS;

Result:

Then, for each row_num, we need to select the name of the person with the corresponding Occupation value. To determine the occupation of each person, CASE statement can be used. However, CASE statement can return multiple values for each occupation and MySQL cannot handle this statement. Therefore, we can use the MAX() function to let MySQL know that the return value is a single value. (MIN() can work as well) You should repeat this process for each row_num value. At some point, some of the occupations will not have a corresponding name for a specific row_num value. In this case, the MAX() function will return NULL.

SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN name END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN name END) AS Professor,
    MAX(CASE WHEN Occupation = 'Singer' THEN name END) AS Singer,
    MAX(CASE WHEN Occupation = 'Actor' THEN name END) AS Actor
FROM (
    SELECT
        name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY name) AS row_num
    FROM OCCUPATIONS
) AS ranked
GROUP BY row_num
ORDER BY row_num;