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.

Practice

1. What is the main purpose of using aggregate functions like COUNT, SUM, and AVG in metrics collection?

easy

A. To create new tables for storing data

B. To change the structure of the database tables

C. To calculate totals, averages, and counts from data

D. To delete unwanted data from the database

Solution

1. Step 1: Understand aggregate functions

   Aggregate functions like COUNT, SUM, and AVG are used to calculate summary values from multiple rows of data.

2. Step 2: Identify their purpose in metrics

   These functions help get totals, averages, and counts which are useful for metrics and reports.

3. Final Answer:

   To calculate totals, averages, and counts from data -> Option C

4. Quick Check:

   Aggregate functions = summary numbers [OK]

Hint: Aggregate functions summarize data into numbers fast [OK]

Common Mistakes:

• Thinking aggregate functions modify data structure
• Confusing aggregate functions with data deletion
• Believing aggregate functions create tables

2. Which of the following SQL queries correctly counts the number of orders in a table named Orders?

easy

A. SELECT COUNT(Orders) FROM *;

B. SELECT COUNT FROM Orders;

C. SELECT SUM(*) FROM Orders;

D. SELECT COUNT(*) FROM Orders;

Solution

1. Step 1: Recall correct COUNT syntax

   The correct syntax to count rows is COUNT(*) inside SELECT.

2. Step 2: Check each option

   SELECT COUNT(*) FROM Orders; uses COUNT(*) correctly. Others have syntax errors or wrong functions.

3. Final Answer:

   SELECT COUNT(*) FROM Orders; -> Option D

4. Quick Check:

   COUNT(*) counts rows correctly [OK]

Hint: Use COUNT(*) to count all rows in a table [OK]

Common Mistakes:

• Omitting parentheses after COUNT
• Using COUNT without specifying columns or *
• Confusing SUM with COUNT

3. Given the table Sales with columns amount and region, what will this query return?

SELECT AVG(amount) FROM Sales WHERE region = 'East';

medium

A. The average sales amount for the East region

B. The total sales amount for all regions

C. The count of sales records in the East region

D. An error because AVG cannot be used with WHERE

Solution

1. Step 1: Understand the WHERE clause effect

   The WHERE clause filters rows to only those where region is 'East'.

2. Step 2: Understand AVG function

   AVG calculates the average of the filtered amount values.

3. Final Answer:

   The average sales amount for the East region -> Option A

4. Quick Check:

   AVG with WHERE filters = average for filtered data [OK]

Hint: WHERE filters rows before aggregate functions run [OK]

Common Mistakes:

• Thinking AVG returns total sum
• Confusing COUNT with AVG
• Believing WHERE cannot be used with aggregates

4. Identify the error in this SQL query that tries to get the total sales for the 'West' region:

SELECT SUM(amount) FROM Sales WHERE region == 'West';

medium

A. Using double equals (==) instead of single equals (=) in WHERE clause

B. SUM cannot be used with WHERE clause

C. Missing GROUP BY clause for aggregation

D. amount column cannot be summed

Solution

1. Step 1: Check WHERE clause syntax

   SQL uses single equals (=) for comparison, not double equals (==).

2. Step 2: Confirm aggregate and WHERE usage

   SUM works with WHERE filtering; GROUP BY is not needed here.

3. Final Answer:

   Using double equals (==) instead of single equals (=) in WHERE clause -> Option A

4. Quick Check:

   Use = for comparisons in SQL WHERE [OK]

Hint: Use single = for comparisons in SQL WHERE [OK]

Common Mistakes:

• Using == instead of = in WHERE
• Thinking GROUP BY is always required with SUM
• Assuming SUM cannot be used with WHERE

5. You want to find the average order amount per customer but only for customers who placed more than 5 orders. Which SQL approach correctly achieves this?

hard

A. Use WHERE COUNT(*) > 5 and SELECT AVG(amount) without GROUP BY

B. Use GROUP BY customer_id with HAVING COUNT(*) > 5 and SELECT AVG(amount)

C. Use WHERE AVG(amount) > 5 and GROUP BY customer_id

D. Use HAVING AVG(amount) > 5 without GROUP BY

Solution

1. Step 1: Understand grouping by customer

   To get average per customer, group rows by customer_id.

2. Step 2: Filter groups with more than 5 orders

   Use HAVING with COUNT(*) > 5 to keep only customers with more than 5 orders.

3. Step 3: Select average order amount

   Use AVG(amount) in SELECT to get average per filtered customer group.

4. Final Answer:

   Use GROUP BY customer_id with HAVING COUNT(*) > 5 and SELECT AVG(amount) -> Option B

5. Quick Check:

   GROUP BY + HAVING filters groups before AVG [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE to filter aggregated counts
• Forgetting GROUP BY when using aggregates per group
• Using HAVING without GROUP BY