AkunaCapital·Software Engineer·Technical Phone Screen·Intermediate

IntermediatePrefer not to say

Feb 2024

Summary

Data engineer interview at Akuna Capital with a SQL-focused technical question. Pretty straightforward if you know your aggregations and set operations, but the ordering requirement at the end is easy to fumble under pressure.

Questions Asked (1)

Given two tables, cpu_metrics and memory_metrics, each with an application_id and usage_percentage column, write a query to find all applications where the average usage_percentage exceeds 50% during February 2024. Return a label indicating the resource type ('CPU' or 'Memory'), the application_id, and the rounded average. Combine both result sets and sort by application_id, then type.

Data ModelingAlgorithms & Data Structures

Author's notes

The UNION ALL part felt obvious but I second-guessed myself on whether to use UNION vs UNION ALL.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Write two separate SELECT statements, one for CPU and one for Memory, each filtering for February 2024, grouping by application_id, and using HAVING to keep only averages above 50. Then combine them with UNION ALL, and finally sort by application_id and resource type.

Pro tip: Mention that using UNION ALL instead of UNION avoids unnecessary deduplication overhead, which is important for large datasets. Also, clarify that the date filter should be applied before aggregation to reduce the number of rows processed.

1. Filter and aggregate each table

For each table, apply a WHERE clause to restrict to February 2024, then GROUP BY application_id and compute AVG(usage_percentage).

2. Apply HAVING condition

Use HAVING AVG(usage_percentage) > 50 to keep only groups where the average exceeds 50%.

3. Add resource type label and round

In each SELECT, include a literal column for the resource type ('CPU' or 'Memory') and round the average to the desired precision (e.g., 2 decimal places).

4. Combine results with UNION ALL

Use UNION ALL to merge the two result sets, ensuring column order and data types match.

5. Sort final output

Add an ORDER BY clause to sort by application_id and then by resource type (or type as specified).

Key Points to Mention

Use of WHERE clause to filter dates for February 2024 (e.g., date >= '2024-02-01' AND date < '2024-03-01').
GROUP BY application_id to compute average per application.
HAVING clause to filter groups with average > 50.
ROUND function to round the average (e.g., to 2 decimal places).
UNION ALL to combine results without removing duplicates.
ORDER BY application_id, type to sort the final result.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.