Design SQL for reporting

Reporting SQL is like building a scoreboard from raw game logs: if you count the same play twice, the final score still looks neat and still lies.

Question: Design SQL for reporting.

Answer: I’d design reporting SQL by first defining the grain, meaning what one row should represent, such as one day per country. Then I’d filter the base fact table early, join dimension tables by keys, aggregate with GROUP BY, and handle missing values with COALESCE. The big risks are double counting, wrong join filters, and slow full-table scans.

Interview-Ready Answer: I’d start by asking what one row in the report should represent, then build the query around that grain. I filter the fact table first, join dimensions by keys, aggregate with GROUP BY, and use COALESCE so empty groups still show as zero instead of null. I also validate the totals against the source data to catch double counting, and if the report is high traffic I’d consider partitioning or a materialized view to keep it fast.

Detailed Explanation: Reporting SQL is not just ‘write a SELECT’. It is a design problem: choose the right grain, join safely, aggregate correctly, and make it fast enough for users who refresh dashboards all day.

1) Start with the business question

Before writing SQL, translate the request into three things: the metric, the dimension, and the time window. For example: revenue by country for January. The metric is revenue, the dimension is country, and the grain could be one row per country for one month. If you do not lock the grain first, you can accidentally mix daily and monthly rows and get numbers that look fine but are wrong.

2) Under the hood: the safe reporting flow

1. Pick a fact table, such as orders or payments. A fact table is the table with events or transactions.
2. Filter early on selective columns like date and status. This reduces rows before joins and makes the query cheaper.
3. Join dimensions, such as customer or product, using keys instead of names. Keys are stable; names can change.
4. Aggregate to the report grain with SUM, COUNT, or AVG. This is where one row becomes one summary row.
5. Protect empty groups with COALESCE so a dashboard shows 0 instead of null.
6. Validate the result against a control total, such as total paid orders or invoice totals, to catch double counting.

The database optimizer may reorder some work behind the scenes, but writing the query in this order makes your intent clear and helps you reason about correctness.

3) Compare the main design options

A live query is easiest when the data is small or the question is ad hoc. A materialized view is a stored result set: the database computes it ahead of time and refreshes it later. A summary table is similar, but you usually build and update it yourself in ETL, which means more control and more maintenance.

4) Performance and complexity

For a plain grouped report, think roughly O(n) over the number of filtered rows, plus join cost. If you scan 50 million orders, the database must touch a lot of data even if the final output is only 20 rows. With date partitioning, a January report may scan only one partition instead of the full table; that can cut work from 50 million rows to around 4 million. A good index on (status, order_date) can help when the filter is selective, but on very large analytic tables, partition pruning and pre-aggregation usually matter more than a single index.

Also watch for expensive operators. COUNT(DISTINCT) is heavier than COUNT(*) because the engine has to remove duplicates. Wide joins can spill to disk if memory is too small, and that turns a two-second query into a 30-second one.

5) Important edge cases

• Wrong join filter: putting a filter from the right side of a LEFT JOIN in the WHERE clause can silently turn it into an inner join.
• Duplicate rows: if a dimension is not truly unique, one order may appear twice and inflate revenue.
• Null amounts: SUM ignores nulls, but AVG and empty groups can surprise you, so check defaults.
• Timezone shifts: a day report can move across midnight if the business uses UTC but users expect local time.
• Late data: events can arrive after the dashboard refresh, so finance often needs a backfill job.

Real-World Story: A checkout team at an e-commerce company needs a daily revenue dashboard by country. The report starts from a country list so finance can see zeros, even for countries with no sales yet. One day an engineer moves the payment-status filter into the WHERE clause after a LEFT JOIN; the zero-sale country disappears, and the total revenue suddenly looks 8% lower. The symptoms are subtle: the dashboard still loads, logs show fewer rows than yesterday, and the reconciliation job prints a mismatch like ‘report total does not equal ledger total’. Users do not see a crash, but they do see the wrong business answer, which is worse.

Follow-up & Tricky Questions:

• How would you make this report fast on 100 million rows? I would partition the fact table by date, filter on the partition key, and pre-aggregate common dashboard metrics. If the same report is queried all day, I’d strongly consider a materialized view or summary table so the dashboard stops rescanning raw data.
• How do you avoid double counting in joins? I would identify the true grain of each table and never join a one-to-many table unless I know how many rows it should add. If needed, I’d aggregate the many-side first or use a bridge table for many-to-many relationships.
• When would you use a materialized view instead of a live query? I’d use one when the report is reused often, freshness can be slightly delayed, and the logic is expensive to compute. For example, a finance dashboard refreshed every 15 minutes is a strong fit.
• How do you handle time zones in reporting? I would store timestamps in UTC, then convert to the business timezone at query time or in a controlled reporting layer. That keeps storage consistent while still letting the report line up with local business days.
• How do you validate that the report is correct? I compare a report total to a control total from the source fact table, check row counts, and spot-check a few known entities. If those do not match, I look first for join duplication and misplaced filters.

• Tricky: Why can a LEFT JOIN act like an inner join? Because a filter in the WHERE clause removes the null-extended rows that the LEFT JOIN created. If you need to preserve missing rows, keep the filter in the join condition or in a pre-filtered CTE.
• Tricky: What is the difference between COUNT(*) and COUNT(column)? COUNT(*) counts rows, including rows where every selected column is null from an outer join. COUNT(column) ignores null values, which is often what you want when counting matched facts.
• Tricky: Why is COUNT(DISTINCT) often expensive? The engine must remove duplicates before it can return the answer, which usually needs more memory or sorting. If the metric is important and repeated often, pre-aggregate or store a deduplicated summary instead.

Common Mistakes:

• Wrong grain: candidates mix daily and monthly data in one query. Correction: state the grain first, then write every join and aggregate to match it.
• Filtering after an outer join: this silently drops missing rows. Correction: filter the fact set before the join, or move the condition into the join itself.
• Double counting after joins: many-to-many joins inflate totals. Correction: aggregate one side first or use a bridge table with known cardinality.
• Ignoring nulls and zero groups: dashboards show blank cells instead of 0. Correction: use COALESCE and test empty cases explicitly.

Memory Hook: Think of reporting SQL like a LEGO tower: first choose the base block (grain), then snap each piece on once (join keys), and finally check the height against the ruler (control total). If the tower is built on the wrong base, every number above it is wrong even if it looks sturdy.

Cheat Sheet:

• Define the report grain before writing SQL.
• Filter the fact table early to shrink the work.
• Join dimensions by keys, not by names.
• Use GROUP BY for the final report row shape.
• Use COALESCE for zero-friendly dashboards.
• Validate totals against a control query.

Practice Tasks:

• Write a daily revenue report by country from a sales table.
• Add countries with zero sales so they still appear with 0 revenue.
• Optimize the same report for a 50 million row fact table using indexes, partitioning, or a materialized view.