I encountered this SQL question in an IBM Coding Assessment and wanted to share it here for others preparing for IBM coding assessments, SQL interviews, and technical rounds.

The question required writing a PostgreSQL query to identify customers whose websites have high average resource usage.

Problem Statement

Create a report showing customers whose websites use more than 50% of any resource (CPU, memory, or disk) for a web hosting provider.

The report should provide a detailed overview of their average resource consumption across all their sites.

The result should contain the following columns:

• email — the email address of the customer
• average_cpu_usage — the average CPU usage across all sites for that customer, rounded to 2 decimal places
• average_memory_usage — the average memory usage across all sites for that customer, rounded to 2 decimal places
• average_disk_usage — the average disk usage across all sites for that customer, rounded to 2 decimal places

The results should be sorted in ascending order by email.

Important Condition

Only include customers for whom at least one average resource usage is greater than 50%.

In other words:

    average_cpu_usage > 50
    OR
    average_memory_usage > 50
    OR
    average_disk_usage > 50

Database Schema

customers

site_metrics

Key Observation

The filtering is not based on individual website records.

We first need to calculate the average CPU, memory, and disk usage for each customer across all their records in site_metrics.

Only after calculating these averages can we determine whether a customer satisfies the > 50% condition.

Therefore, the main SQL concepts required are:

• JOIN
• GROUP BY
• AVG()
• ROUND()
• HAVING
• ORDER BY

The most important point is using HAVING instead of WHERE, because the condition is based on aggregate values.

Approach

1. Join the tables

Join customers and site_metrics using:

    c.id = sm.customer_id

2. Group records by customer

Use:

    GROUP BY c.id, c.email

This ensures that the averages are calculated separately for each customer.

3. Calculate the averages

For each customer:

    ROUND(AVG(sm.cpu_usage), 2)
    ROUND(AVG(sm.memory_usage), 2)
    ROUND(AVG(sm.disk_usage), 2)

4. Filter customers

Use HAVING because we are filtering based on aggregate values:

    HAVING
        AVG(sm.cpu_usage) > 50
        OR AVG(sm.memory_usage) > 50
        OR AVG(sm.disk_usage) > 50

5. Sort the result

Finally:

    ORDER BY c.email ASC

PostgreSQL Solution

    SELECT
        c.email,
        ROUND(AVG(sm.cpu_usage), 2) AS average_cpu_usage,
        ROUND(AVG(sm.memory_usage), 2) AS average_memory_usage,
        ROUND(AVG(sm.disk_usage), 2) AS average_disk_usage
    FROM customers c
    JOIN site_metrics sm
        ON c.id = sm.customer_id
    GROUP BY
        c.id,
        c.email
    HAVING
        AVG(sm.cpu_usage) > 50
        OR AVG(sm.memory_usage) > 50
        OR AVG(sm.disk_usage) > 50
    ORDER BY
        c.email ASC;

Why HAVING?

This is probably the most important SQL concept tested in this question.

WHERE is applied before aggregation, whereas HAVING is applied after GROUP BY and aggregation.

Since we need to check:

    AVG(cpu_usage) > 50

we cannot use WHERE for this condition.

Instead:

    HAVING AVG(cpu_usage) > 50

is the correct approach.

Complexity

Let N be the number of rows in site_metrics and C be the number of distinct customers.

• Joining and aggregation: approximately O(N) depending on the database execution plan.
• Sorting the resulting customers by email: O(C log C).

Overall:

    Time Complexity: O(N + C log C)

The additional query-level space depends on the PostgreSQL execution plan and is managed internally by the database engine.

Concepts Tested

This IBM assessment question tests:

• SQL JOIN
• GROUP BY
• Aggregate functions
• AVG()
• ROUND()
• HAVING
• ORDER BY
• Filtering aggregated results
• PostgreSQL syntax

This question was asked in an IBM Coding Assessment.

Sharing it for anyone preparing for IBM assessments, SQL coding rounds, and database interview questions.

If anyone got the same question or has a cleaner PostgreSQL solution, feel free to share it.