SQL for business analysts: a practical beginner's guide

SQL is still the most useful technical skill for anyone who answers business questions with data. It lets you check a dashboard number against the source, answer a one-off question without waiting for someone else, and describe exactly what you want when you ask a colleague or a tool for help.

This guide covers the SQL you will use every week, with short examples on three tables: customers, orders and order_items. The syntax is PostgreSQL; almost all of it works the same in MySQL, SQL Server, BigQuery and Snowflake, with small differences in date functions.

SELECT and FROM: pick columns from a table

SELECT customer_id, name, signup_date
FROM customers
LIMIT 20;

Name the columns you need rather than SELECT *. It is faster on wide tables and makes the query easier to read later. LIMIT keeps exploratory queries quick.

WHERE: filter rows

SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-04-01'
  AND status = 'paid';

Use a half-open range (>= start, < end) for dates. BETWEEN '2026-01-01' AND '2026-03-31' silently drops anything after midnight on 31 March when the column holds timestamps.

Other everyday filters: IN ('EU', 'UK'), LIKE '%@example.com', and IS NULL / IS NOT NULL (never = NULL).

GROUP BY: turn rows into totals

SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*)                       AS orders,
       SUM(total_amount)              AS revenue,
       SUM(total_amount) / COUNT(*)   AS avg_order_value,
       COUNT(DISTINCT customer_id)    AS customers
FROM orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;

Every column in the SELECT is either in the GROUP BY or inside an aggregate such as SUM, COUNT, AVG, MIN or MAX.

HAVING filters after grouping. WHERE cannot see aggregates:

SELECT customer_id, SUM(total_amount) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 5000
ORDER BY spent DESC;

JOIN: combine tables

-- Every customer, with their order count (zero if none)
SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

• INNER JOIN keeps only rows that match in both tables.
• LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match. Use it for "who has not..." questions: add WHERE o.order_id IS NULL to find customers with no orders.

CASE: label rows

SELECT customer_id,
       SUM(total_amount) AS spent,
       CASE WHEN SUM(total_amount) >= 5000 THEN 'high'
            WHEN SUM(total_amount) >= 1000 THEN 'medium'
            ELSE 'low' END AS tier
FROM orders
GROUP BY customer_id;

CTEs: write the query in steps

A common table expression (WITH ...) names an intermediate result so you can build a query in readable steps instead of nesting subqueries.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(total_amount) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT month, revenue
FROM monthly
WHERE revenue > 50000;

Window functions: compare rows without collapsing them

Window functions compute across related rows while keeping each row. They cover most of the "compared with last month" and "top N per group" questions.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(total_amount) AS revenue
  FROM orders WHERE status = 'paid' GROUP BY 1
)
SELECT month,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change,
       SUM(revenue) OVER (ORDER BY month) AS running_total,
       AVG(revenue) OVER (ORDER BY month
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3m
FROM monthly
ORDER BY month;

• LAG reads the previous row, LEAD the next one.
• SUM(...) OVER (ORDER BY ...) gives a running total.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a three-month moving average.
• ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) numbers rows within each category; filter on it to get the top 3 per category.

Window functions are also the basis of cohort retention tables and RFM segmentation.

Five mistakes that give wrong totals

1. Join fan-out. Joining orders to order_items repeats each order once per item. SUM(o.total_amount) after that join counts a three-item order three times. Sum at the item level (SUM(oi.quantity * oi.unit_price)), or aggregate each table in its own CTE before joining.

2. Integer division. In PostgreSQL, 3 / 4 is 0. Write 3.0 / 4, or cast one side (3::numeric / 4), when calculating rates.

3. NULLs in averages and counts. AVG(discount) ignores rows where discount is NULL, which may not be what you mean. COUNT(*) counts rows; COUNT(column) counts non-null values. Use COALESCE(discount, 0) when a missing value really means zero.

4. Dividing by zero. Wrap denominators in NULLIF(x, 0) so an empty month returns NULL instead of an error.

5. Time zones. A timestamp stored in UTC and grouped by date puts late-evening orders in New York on the next day. Convert first: DATE_TRUNC('day', order_ts AT TIME ZONE 'America/New_York') for a timestamptz column.

Practice exercises

Try these on a sample database with the three tables above:

1. New customers per month for the last 12 months, with the month-over-month change (DATE_TRUNC, COUNT, LAG).
2. The best-selling product in each category last quarter (ROW_NUMBER with PARTITION BY).
3. Customers who ordered in 2025 but not in 2026 (LEFT JOIN or NOT EXISTS).
4. Each product's share of total revenue, with a running cumulative share from the top, for a Pareto view (SUM() OVER ()).
5. Revenue by weekday, converted to your local time zone first.

Where SQL fits next to a BI tool

SQL and a BI tool do different jobs. SQL is precise and repeatable; a BI tool makes the results visible and lets people who do not write SQL ask their own questions.

A practical split for a small team: use SQL to create a few clean views in your database (daily revenue by channel, orders with customer attributes), and point the BI tool at those views. In clariBI, the PostgreSQL connection runs only SELECT statements with a read-only user you create, and each sync copies up to a per-plan number of rows per table, so an aggregated view is both faster and more complete than a raw event table. The database connection guide includes the SQL to create that user, the PostgreSQL walkthrough covers the setup end to end, and the database docs cover MySQL, SQL Server and Oracle as well.

Once the views are connected, anyone on the team can ask about them in plain English on the Trial or on Starter and above. clariBI does not write SQL behind the scenes: it runs its calculations as pandas code on the synced rows, and removes any narrated figure the chart data cannot back. Your SQL skills remain the way to check a surprising number at the source. The post on how natural language analytics works and where it fails explains where each approach is stronger.

A next step: write the monthly revenue query from the GROUP BY section against your own orders table, then check its total for last month against your billing system. If they differ, the five mistakes above are the first places to look.