IBM Coding Assesment Question | Customer Resource Usage Analysis | PostgreSQL

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.

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
id – INT – Primary key, identifier of the customer
email – VARCHAR(255) – Email address of the customer

site_metrics
customer_id – INT – Foreign key referencing customers.id
cpu_usage – DECIMAL(5,2) – CPU usage percentage
memory_usage – DECIMAL(5,2) – Memory usage percentage
disk_usage – DECIMAL(5,2) – Disk usage percentage

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 using c.id = sm.customer_id
2. Group records by customer (c.id, c.email)
3. Calculate the averages with ROUND(AVG(...), 2)
4. Filter customers with HAVING AVG(...) > 50 for any resource
5. Sort the result by email ascending

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?

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.

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)
• Sorting the resulting customers by email: O(C log C)

Overall time complexity: O(N + C log C).

Concepts Tested

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