HackerRank SQL Certification — Solutions Series

📦 Full source code: github.com/khaaleoo/hackerrank-sql-certification

This series compiles solutions to problems across the three levels of HackerRank's SQL certification: Basic, Intermediate, Advanced. Each entry includes the problem statement, schema, SQL solution, and an explanation of the approach.

---

🟢 Basic

1. Student Advisor

Description

A university has started a student-advisor plan which assigns a professor as an advisor to each student for academic guidance. Write a query to find the roll number and names of students who either have a male advisor with a salary of more than 15,000 or a female advisor with a salary of more than 20,000.

Schema

• student_information: roll_number (PK), name, advisor (FK)
• faculty_information: employee_ID (PK), gender, salary

Solution (PostgreSQL)

SELECT 
    s.roll_number, 
    s.name
FROM 
    student_information s
JOIN 
    faculty_information f ON s.advisor = f.employee_id
WHERE 
    (f.gender = 'M' AND f.salary > 15000)
    OR 
    (f.gender = 'F' AND f.salary > 20000);

Explanation

• JOIN: match each student's advisor ID with the faculty's employee_id.
• WHERE: apply the conditional logic using parentheses to isolate the two distinct requirements combined with an OR operator — male advisor making more than 15,000, or female advisor making more than 20,000.

---

2. Student Analysis

Description

A school recently conducted its annual examination and wishes to know the list of academically low performing students to organize extra classes for them. Write a query to return the roll number and names of students who have a total of less than 100 marks including all 3 subjects.

Schema

• student_information: roll_number (PK), name
• examination_marks: roll_number (PK), subject_one, subject_two, subject_three

Solution (PostgreSQL)

SELECT 
    s.roll_number, 
    s.name
FROM 
    student_information s
JOIN 
    examination_marks e ON s.roll_number = e.roll_number
WHERE 
    (e.subject_one + e.subject_two + e.subject_three) < 100;

Explanation

• JOIN: match the records from both tables using the common identifier, roll_number.
• WHERE: calculate the sum of all 3 subjects and filter out only those students whose total marks are strictly less than 100.

---

🟡 Intermediate

3. Business Expansion

Description

As part of business expansion efforts at a company, your help is needed to find all pairs of customers and agents who have been in contact more than once. For each such pair, display the user id, first name, and last name, and the customer id, name, and the number of their contacts. Order the result by user id ascending.

Schema

• customer: id (PK), customer_name, city_id, ...
• user_account: id (PK), first_name, last_name, ...
• contact: id (PK), user_account_id (FK), customer_id (FK), ...

Solution (PostgreSQL)

SELECT 
    u.id AS user_id,
    u.first_name,
    u.last_name,
    c.id AS customer_id,
    c.customer_name,
    COUNT(co.id) AS number_of_contacts
FROM 
    contact co
JOIN 
    user_account u ON co.user_account_id = u.id
JOIN 
    customer c ON co.customer_id = c.id
GROUP BY 
    u.id, 
    u.first_name, 
    u.last_name, 
    c.id, 
    c.customer_name
HAVING 
    COUNT(co.id) > 1
ORDER BY 
    u.id ASC;

Explanation

• GROUP BY & HAVING: group by the unique identifiers and names of both the users and the customers to count individual occurrences of interactions, strictly filtering for pairs with COUNT(co.id) > 1.

---

4. Product Sales per City

Description

For each pair of city and product, return the names of the city and product, as well as the total amount spent on the product to 2 decimal places. Order the result by the amount spent from high to low then by city name and product name in ascending order. (Expected output is a space-separated flat string per row.)

Schema

Sequential JOIN through 5 tables: city -> customer -> invoice -> invoice_item -> product.

Solution (PostgreSQL)

SELECT 
    ci.city_name || ' ' || p.product_name || ' ' || TO_CHAR(SUM(ii.line_total_price), 'FM999999990.00') AS product_sales
FROM 
    city ci
JOIN 
    customer cu ON ci.id = cu.city_id
JOIN 
    invoice i ON cu.id = i.customer_id
JOIN 
    invoice_item ii ON i.id = ii.invoice_id
JOIN 
    product p ON ii.product_id = p.id
GROUP BY 
    ci.city_name,
    p.product_name
ORDER BY 
    SUM(ii.line_total_price) DESC,
    ci.city_name ASC,
    p.product_name ASC;

Explanation

