Katkat
Back

Write February Resource Usage Query

Akuna CapitalData Engineer · Online Assessment
MediumData EngineerOnline AssessmentSoftware Engineering Fundamentals

Last updated

This question tests the ability to join multiple tables, handle date filtering with range conditions, and perform grouped aggregations to calculate average metrics across different resource categories.

Problem Statement

You are working with two tables in a resource management database: applications and resource_usage.

Tables

  • applications (app_id (INT), app_name (VARCHAR))
  • resource_usage (usage_id (INT), app_id (INT), metric_type (VARCHAR), usage_value (FLOAT), recorded_at (TIMESTAMP))

Task

Write a SQL query to calculate the average usage_value for each app_name during the month of February 2024. Your results should only include applications that recorded at least one metric during this period.

Expected Output

Return a result set with columns app_name and avg_usage, sorted by avg_usage in descending order.

Example

Input: applications table:

app_idapp_name
1Trading
2Logging

resource_usage table:

usage_idapp_idmetric_typeusage_valuerecorded_at
1011CPU45.02024-02-05 10:00:00
1021CPU55.02024-02-10 12:00:00
1032RAM80.02024-02-28 09:00:00
1041CPU10.02024-03-01 08:00:00

Output:

app_nameavg_usage
Logging80.0
Trading50.0

Note: The record on 2024-03-01 is ignored as it falls outside of February.

Constraints

  • Use standard SQL syntax (ANSI SQL).
  • recorded_at will be in YYYY-MM-DD HH:MM:SS format.

Hints

Hint 1

Use a WHERE clause with comparison operators or functions like EXTRACT to filter for February 2024.

Hint 2

Join the tables on the app_id column before performing the aggregation.

Hint 3

Remember to use the GROUP BY clause on the app_name column to calculate the average per application.

Follow-up questions

  • How would your query change if you needed to calculate the average usage per metric_type per application?
  • If the resource_usage table contained millions of rows, what index would you create to improve the performance of this specific query?
  • How would you modify the query to return applications that had zero usage in February (i.e., keep them in the list with a NULL or 0 value)?