azhar.khan

HackerRank certification guide

HackerRank SQL (Intermediate) Certification Solutions: Every Question Explained

Last checked 7 min read

The HackerRank SQL (Intermediate) test gives you two SQL questions and 35 minutes. Both questions run on the same small sales database: countries, cities, customers, invoices, products and the agents who talk to customers. Five questions keep coming up, and this page has all five with a working MySQL query for each.

I took this test on 3 June 2022 and passed. You can see my certificate here. I got Invoices Per Country and Product Sales Per City. The other three questions come from people who wrote up their own attempts on GitHub and YouTube, and I have tested every query below on MySQL 8.0 with sample data.

Read the explanation, not just the query. The hidden test cases punish small things like a missing tie-breaker or the wrong number of decimals, and you can only spot those if you know why the query is written the way it is.

What the test looks like

  • Time: 35 minutes.
  • Questions: 2, both “write a query” questions. HackerRank’s page says the test covers complex joins, and that is a fair summary: every question needs at least one join, and most need GROUP BY with HAVING or a subquery.
  • Language: you pick the database from a dropdown in the editor. MySQL is what most people use, and all the queries here are MySQL. People have also passed using MS SQL Server and Oracle.
  • Checking: you can run your query against a sample test case before you submit. The final result also depends on hidden test cases with different data.

Which two questions you get varies. People have reported every kind of pair: Invoices Per Country with Product Sales Per City (my attempt), Business Expansion with Customer Spending, Products Without Sales with Business Expansion, and others. Prepare all five.

The tables

All the questions use parts of this one schema. Each question only shows you the tables it needs, but the names and columns are the same everywhere.

Table Main columns Links to
country id, country_name
city id, city_name, postal_code, country_id country.id
customer id, customer_name, city_id, customer_address, contact_person, email, phone, is_active city.id
invoice id, invoice_number, customer_id, user_account_id, total_price decimal(8,2) customer.id
invoice_item invoice_id, product_id, quantity, price, line_total_price decimal(8,2) invoice.id, product.id
product id, sku, product_name, product_description, current_price, quantity_in_stock, is_active
user_account id, first_name, last_name, user_name, email, phone, is_active
contact id, user_account_id, customer_id, contact_type_id, initiated_by_customer, initiated_by_user user_account.id, customer.id

The chain to remember: country, then city, then customer, then invoice, then invoice_item, then product. Most questions walk part of that chain with joins.

How to prepare

  • Be comfortable with joins across 3 to 5 tables. Write the join chain above from memory once or twice.
  • Know GROUP BY with HAVING. Three of the five questions filter on an aggregate (a count or a sum compared with something).
  • Know scalar subqueries. “Compare with the average of all invoices” is just (SELECT AVG(total_price) FROM invoice) inside a HAVING.
  • Know how to find rows with no match. LEFT JOIN ... WHERE ... IS NULL or NOT EXISTS.
  • Practise output formatting. Rounding to 2 places, showing 6 decimal places, and sorting with tie-breakers. This is where most failed attempts go wrong.
  • Solve a few medium SQL problems on HackerRank’s practice track to get used to the editor and the way it shows output.

If you have not done the basic test yet, my HackerRank SQL (Basic) guide covers that one.

Invoices Per Country

Find the countries whose average invoice is higher than the average of all invoices. For each one, show the country name, the number of invoices and the average invoice. You walk from invoice to customer to city to country, group by country, and compare in HAVING.

invoices-per-country.sqlMySQL
SELECT
    co.country_name,
    COUNT(i.id) AS invoice_count,
    ROUND(AVG(i.total_price), 6) AS average_invoice
FROM country AS co
JOIN city AS ci ON ci.country_id = co.id
JOIN customer AS cu ON cu.city_id = ci.id
JOIN invoice AS i ON i.customer_id = cu.id
GROUP BY co.country_name
HAVING AVG(i.total_price) > (SELECT AVG(total_price) FROM invoice);

Full explanation →

Product Sales Per City

For every city and product, add up what that city’s customers spent on that product, rounded to 2 decimal places. Sort by the total (highest first), then city name, then product name. It is a five-table join, and the tie-breakers in the ORDER BY are what the hidden tests check.

product-sales-per-city.sqlMySQL
SELECT
    ci.city_name,
    pr.product_name,
    ROUND(SUM(ii.line_total_price), 2) AS total_spent
