Customer Resource Usage Analysis
IBMFULLTIMEOAProblem statement
A web-hosting provider tracks resource usage for every customer's sites. Using the customers and site_metrics tables, create a report with email, average_cpu_usage, average_memory_usage, and average_disk_usage.
- Compute each average across all metric rows belonging to that customer.
- Include a customer only when at least one unrounded average is strictly greater than
50. - Round each returned average to two decimal places.
- Order rows by
emailascending.
For this exercise, customer IDs and emails are unique, every metric row references a customer, and all values are non-null. Customers without metric rows are omitted. Calculate with exact decimals, apply the threshold before rounding, and round halfway values away from zero.
In MySQL and PostgreSQL, write a query over the supplied tables. In Pandas, implement customer_resource_report(customers, site_metrics) and return a DataFrame with the four result columns in the stated order.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
customers
One row per web-hosting customer.
| Column | Type | Nullable | Description |
|---|---|---|---|
| idPK | Integer | No | Unique customer identifier. |
| Text | No | Unique customer email address. |
site_metrics
One resource-usage observation for one customer site.
| Column | Type | Nullable | Description |
|---|---|---|---|
| customer_id | Integer | No | Customer owning the observed site. |
| cpu_usage | Decimal | No | CPU usage percentage. |
| memory_usage | Decimal | No | Memory usage percentage. |
| disk_usage | Decimal | No | Disk usage percentage. |
Foreign key: customer_id → customers(id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| Text | No | Customer email. | |
| average_cpu_usage | Decimal | No | Average CPU usage rounded to two decimals. |
| average_memory_usage | Decimal | No | Average memory usage rounded to two decimals. |
| average_disk_usage | Decimal | No | Average disk usage rounded to two decimals. |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
0 <= customers.length <= 10000 <= site_metrics.length <= 100000- Customer IDs are signed 32-bit integers and are unique.
- Email values are nonempty and unique.
- CPU, memory, and disk usage are exact decimals from
0through100, with at most two fractional digits. - Every metric row references an existing customer, and no table cell is null.