• || ' ' ||: concatenates the strings together with a single blank space in between to match standard output expectations.
• TO_CHAR(..., 'FM999999990.00'): formats the total spent amount to exactly 2 decimal places and uses FM (Fill Mode) to strip away leading/trailing spaces.

---

🔴 Advanced

5. Crypto Market Algorithms Report

Description

A number of algorithms are used to mine cryptocurrencies. As part of a comparison, create a query to return a list of algorithms and their volumes for each quarter of the year 2020. The results should be sorted ascending by algorithm name. Q1 through Q4 contain the sums of transaction volumes precise to 6 places after the decimal.

Schema

• coins: code (PK), name, algorithm
• transactions: coin_code (FK), dt (VARCHAR), volume (DECIMAL)

Solution (PostgreSQL)

SELECT 
    c.algorithm,
    COALESCE(SUM(CASE WHEN t.dt >= '2020-01-01' AND t.dt < '2020-04-01' THEN t.volume END), 0) AS q1,
    COALESCE(SUM(CASE WHEN t.dt >= '2020-04-01' AND t.dt < '2020-07-01' THEN t.volume END), 0) AS q2,
    COALESCE(SUM(CASE WHEN t.dt >= '2020-07-01' AND t.dt < '2020-10-01' THEN t.volume END), 0) AS q3,
    COALESCE(SUM(CASE WHEN t.dt >= '2020-10-01' AND t.dt < '2021-01-01' THEN t.volume END), 0) AS q4
FROM 
    coins c
LEFT JOIN 
    transactions t ON c.code = t.coin_code
GROUP BY 
    c.algorithm
ORDER BY 
    c.algorithm ASC;

Explanation

• Conditional Aggregation: use CASE WHEN combined with SUM to isolate transaction volumes per quarter. COALESCE(..., 0) handles algorithms with no transactions in a given quarter.

---

6. Crypto Market Transactions Monitoring

Description

As part of a cryptocurrency trade monitoring platform, create a query to return a list of suspicious transactions. Suspicious transactions are defined as:

1. A series of two or more transactions occur at intervals of an hour or less.
2. They are from the same sender.
3. The sum of transactions in a sequence is 150 or greater.

Return the sender, sequence_start, sequence_end, transactions_count, and transactions_sum (to 6 decimal places).

Schema

• transactions: name, type, dt (VARCHAR), sender, recipient, amount

Solution (MySQL)

WITH converted_transactions AS (
    SELECT 
        sender,
        STR_TO_DATE(dt, '%Y-%m-%d %H:%i:%s') AS tx_time,
        amount
    FROM transactions
),
lagged_transactions AS (
    SELECT 
        sender,
        tx_time,
        amount,
        LAG(tx_time) OVER (PARTITION BY sender ORDER BY tx_time) AS prev_tx_time
    FROM converted_transactions
),
island_indicators AS (
    SELECT 
        sender,
        tx_time,
        amount,
        CASE 
            WHEN prev_tx_time IS NULL OR TIMESTAMPDIFF(SECOND, prev_tx_time, tx_time) > 3600 THEN 1 
            ELSE 0 
        END AS is_new_sequence
    FROM lagged_transactions
),
island_groups AS (
    SELECT 
        sender,
        tx_time,
        amount,
        SUM(is_new_sequence) OVER (PARTITION BY sender ORDER BY tx_time) AS sequence_id
    FROM island_indicators
)
SELECT 
    sender,
    MIN(tx_time) AS sequence_start,
    MAX(tx_time) AS sequence_end,
    COUNT(*) AS transactions_count,
    ROUND(SUM(amount), 6) AS transactions_sum
FROM island_groups
GROUP BY 
    sender, 
    sequence_id
HAVING 
    COUNT(*) >= 2 
    AND SUM(amount) >= 150
ORDER BY 
    sender ASC, 
    sequence_start ASC, 
    sequence_end ASC;

Explanation

Classic Gaps & Islands problem:

• STR_TO_DATE converts string dates to proper DATETIME.
• LAG gets the timestamp of the previous transaction by the same sender.
• TIMESTAMPDIFF(SECOND, ...) checks if the interval between adjacent transactions exceeds 3600 seconds (1 hour). If it does, a 1 is flagged.
• SUM(...) OVER(...) acts as a running total to assign a unique sequence_id to each valid cluster of transactions, allowing correct GROUP BY.