Metrics collection in HLD - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Metrics Collection Purpose

What is the primary purpose of collecting metrics in a database system?

A To monitor system performance and detect anomalies

B To store user data permanently

C To create database backups automatically

D To encrypt sensitive information

💡 Hint

Think about why you would want to watch how the database behaves over time.

Metrics collection helps track performance and identify issues early, ensuring smooth operation.

❓ query_result

intermediate

Interpreting a Metrics Query Result

Given a metrics table with columns metric_name, value, and timestamp, what will this query return?

SELECT metric_name, AVG(value) FROM metrics WHERE timestamp > NOW() - INTERVAL '1 hour' GROUP BY metric_name;

A All metric names without any values

B Sum of all metric values in the last hour

C The latest value of each metric regardless of time

D Average values of each metric collected in the last hour

💡 Hint

Look at the AVG(value) and the time filter in the WHERE clause.

The query calculates the average value for each metric recorded within the last hour.

🧠 Conceptual

advanced

Identifying Syntax Error in Metrics Collection Query

Which option contains a syntax error in this metrics collection SQL query?

SELECT metric_name, COUNT(*) FROM metrics GROUP BY metric_name HAVING COUNT(*) > 10;

A SELECT metric_name, COUNT(*) FROM metrics WHERE COUNT(*) > 10 GROUP BY metric_name;

B SELECT metric_name, COUNT(*) FROM metrics GROUP BY metric_name HAVING COUNT(*) < 5;

C SELECT metric_name, COUNT(*) FROM metrics GROUP BY metric_name;

D SELECT metric_name, COUNT(*) FROM metrics GROUP BY metric_name HAVING COUNT(*) > 10;

💡 Hint

Remember that WHERE cannot filter on aggregate functions like COUNT().

Option A incorrectly uses WHERE COUNT(*) > 10, which is invalid SQL syntax because WHERE cannot filter aggregates.

❓ optimization

advanced

Optimizing Metrics Query Performance

You have a large metrics table with millions of rows. Which index would best improve the performance of this query?

SELECT metric_name, AVG(value) FROM metrics WHERE timestamp > NOW() - INTERVAL '1 day' GROUP BY metric_name;

A Create an index on <code>(value)</code>

B Create an index on <code>(timestamp, metric_name)</code>

C Create an index on <code>(metric_name)</code> only

D Create an index on <code>(timestamp)</code> only

💡 Hint

Think about which columns are used in filtering and grouping.

Indexing on (timestamp, metric_name) helps quickly filter by time and group by metric name efficiently.

🔍 Analysis

expert

Debugging Missing Metrics Data

A metrics collection system shows no data for the last hour, but older data exists. Which issue is most likely causing this?

A The metrics query uses <code>WHERE timestamp &lt; NOW() - INTERVAL '1 hour'</code> instead of <code>&gt;</code>

B The database table is full and rejecting new inserts

C The system clock is incorrect, causing timestamps to be in the future

D The metrics values are all zero, so they don't appear in results

💡 Hint

Consider how timestamps affect filtering recent data.

If the system clock is wrong and timestamps are in the future, queries filtering for recent data won't find any matching rows.