FROM city AS ci
JOIN customer AS cu ON cu.city_id = ci.id
JOIN invoice AS i ON i.customer_id = cu.id
JOIN invoice_item AS ii ON ii.invoice_id = i.id
JOIN product AS pr ON pr.id = ii.product_id
GROUP BY ci.city_name, pr.product_name
ORDER BY total_spent DESC, ci.city_name ASC, pr.product_name ASC;

Full explanation →

Business Expansion

Find every agent and customer pair that has been in contact more than once. Show the agent’s id and name, the customer’s id and name, and the number of contacts, sorted by the agent’s id. Group by both ids and keep groups with COUNT(*) > 1.

business-expansion.sqlMySQL
SELECT
    ua.id AS user_account_id,
    ua.first_name,
    ua.last_name,
    cu.id AS customer_id,
    cu.customer_name,
    COUNT(*) AS contact_count
FROM contact AS c
JOIN user_account AS ua ON ua.id = c.user_account_id
JOIN customer AS cu ON cu.id = c.customer_id
GROUP BY ua.id, ua.first_name, ua.last_name, cu.id, cu.customer_name
HAVING COUNT(*) > 1
ORDER BY ua.id ASC, cu.id ASC;

Full explanation →

Customer Spending

List the customers whose total spend is 25% or less of the average invoice, with the amount shown to 6 decimal places, highest first. The logic is easy. The trap is the format: ROUND(SUM(...), 6) still prints 2 decimals on MySQL 8, so cast to DECIMAL(12, 6) instead.

customer-spending.sqlMySQL
SELECT
    cu.customer_name,
    CAST(SUM(i.total_price) AS DECIMAL(12, 6)) AS amount_spent
FROM customer AS cu
JOIN invoice AS i ON i.customer_id = cu.id
GROUP BY cu.id, cu.customer_name
HAVING SUM(i.total_price) <= 0.25 * (SELECT AVG(total_price) FROM invoice)
ORDER BY amount_spent DESC;

Full explanation →

Products Without Sales

Find the products that never appear on any invoice line. Show the SKU and product name, sorted by SKU. A LEFT JOIN with an IS NULL check does it, and unlike the popular NOT IN answer it does not break if invoice_item has a NULL product id.

products-without-sales.sqlMySQL
SELECT
    p.sku,
    p.product_name
FROM product AS p
LEFT JOIN invoice_item AS ii ON ii.product_id = p.id
WHERE ii.product_id IS NULL
ORDER BY p.sku ASC;

Full explanation →

Tips for test day

  • Read the output columns and their order first. Write the SELECT list straight from the question before you think about joins.
  • Look for the words “rounded”, “decimal places” and “order by”. They tell you exactly what the hidden tests compare.
  • Run against the sample case early. A syntax error costs you nothing at minute 5 and a lot at minute 34.
  • End the query with a semicolon. The editor’s starter comment asks for one.
  • Never hard-code values from the sample output. The hidden data is different.
  • Do not paste a query from the internet without reading it. One query for Invoices Per Country that is copied across many GitHub repos (my own old one included) does not even run, because it has spaces in column names.
  • Split the time. 35 minutes for two queries is enough if you do not get stuck. If one is not working after 15 minutes, move to the other and come back.

FAQ

How many questions are there, and how long is the test?

Two questions in 35 minutes, according to HackerRank’s page for the test. The two come from the pool on this page.

Is there a sixth question called Invoice Spending?

One GitHub repo from 2023 lists an “Invoice Spending” question, and a couple of other repos copy the same file. It looks like a variant of Customer Spending that compares each customer’s average invoice (instead of their total) with a quarter of the overall average. I could not find the problem statement or a second independent report, so I have not given it its own page. If you get it, the Customer Spending query with AVG in place of SUM is the place to start, and read the output format carefully.

Can I write the queries in something other than MySQL?

Yes. The editor has a language dropdown, and people report passing with MS SQL Server and Oracle. Functions like ROUND, CAST and date handling differ between them, so stick to the one you know best. Everything on this page is MySQL.

What happens if I fail?

HackerRank’s help pages say you can retake a certification test 30 days after your previous attempt. A few tests allow only one attempt, and SQL (Intermediate) is not on that list.

When do I get the certificate?

HackerRank says certificates usually show up within a few minutes of passing, and in some cases it can take up to 24 hours.