Calculate Compressed Mean

Calculate Compressed Mean

What is this problem about?

The Calculate Compressed Mean interview question is a SQL/Database task centered on data aggregation. You are given a table where rows represent "compressed" data—for example, a row might state that there were 5 orders of 10 items each. Your goal is to calculate the overall average (mean) of the items across all orders. This Calculate Compressed Mean coding problem tests your ability to handle frequency-weighted averages in a relational database.

Why is this asked in interviews?

Google and other data-driven companies use this to evaluate a candidate's proficiency with SQL aggregate functions. It checks if you understand how to perform arithmetic within a SELECT statement and how to correctly use SUM() to handle weighted values. It’s a practical test of data manipulation skills often needed in analytics and reporting.

Algorithmic pattern used

This follows the Database interview pattern. The "Mean" is calculated as the (Total Sum of Items) / (Total Number of Orders).

1. Calculate the total sum of items by multiplying each item count by its frequency (order count).
2. Calculate the total number of orders by summing the frequency column.
3. Divide the two results and round to the required decimal places.

Example explanation

Suppose you have a table Orders:

• Items: 10, Frequency: 5
• Items: 20, Frequency: 2

1. Total Items = (10 * 5) + (20 * 2) = 50 + 40 = 90.
2. Total Orders = 5 + 2 = 7.
3. Mean = 90 / 7 approx 12.86. The SQL query would use SUM(items * frequency) / SUM(frequency) to get this result.

Common mistakes candidates make

• Average of the Items column: Simply using AVG(items), which treats every frequency as 1. This ignores the "compressed" nature of the data.
• Rounding errors: Forgetting to use ROUND(..., 2) if the problem specifies a certain precision.
• Integer division: Not casting to a decimal/float before division in databases like PostgreSQL or SQL Server, which might truncate the result.

Interview preparation tip

Always look for "frequency" or "count" columns in database problems. If they exist, your aggregate calculations (like mean or median) must be weighted by those values. Practice using SUM(colA * colB) for weighted totals.