Find Customers Whose Average Website Resource Usage Exceeds 50 Percent
IBMSoftware Engineer · Online Assessment
Last updated
Practice PostgreSQL aggregation with HAVING, customer-level average resource utilization, strict thresholds, two-decimal rounding, and ascending email output.
Write one read-only PostgreSQL query that reports customers whose average website resource usage exceeds 50 percent.
Schema
| Table | Column | Type | Meaning |
|---|---|---|---|
customers | id | INT | Primary key |
customers | email | VARCHAR(255) | Customer email |
site_metrics | customer_id | INT | Foreign key to customers.id |
site_metrics | cpu_usage | DECIMAL(5,2) | CPU usage percentage |
site_metrics | memory_usage | DECIMAL(5,2) | Memory usage percentage |
site_metrics | disk_usage | DECIMAL(5,2) | Disk usage percentage |
Task
- Calculate each customer's average CPU, memory, and disk usage across all of that customer's site-metric rows. Return one row per qualifying customer, even if customers share an email address.
- Include a customer if at least one of the three averages, before rounding, is strictly greater than 50. Apply this condition to the aggregated customer results with
HAVING; do not filter individual metric rows by resource usage before averaging. - Return the columns
email,average_cpu_usage,average_memory_usage, andaverage_disk_usage, in that order. Round each displayed average to two decimal places. - Sort rows by
emailin ascending order. The relative order of rows with the same email is unspecified.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.