Understanding Codility Tests

What is a Codility Test?

Codility tests are online assessments used by companies to gauge a candidate's programming skills. Instead of a whiteboard interview, you'll solve problems in a web-based code editor. The platform automatically runs your solution against a series of tests to see how well it works.

For SQL tasks, you're usually given a description of one or more database tables and a problem to solve. Your job is to write a single SQL query that returns the correct data. The environment is standardized, so you don't have to worry about setting up a database, just writing the query.

The goal is simple: write a single, effective SQL query that answers the question posed in the problem description.

How Your Code is Scored

Codility evaluates your SQL query on two main criteria: correctness and performance. Both are equally important for a high score.

Correctness

noun

Does your query produce the right results for all possible scenarios?

The system runs your query against a hidden set of test cases. These include typical examples, empty tables, and tricky edge cases (like duplicates or missing values). To get a full score for correctness, your query needs to pass every single test. A small mistake might cause it to fail on an edge case you didn't consider.

Think of it like a quality check. Your query might work for the simple example provided, but does it hold up under more complex or unexpected conditions?

Performance

noun

How efficiently does your query run on large datasets?

After checking for correctness, Codility tests your query against very large tables, sometimes with hundreds of thousands of rows. A query that works fine on a small table might be too slow on a big one. The platform measures the execution time. If it exceeds a certain limit, you'll lose points on performance.

This is why efficiency matters. Using the right types of joins and filtering data early are key to writing performant queries.

Criterion	What It Measures	How to Succeed
Correctness	Accuracy of results across various test cases.	Handle edge cases, nulls, and empty tables.
Performance	Speed of execution on large datasets.	Write efficient queries that scale well.

Common Problem Types and Best Practices

Most Codility SQL problems fall into a few categories. You'll often be asked to join tables, aggregate data, or filter for specific results. Sometimes you'll need to do all three.

Here are some best practices to keep in mind:

1. Read the Problem Carefully: Understand exactly what the question is asking for. Pay close attention to the column names in the expected output and the required sorting order.

It's easy to lose points by misreading a requirement. For example, if the problem asks for the id of employees who meet a condition, don't return their names. Double-check every detail.

2. Plan Your Query: Before writing any code, think through the logic. Which tables do you need to join? What conditions do you need in your WHERE clause? How should you group the data?

A common pitfall is diving straight into coding without a plan. You might write a query that works for the example but fails on edge cases. For instance, using a simple JOIN (INNER JOIN) might exclude records you need, when a LEFT JOIN is the correct choice to include rows from one table even if there's no match in the other.

3. Write Clean, Readable Code: While Codility doesn't grade style, readable code is easier to debug. Use clear aliases for your tables and format your query so that it's easy to follow.

When it comes to questions categorized under coding, the most prominent concept tested was writing SQL queries with emphasis on writing join statements.

For example, instead of cramming your query onto one line, break it up. This makes it much easier to spot mistakes.

-- Harder to read:
SELECT c.name, COUNT(o.id) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name HAVING COUNT(o.id) > 1;

-- Easier to read:
SELECT
    c.name,
    COUNT(o.id) AS order_count
FROM
    customers AS c
JOIN
    orders AS o ON c.id = o.customer_id
GROUP BY
    c.name
HAVING
    COUNT(o.id) > 1;

Following these simple guidelines will help you approach Codility SQL tests with more confidence. The key is to be methodical, thorough, and mindful of both correctness and performance.

Time for a quick review of the key terms.

Now, let's test your understanding of how Codility tests work.

Quiz Questions 1/5

In a Codility SQL test, what is the primary factor that determines your 'correctness' score?

Quiz Questions 2/5

Why might a SQL query that works perfectly on a small sample dataset lose points for 'performance' in Codility?

With these concepts in mind, you're better prepared to tackle a Codility test and showcase your SQL skills.

Mastering the Tempus AI SQL Programming Test