What Makes Business Reporting SQL Different
The SQL you write for a back-end application is not the SQL you write for a business report. Application SQL usually fetches one row by ID, updates that row, and moves on. Reporting SQL aggregates thousands of rows, groups by time buckets, joins across many tables, and is run interactively by a human who wants to slice the answer three different ways before lunch. The patterns are different. The mindset is different. The mistakes are different.
The biggest mindset shift is that reporting SQL is exploratory. You rarely know the final shape of the answer when you start. You run a query, scan the output, and the answer suggests a follow-up. That is why the patterns below are written as starting templates — you paste one in, then edit it. Do not try to write the final query from scratch on the first attempt.
The second shift is that reporting SQL is read much more often than it is written. A query you write once will be re-run, copy-pasted, and read by your colleagues for years. Make it readable. Use lowercase column names or snake_case consistently. Comment the tricky bits. Use CTEs (WITH clauses) so the query reads top-to-bottom instead of inside-out. Future-you will read this at 9pm the night before a board meeting and you will thank past-you for the readability.
Setup: Pick a Sandbox and a Sample Database
Open sqliteonline.com in your browser. The site gives you a blank SQLite database with a SQL editor. For practicing the patterns in this guide, this is the fastest option. SQLite supports 95 percent of the reporting SQL you will ever write — CTEs, window functions, CASE expressions, date functions. The 5 percent it does not support (full outer join, some date arithmetic) you can work around.
Both PostgreSQL sample databases ship with a payments table, a customer table, a film or product table, and a date range of a year or two. That is enough to run every pattern below. Google "postgres dvdrental sample database" — it is a one-line download and one-line restore. The patterns below assume payments and customers tables; adapt column names if yours differ.
Before you write any query, find the foreign key relationships. In dvdrental: customer.customer_id links to payment.customer_id, payment.staff_id links to staff.staff_id, payment.rental_id links to rental.rental_id, rental.inventory_id links to inventory.inventory_id, inventory.film_id links to film.film_id. Draw this on a sticky note. Every query you write for the next three months will use at most three of these joins.
If you are using a real work database, do not run reporting queries directly against production. Pull a snapshot into a sandbox or use a read replica. Two reasons: reporting queries are slow and they slow down the application, and you do not want a typo in a DELETE statement to wipe a real table. Reporting SQL belongs in a sandbox, never on production primaries.
Pattern 1: Monthly Revenue Trend (the Query You Write First Every Week)
The single most common business report is a monthly revenue line chart. Every SaaS dashboard, every e-commerce report, every retail review starts with this query. The pattern uses DATE_TRUNC to bucket a date column into months and SUM to aggregate the revenue column. That is the entire pattern. You will run this query roughly once a week for the rest of your career.
SELECT DATE_TRUNC('month', payment_date)::date AS month, SUM(amount) AS revenue FROM payment GROUP BY 1 ORDER BY 1; This gives you one row per month with the total revenue for that month. The DATE_TRUNC part is the magic — it takes any timestamp and rounds it down to the start of the month. The cast to ::date strips the time component so the chart axis is clean. GROUP BY 1 means "group by the first selected column" — shorter than GROUP BY month and survives column renames.
Wrap the previous query in a CTE and add LAG() to compute the previous month's revenue, then divide. WITH monthly AS (SELECT DATE_TRUNC('month', payment_date)::date AS month, SUM(amount) AS revenue FROM payment GROUP BY 1) SELECT month, revenue, LAG(revenue) OVER( (ORDER BY month) ) AS prev_month_revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_growth_pct FROM monthly ORDER BY month; The NULLIF prevents a division-by-zero on the first row where there is no previous month.
Daily revenue is noisy — a single big order makes a daily bar chart spike. Smooth it with a rolling average. Add AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_month_avg to the same CTE. Three-month moving averages are the standard smoothing for monthly business charts. Anything longer and the chart loses too much week-to-week signal.
Always cast DATE_TRUNC results to date (::date) for chart-friendly output. Raw timestamps in the column produce ugly x-axis labels like "2024-01-01 00:00:00+00" instead of "Jan 2024." One cast saves you from reformatting the column in your visualization tool every time.
Pattern 2: Running Totals and Cumulative Revenue
Running totals answer a different question than monthly revenue. Monthly revenue tells you what happened this month. A running total tells you where you are so far — quarter-to-date revenue, year-to-date revenue, lifetime revenue per customer. Both are needed and the patterns are different. The running total pattern uses SUM() as a window function with an unbounded preceding frame.
SELECT DATE(payment_date) AS day, SUM(amount) AS daily_revenue, SUM(SUM(amount)) OVER (PARTITION BY DATE_TRUNC('year', payment_date) ORDER BY DATE(payment_date)) AS ytd_revenue FROM payment GROUP BY 1 ORDER BY 1; The double SUM is intentional — the inner SUM is the daily aggregation, the outer SUM with OVER is the running total. PARTITION BY year resets the running total at the start of each year. Without PARTITION BY, the running total grows forever and crosses years incorrectly.
SELECT customer_id, SUM(amount) AS total_spent, SUM(SUM(amount)) OVER (PARTITION BY customer_id ORDER BY payment_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_spend FROM payment GROUP BY customer_id, payment_date ORDER BY customer_id, payment_date; This produces one row per customer per payment with a running total of how much that customer has spent so far. The cumulative_spend on the last row for a customer is their lifetime value. Most BI tools can plot that as a step line.
Cohort analysis answers "of the customers who joined in January, how much did each subsequent cohort-month generate?" Build a small cohort table: SELECT DATE_TRUNC('month', customer.create_date) AS cohort_month, DATE_TRUNC('month', payment.payment_date) AS active_month, COUNT(DISTINCT customer.customer_id) AS active_customers, SUM(payment.amount) AS revenue FROM customer JOIN payment ON customer.customer_id = payment.customer_id GROUP BY 1, 2; That produces a cohort x month matrix. Pivot it in your BI tool or a spreadsheet and you have a classic retention heatmap.
Window functions with PARTITION BY are where most reporting SQL either works or breaks. If your running total is suddenly the same on every row, you forgot the ORDER BY inside the OVER clause. If it is wrong across years or categories, you forgot the PARTITION BY. Those two clauses together control the entire behavior. Test on three rows before running on a million.
Pattern 3: TopN Rankings (the Query Behind Every "Top 10" Dashboard)
Every business dashboard has a Top 10. Top customers, top products, top regions, top campaigns. The pattern uses ROW_NUMBER or RANK with a window function and filters the result to the top N. The two window functions behave differently when there are ties — ROW_NUMBER gives every row a unique number, RANK gives ties the same rank and skips the next. Choose based on what your audience expects.
WITH ranked AS (SELECT customer_id, SUM(amount) AS total_spent, ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rank FROM payment GROUP BY customer_id) SELECT * FROM ranked WHERE rank <= 10; ROW_NUMBER guarantees exactly 10 rows even if there are ties at the boundary. RANK would give you 10 rows only if the top 10 are unique; with ties you could get 9 or 11. For most Top 10 dashboards, ROW_NUMBER is what you want — your audience expects 10, not 9 or 11.
WITH ranked AS (SELECT c.customer_id, country, SUM(amount) AS total_spent, ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(amount) DESC) AS country_rank FROM customer c JOIN payment p USING(customer_id) GROUP BY c.customer_id, country) SELECT * FROM ranked WHERE country_rank <= 10; This gives you the top 10 customers per country. PARTITION BY country resets the rank to 1 at every country boundary. This is the pattern behind every "Top 10 in each region" dashboard.
Flip the ORDER BY to ASC and you have a Bottom 10. Useful for finding dormant customers, low-performing products, and inactive sales reps. Same template, opposite order, no extra logic.
If your Top 10 includes a customer you do not recognize, you probably have a test account or a fraud account in production. Filter those out at the start of every TopN query — your audience will lose trust in the dashboard the moment they see "Test Customer" in position 1. WHERE customer_id NOT IN (SELECT id FROM test_customers) is a one-line addition that prevents a lot of awkward conversations.
Pattern 4: Funnel Conversion (Sign-up → Active → Paid → Retained)
Funnel queries count distinct users at each step of a flow. The pattern is a chain of CTEs, one per step, each with its own WHERE clause that defines the event. The final query joins the CTE row counts and computes the conversion rate between steps. Every product analyst writes this query every quarter. Once you have the template, you can adapt it to any product funnel in 15 minutes.
WITH step1 AS (SELECT COUNT(DISTINCT user_id) AS users FROM event WHERE event_name = 'signup'), step2 AS (SELECT COUNT(DISTINCT user_id) AS users FROM event WHERE event_name = 'first_login'), step3 AS (SELECT COUNT(DISTINCT user_id) AS users FROM event WHERE event_name = 'first_purchase'), step4 AS (SELECT COUNT(DISTINCT user_id) AS users FROM event WHERE event_name = 'second_purchase') SELECT (SELECT users FROM step1) AS signup, (SELECT users FROM step2) AS activated, (SELECT users FROM step3) AS paid, (SELECT users FROM step4) AS retained; This gives you four numbers. The conversion rates between them are computed by the BI tool or by adding ROUND(100.0 * activated / NULLIF(signup, 0), 1) AS signup_to_activated_pct to the SELECT.
Add DATE_TRUNC('week', event_time) AS week to each CTE and GROUP BY week, and you have a weekly funnel trend. This is what every growth dashboard plots. Watch the activated/signup rate over time — that is the metric that tells you whether your onboarding is improving.
SELECT user_id, MIN(event_time) FILTER (WHERE event_name = 'signup') AS signup_time, MIN(event_time) FILTER (WHERE event_name = 'first_purchase') AS first_purchase_time, EXTRACT(EPOCH FROM (MIN(event_time) FILTER (WHERE event_name = 'first_purchase') - MIN(event_time) FILTER (WHERE event_name = 'signup'))) / 3600 AS hours_to_convert FROM event GROUP BY user_id; FILTER is a clause that lets you aggregate conditionally inside the same MIN. The hours_to_convert column tells you how long it took each user to convert. AVERAGE it and you have a leading indicator of activation quality.
Funnel queries break the moment you rename an event name. Document every event_name value in a data dictionary and treat that dictionary as a contract — anyone who renames an event breaks every funnel query in the company. Better yet, use an event tracking system that gives you stable event IDs even when display names change.
Common Pitfalls (What Breaks These Queries in Production)
Reporting SQL has a short list of recurring bugs. Almost every analyst has shipped one of these. They are all preventable if you know to look for them. The pattern is the same in every case: the query runs, returns a number, the number looks plausible, the chart gets built, and three months later someone notices the number was wrong the whole time. The bugs are quiet. The fix is always the same — be explicit about which rows count and which do not.
COUNT(*) counts every row. COUNT(DISTINCT user_id) counts each user once. They are different. If a user makes three purchases in a month, COUNT(*) reports three, COUNT(DISTINCT user_id) reports one. The right choice depends on the question. "How many orders did we get?" is COUNT(*). "How many unique customers bought?" is COUNT(DISTINCT user_id). Mixing them up is the single most common reporting bug.
100.0 * this_month / NULLIF(last_month, 0) is safer than 100.0 * this_month / last_month. Without NULLIF, if last_month is 0, you get a division-by-zero error. NULLIF(last_month, 0) returns NULL when last_month is 0, and dividing by NULL is NULL — your report just shows a NULL for that row instead of crashing the whole query.
Your database stores timestamps in UTC. Your users are in Pacific time. A payment that happened at 11pm Pacific on Jan 31 is stored as 7am UTC on Feb 1. If your report groups by DATE_TRUNC('month', payment_date) without a time zone conversion, the payment lands in February instead of January — and your January number is wrong by the amount of that one late-night order. Multiply by thousands of users and your monthly chart is off by 5 to 15 percent. Always AT TIME ZONE 'America/Los_Angeles' before DATE_TRUNC in a query that runs in a different time zone from your users.
INNER JOIN drops rows that do not have a match. If your fact table has a customer_id that does not exist in the customer table (deleted customer, bad data, late-arriving record), the row disappears from your report. Use LEFT JOIN fact_table LEFT JOIN customer ON fact_table.customer_id = customer.customer_id and you see every fact row, with NULL for the customer columns when the customer is missing. NULLs are visible. Missing rows are invisible. Always use LEFT JOIN for the side you cannot afford to lose.
Add a sanity-check row to every reporting query. SELECT 'total_rows' AS check_name, COUNT(*) FROM payment UNION ALL SELECT 'distinct_customers', COUNT(DISTINCT customer_id) FROM payment UNION ALL SELECT 'min_date', MIN(payment_date)::text FROM payment UNION ALL SELECT 'max_date', MAX(payment_date)::text FROM payment; Run this every time you ship a new query. If the sanity numbers look reasonable, the query is probably right. If they look weird, debug before shipping.
Putting It Together: A Weekly Reporting Stack
You do not need twelve separate queries every week. You need three or four queries that cover the questions your boss actually asks. The pattern is: a revenue trend, a TopN, a funnel, and a sanity check. Together they answer 90 percent of weekly reporting. Everything else is an exception.
The actual workflow is: save each query as a view or a scheduled task in your warehouse, schedule it to refresh on Monday morning, and pipe the output into a spreadsheet or BI dashboard. Your boss reads the dashboard, asks follow-up questions, you run one ad-hoc query to answer the follow-up, and that follow-up either becomes a permanent dashboard tile or stays as a one-off. Over time, your dashboard grows organically and reflects the questions the business actually asks.
The other 80 percent of reporting work is not the SQL itself — it is reading the numbers, explaining what changed, and proposing what to do about it. The SQL gets you the numbers in five minutes. The interpretation is what makes you valuable. Focus on building the habit of running the query, looking at the output, and asking "why did this number change?" before sending it to anyone. That single habit separates analysts who get promoted from analysts who stay stuck running queries for other people.