Objective

Write a SQL query to calculate the percentage of calls that cannot be categorised.

Table Schema:

• callers

Task Requirements:

• A call is uncategorised if call_category is 'n/a' or NULL
• Calculate the percentage of uncategorised calls out of all calls
• Round the result to 1 decimal place
  
  

Output columns: uncategorised_pct

SELECT
    ROUND(
        SUM(CASE
            WHEN call_category = 'n/a' OR call_category IS NULL THEN 1
            ELSE 0
        END)::DECIMAL /
        COUNT(*) * 100,
        1
    ) AS uncategorised_pct
FROM callers;

Explanation

Step 1: Identifying Uncategorised Calls

CASE
    WHEN call_category = 'n/a' OR call_category IS NULL THEN 1
    ELSE 0
END

The CASE expression checks two conditions for uncategorised calls: the value 'n/a' (a string explicitly marking no category) and NULL (missing data). Both are treated as uncategorised and return 1. All other categories return 0. Note that NULL requires IS NULL for comparison since = NULL always evaluates to unknown in SQL.

Step 2: Counting Uncategorised Calls

SUM(CASE ... END)

Wrapping the CASE expression in SUM adds up all the 1s, giving the total number of uncategorised calls. This is equivalent to counting rows where the condition is true, but avoids needing a subquery or FILTER clause.

Step 3: Calculating the Percentage

SUM(...)::DECIMAL / COUNT(*) * 100

COUNT(*) returns the total number of calls regardless of category. Dividing the uncategorised count by the total gives the ratio. The ::DECIMAL cast prevents integer division from truncating the result to 0. Multiplying by 100 converts the decimal ratio to a percentage.

Step 4: Rounding the Result

ROUND(..., 1)

ROUND with 1 as the second parameter ensures the percentage is displayed to exactly 1 decimal place.