Skip to content
Expert O'clock

SQL

SQL for business analysts: five query patterns worth knowing

Five practical SQL patterns for reporting: aggregation, joins, date filtering, window functions, and data quality checks.

Talha Ansari··3 min read

Key takeaways

  • Start with the business question and the grain of the data.
  • Use joins only after you understand the relationship and the row count.
  • Window functions are useful for trends, rankings, and prior-period comparisons.
  • Add data quality queries to the same workflow as reporting queries.
On this page

Aggregation: answer the question at the right level

Most reporting queries start with an aggregation: revenue by month, orders by customer, stock by location. The key is to know the grain of the source table before grouping.

If the source has one row per order line, summing revenue by order requires care. A single order with three lines can be counted three times if the query is not written correctly.

Aggregate at the order grain before reporting by monthSQL
WITH order_totals AS (
  SELECT
    order_id,
    customer_id,
    DATE_TRUNC('month', order_date) AS order_month,
    SUM(line_amount) AS order_revenue
  FROM order_lines
  GROUP BY order_id, customer_id, DATE_TRUNC('month', order_date)
)
SELECT
  order_month,
  COUNT(*) AS orders,
  SUM(order_revenue) AS revenue
FROM order_totals
GROUP BY order_month
ORDER BY order_month;

Joins: check the relationship first

Joins can multiply rows when the relationship is not one-to-one. Before joining, check the key and the expected cardinality. A customer table should have one row per customer; a transaction table may have many.

Compare row counts before and after the join. If the count changes unexpectedly, stop and investigate. This simple check prevents many reporting errors.

Date filtering: be explicit

Use half-open date ranges such as `>= start` and `< next_start` to avoid missing records at the end of a period. Define whether the date is an invoice date, order date, or payment date.

Time zones can also change the result. If the source stores timestamps in UTC, convert them consistently before grouping by day or month.

Window functions: compare without losing detail

Window functions let you calculate rankings, running totals, prior values, and period comparisons while keeping the detail rows. They are useful for customer ranking, month-over-month change, and first/last activity.

Use a clear partition and order. A window function without the right partition can produce a result that looks plausible but is wrong.

Quality checks: query the exceptions

Reporting queries should be paired with checks: duplicate keys, missing periods, null values in mandatory fields, and totals that do not reconcile.

Write these checks as queries and run them on every refresh. It is easier to fix a data issue before it reaches a dashboard.

Frequently asked questions

How much SQL does a business analyst need?+

Enough to select, filter, aggregate, join, and validate data. Window functions and CTEs are useful next steps.

Should I learn SQL or Python first?+

SQL is usually the higher-leverage first step for reporting and analytics because most business data lives in relational systems.

How do I avoid duplicate totals?+

Understand the grain of each table, check join cardinality, and aggregate at the correct level. Compare row counts before and after joins.

Need help applying this?

Improve your data cleanup & pipelines.

Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.

Discuss this serviceView service
Free KPI definition template

Related reading