Dashboard Aggregations

A dashboard is like a receipt tape: if you group at the wrong level, the numbers look neat and are still wrong.

Question: Dashboard Aggregations

Answer: Dashboard aggregations turn raw rows into summary numbers, such as daily revenue, orders, active users, or conversion rate. In SQL, you usually do this with GROUP BY, plus SUM, COUNT, AVG, and conditional logic like CASE. The biggest skill is choosing the right grain, which means the exact level you want to summarize at, such as per day or per customer.

Interview-Ready Answer: I build dashboard aggregations by first picking the right grain, then filtering to the correct fact rows, then grouping by the dashboard bucket, like day or product. After that I compute metrics with COUNT, SUM, and COUNT(DISTINCT ...), and I use COALESCE plus a calendar table when I need zero-filled days. I also watch out for double counting after joins, because one order can become many rows if I join to line items too early.

1. The mental model

Think of a dashboard query as a funnel: raw facts go in, a smaller set of grouped rows comes out. The query is not just ‘count things’; it is ‘count the right things at the right level’.

1. Choose the grain. Decide whether the dashboard should be by day, week, customer, product, or region. If the dashboard shows daily revenue, the grain is one row per day.
2. Filter the source rows early. Remove rows you do not want to count, such as canceled orders or test traffic. This keeps the metric honest before grouping happens.
3. Bucket time or categories. Turn timestamps into buckets like a date, month, or hour. This is what makes raw events become dashboard lines.
4. Aggregate the measures. Use SUM for money, COUNT for volume, AVG for averages, and COUNT(DISTINCT ...) for unique users.
5. Handle missing buckets. If a day has no sales, a plain GROUP BY will skip it. A dashboard usually wants zero instead of a missing line, so you join to a calendar table and use COALESCE.
6. Validate the result. Check that totals match a raw query for a small date range. Interviewers love this because it shows you think like someone who ships reliable analytics.

2. Under the hood

The database reads rows, keeps a running state for each group, and then finalizes the result. With a hash aggregate, the engine stores each group in memory using a hash table; with a sort aggregate, it sorts rows by the grouping key first. In both cases, the important idea is that the database only needs to remember one state per group, not one state per row.

1. The engine applies the WHERE filter to discard unwanted rows.
2. It computes grouping keys, such as the day or product.
3. For each row, it updates aggregate state, like a running sum or count.
4. When the group ends, it outputs the final value.
5. If you need zero-filled dates, a later LEFT JOIN to a calendar table restores the missing buckets.

Complexity is usually O(N) time for N input rows, because each row is read once, plus O(G) memory for G groups. A dashboard with 10 million events and 30 daily buckets is usually cheap; a dashboard grouped by day, product, country, and channel can explode into hundreds of thousands of groups and use much more memory. Distinct counts are the expensive part, because COUNT(DISTINCT ...) needs extra work to remember unique values.

3. When to use what

GROUP BY is the main tool for dashboards because dashboards usually want summary rows. Window functions, which are aggregate functions that do not remove rows, are useful when you want each raw row plus a total beside it. HAVING is for post-aggregation filters, such as ‘show only products with more than 100 orders’.

4. Important edge cases

• COUNT(*) vs COUNT(column): COUNT(*) counts rows, while COUNT(column) ignores NULL values. That difference matters if missing data should not be counted.
• Filter placement: A condition in WHERE removes rows before grouping, while HAVING removes groups after grouping. Putting the wrong condition in the wrong place changes the meaning of the metric.
• Join duplication: If you join orders to order items too early, one order becomes many rows and revenue is overstated. Aggregate at the right grain first, then join.
• Empty buckets: A plain aggregate query skips dates with no rows. Use a calendar table and LEFT JOIN if the dashboard must show zeroes.

Memory model: remember ‘filter, bucket, count, then fill gaps’. If you can explain that flow clearly, you can whiteboard most dashboard queries under pressure.

Real-world story

Imagine a checkout service for an e-commerce app. The business dashboard shows daily revenue, paid orders, and unique paying customers. The SQL query must exclude canceled and pending orders, group by day, and show a zero for days when the store was offline or simply had no orders.

Here is the classic bug: an engineer joins orders to order_items before summing revenue. One order with three items becomes three rows, so the revenue suddenly triples. The dashboard still looks ‘reasonable’ because the numbers are larger, but finance notices a mismatch against the ledger. In logs, you may see row counts that are much higher than order counts, and the user-facing symptom is a sales graph that spikes for the wrong reason.

This is why aggregation questions matter: dashboards are often the first place bad SQL becomes visible to executives, support, and finance. A tiny counting mistake can look like a company-wide growth miracle or a fake outage.

Follow-up & Tricky Questions:

• How do you include days with no data? Answer: build or reuse a calendar table and LEFT JOIN the aggregated facts onto it. Then wrap missing metrics with COALESCE(..., 0) so the dashboard shows zero instead of a blank.
• What is the difference between GROUP BY and a window function? GROUP BY collapses rows into summary rows, while a window function keeps the original rows and adds a metric beside them. Use windows when you need row detail plus a total.
• How do you calculate conversion rate safely? Keep numerator and denominator separate, for example paid orders divided by visits, and guard the denominator with NULLIF so you do not divide by zero.
• How do you avoid double counting after joins? Aggregate at the lowest unique grain first, or deduplicate before summing. If one order has many items, sum order totals from the orders table, not from the item table unless you intentionally re-aggregate items.
• When should you use HAVING? Use it when you want to filter groups after aggregation, such as ‘only days with revenue above 10,000’. If you put that condition in WHERE, the query means something different.
• Why is COUNT(DISTINCT ...) sometimes slow? Because the database must track uniqueness, which is more expensive than a simple count. On large dashboards, that can become the heaviest part of the query.
• Why is COUNT(status = 'paid') a trap? It is not a portable way to count only paid rows, because COUNT counts non-NULL expressions rather than true values. Use SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) for clear, portable SQL.
• Why does a LEFT JOIN sometimes behave like an inner join? If you put a filter on the right table in WHERE, you remove the null-extended rows and lose the zero buckets. Keep right-table filters inside the join condition or inside the pre-aggregation.

Common Mistakes:

• Grouping at the wrong grain - If the dashboard is daily, grouping by timestamp creates one row per event. Fix: bucket to the same level the dashboard needs, such as day or hour.
• Counting the wrong rows - Canceled, test, or duplicate rows sneak into totals. Fix: filter early with WHERE and use the correct status logic.
• Double counting after joins - One parent row becomes many child rows. Fix: aggregate before the one-to-many join, or aggregate on the parent key only.
• Forgetting zero buckets - Missing days disappear and the graph looks broken. Fix: join to a calendar table and apply COALESCE.

Memory Hook: Filter, bucket, count, fill. First remove bad rows, then choose the bucket, then compute the metric, then fill in the empty dates.

Cheat Sheet:

• GROUP BY collapses rows into dashboard rows.
• WHERE filters before aggregation; HAVING filters after.
• COUNT(*) counts rows; COUNT(column) ignores NULL.
• Use CASE for conditional metrics like paid revenue.
• Use a calendar table plus LEFT JOIN to show zero days.
• Watch for join duplication before summing.

Practice Tasks:

1. Write a query that shows daily revenue and daily paid order count for the last 7 days.
2. Add hourly buckets to the same query and compare GROUP BY results by hour versus by day.
3. Modify the dashboard so it shows only products with more than 100 paid orders using HAVING.