UltraHard Scenario BasedSQL#1857 min readJul 11, 2026
Design SQL for reporting
practice
Practice modeTest yourself instead of reading straight through
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.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3EReporting%20SQL%3C%2Ftext%3E%3C%2Fsvg%3E)
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
- Pick a fact table, such as
ordersorpayments. A fact table is the table with events or transactions. - Filter early on selective columns like date and status. This reduces rows before joins and makes the query cheaper.
- Join dimensions, such as customer or product, using keys instead of names. Keys are stable; names can change.
- Aggregate to the report grain with
SUM,COUNT, orAVG. This is where one row becomes one summary row. - Protect empty groups with
COALESCEso a dashboard shows 0 instead of null. - 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
| Approach | Best for | Trade-off |
|---|---|---|
| Live query | Small data | Fresh, but slower |
| Materialized view | Dashboards | Fast, slightly stale |
| Summary table | Heavy reporting | Extra ETL work |
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 JOINin theWHEREclause 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:
SUMignores nulls, butAVGand 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.
SQL
-- A small, runnable reporting example that shows the right pattern:
-- 1) define the grain,
-- 2) filter the fact table early,
-- 3) join dimensions safely,
-- 4) keep zero rows visible.
CREATE TABLE countries (
country_code CHAR(2) PRIMARY KEY,
country_name VARCHAR(50) NOT NULL
);
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
country_code CHAR(2) NOT NULL REFERENCES countries(country_code)
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
-- Helpful on reporting tables: the date filter and join key are common access paths.
CREATE INDEX idx_orders_status_date ON orders(status, order_date);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_country_code ON customers(country_code);
INSERT INTO countries (country_code, country_name) VALUES
('US', 'United States'),
('CA', 'Canada'),
('MX', 'Mexico');
INSERT INTO customers (customer_id, customer_name, country_code) VALUES
(1, 'Ava', 'US'),
(2, 'Ben', 'US'),
(3, 'Chloe', 'CA');
INSERT INTO orders (order_id, customer_id, order_date, status, amount) VALUES
(1001, 1, DATE '2024-01-02', 'PAID', 120.00),
(1002, 1, DATE '2024-01-03', 'CANCELLED', 120.00),
(1003, 2, DATE '2024-01-05', 'PAID', 75.50),
(1004, 3, DATE '2024-01-07', 'PAID', 200.00),
(1005, 3, DATE '2024-02-01', 'PAID', 50.00),
(1006, 2, DATE '2024-01-20', 'PENDING', 30.00);
-- Base fact set for the report: only paid January orders.
-- Keeping this logic in a CTE makes the reporting grain obvious and reusable.
WITH paid_january_orders AS (
SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'PAID'
AND order_date >= DATE '2024-01-01'
AND order_date < DATE '2024-02-01'
),
country_report AS (
SELECT
ctry.country_name,
COUNT(pjo.order_id) AS paid_orders,
COALESCE(SUM(pjo.amount), 0) AS revenue,
COALESCE(AVG(pjo.amount), 0) AS average_order_value
FROM countries ctry
LEFT JOIN customers cust
ON cust.country_code = ctry.country_code
LEFT JOIN paid_january_orders pjo
ON pjo.customer_id = cust.customer_id
GROUP BY ctry.country_name
)
SELECT country_name, paid_orders, revenue, average_order_value
FROM country_report
ORDER BY revenue DESC, country_name;
-- Control total for validation: the sum here should match the report's total revenue.
SELECT COALESCE(SUM(amount), 0) AS control_total_revenue
FROM orders
WHERE status = 'PAID'
AND order_date >= DATE '2024-01-01'
AND order_date < DATE '2024-02-01';
-- Common failure path to remember:
-- If you move the paid/date filters into a WHERE clause after the LEFT JOIN,
-- countries with no matching paid orders disappear, which is bad for reporting.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 JOINact like an inner join? Because a filter in theWHEREclause removes the null-extended rows that theLEFT JOINcreated. 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(*)andCOUNT(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
COALESCEand 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 BYfor the final report row shape. - Use
COALESCEfor 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.
Previous
Back to Questions←→to navigate