SQL: Questions in Queries

Keep the question stable

We are still comparing known net sales between North and South in the illustrative January 2026 export. Changing tools should not change the metric. Net sales remains sales minus refunds for orders with both amounts present. The confirmed repeated export row has already been removed, but the incomplete order remains in the cleaned data.

SQL is useful because it leaves a visible sequence of instructions. It is not automatically safer than a spreadsheet. A query can quietly omit records, multiply them through a join or apply an inconsistent date filter. Readable structure and small reconciliation checks help you notice those mistakes before they become a dashboard.

Create a reproducible starting table

The following setup is intended for PostgreSQL or SQLite in a scratch database. Run it once in an empty workspace. It creates a temporary practice table for the current database session; do not run tutorial setup against production data. The text dates are suitable for this limited ISO-formatted example, although production models should use appropriate date types.

-- Five unique orders after the spreadsheet review.
CREATE TEMP TABLE clean_orders (
  order_id TEXT PRIMARY KEY,
  order_date TEXT,
  region TEXT,
  channel TEXT,
  sales NUMERIC,
  refund NUMERIC
);

INSERT INTO clean_orders VALUES
('A101', '2026-01-03', 'North', 'Online', 120, 20),
('A102', '2026-01-04', 'South', 'Store',   80, 10),
('A103', '2026-01-05', 'North', 'Store',  150, 30),
('A104', '2026-01-06', 'South', 'Online',  50, 10),
('A105', '2026-01-07', 'North', 'Online', NULL, 0);

Inspect before aggregating

Select a few named columns and order the rows so the output is easy to compare with your workbook. Avoid beginning with a complicated query that mixes cleaning, joining and ranking. A simple baseline gives you something stable to return to when later results change.

-- NULL means the amount is unknown.
SELECT order_id, region, sales, refund
FROM clean_orders
ORDER BY order_id;

-- Count all records and records with a known sales value.
SELECT COUNT(*) AS unique_orders,
       COUNT(sales) AS known_sales_orders
FROM clean_orders;

The second query returns five unique orders and four orders with a known sales amount. COUNT(*) counts rows; COUNT(sales) counts non-NULL sales values. A zero is a value and is counted. A NULL is not a zero, and ordinary arithmetic involving it remains unknown.

Write the business definition explicitly

Filter complete amounts before calculating the regional sums. The half-open date range includes the start of January and excludes February. This pattern is especially useful when a real date field also contains times, because it avoids guessing the final instant of the reporting day.

-- Complete orders only; output is in currency units.
SELECT region,
       COUNT(*) AS complete_orders,
       SUM(sales - refund) AS net_sales
FROM clean_orders
WHERE sales IS NOT NULL
  AND refund IS NOT NULL
  AND order_date >= '2026-01-01'
  AND order_date < '2026-02-01'
GROUP BY region
ORDER BY net_sales DESC, region;

Do not replace the missing sales amount with zero just to produce a tidy result. COALESCE is useful when a default has a defensible meaning, but convenience is not a business definition. Here, replacing A105’s unknown sales amount would conceal incomplete coverage.

Carry coverage beside the metric

A second query should retain all five orders and describe completeness by region. This prevents the incomplete North record from disappearing from the discussion merely because it disappeared from the monetary calculation.

SELECT region,
       COUNT(*) AS all_orders,
       SUM(CASE
         WHEN sales IS NOT NULL AND refund IS NOT NULL
         THEN 1 ELSE 0
       END) AS complete_orders
FROM clean_orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2026-02-01'
GROUP BY region
ORDER BY region;

North should show three orders with two complete; South should show two with two complete. Reconcile the overall totals to the spreadsheet before moving on. A mismatch is a useful signal: inspect date scope, missing-value handling and duplicate treatment rather than adjusting the output to match your expectation.

Treat joins as a change of grain

Suppose you add a returns table containing several refund events per order. Joining it directly to orders repeats the order’s sales value for each event. Summing sales afterward inflates revenue. First aggregate returns to one row per order, then join that result to the order table, and compare row counts and monetary totals before and after the join.

Every join deserves a sentence explaining what one output row represents.

Self-check: why not use SELECT DISTINCT everywhere?

It can conceal a faulty join and may collapse legitimate identical-looking records. Diagnose the relationship, choose the intended grain and resolve duplication at its source.

Save the setup, query and expected output together. A reviewer should be able to reproduce the result without reconstructing the sequence from screenshots. Your next task is to turn these tested outputs into a readable decision surface.

Next studyDashboards: Design for Decisions →