SELECT and WHERE: build a reproducible sales extract

SELECT chooses the columns or expressions returned by a query. WHERE determines which input rows qualify. For a business extract, those two decisions define both the information delivered and the population it describes.

The practical challenge is rarely remembering the syntax. It is making eligibility clear enough that the same extract can be rerun next week without someone guessing what “January sales” meant.

This lesson builds a small completed-order extract with the synthetic commerce lab. It contains ten orders, including a cancellation, a pending order and an order just before the end of January. These deliberate edge cases help test the reporting definition.

Turn the request into a contract

Suppose a stakeholder asks for January completed orders and their recorded values. Before writing the query, agree on four points:

1. 1The unit is an order, not an item or payment event.
2. 2The date is the order timestamp in the fixture's single business timezone.
3. 3Only completed orders qualify.
4. 4Values are before refunds, and the stored order amount already includes discounts.

That final point prevents a plausible mistake: subtracting a discount again from a value that is already discounted. The column name alone does not establish the business definition.

This example describes January 2026 as a fixed teaching period. It is not a claim about real company sales.

Use explicit output columns

SELECT
    order_id,
    customer_id,
    ordered_at,
    order_total_paise
FROM orders
WHERE status = 'completed'
  AND ordered_at >= '2026-01-01T00:00:00'
  AND ordered_at <  '2026-02-01T00:00:00'
ORDER BY ordered_at, order_id;

The output contains eight orders whose values sum to 104,000 paise. Selecting named columns makes the extract easier to review and less sensitive to an unrelated column being added to the source table. It also avoids exporting fields that the recipient does not need.

ORDER BY makes the displayed sequence explicit. Without it, do not assume a database will return rows in insertion order. The order ID provides a stable secondary ordering when timestamps tie.

The query uses SQL operations available in SQLite, the engine used by the lab. SQLite's documentation describes how filtering and result selection contribute to a SELECT statement. SQLite SELECT reference.

Make the end boundary exclusive

The interval includes the beginning of January and excludes the beginning of February. This includes O1009 at 2026-01-31T23:59:59 and excludes O1010 at 2026-02-01T00:00:00.

Using a half-open interval avoids trying to guess the last representable instant of a month. A production database may store fractions of a second, so a filter ending at 23:59:59 could miss later records in that second.

Our fixture stores consistently formatted ISO timestamps as text. A real database should use an appropriate timestamp type and an explicit timezone convention. If the business month is defined in one timezone and storage uses another, calculate the correct boundaries before executing the extract. Do not assume that changing a date label changes the actual instants being filtered.

Check exclusions, not only inclusions

An eight-row output is useful, but it does not explain why two source rows disappeared. Inspect the status distribution within the period:

SELECT status, COUNT(*) AS orders
FROM orders
WHERE ordered_at >= '2026-01-01T00:00:00'
  AND ordered_at <  '2026-02-01T00:00:00'
GROUP BY status
ORDER BY status;

You should see eight completed orders and one cancelled order. The pending order falls in February. These checks distinguish a deliberate exclusion from a missing source record.

Now verify the monetary control:

SELECT COUNT(*) AS orders, SUM(order_total_paise) AS total_paise
FROM orders
WHERE status = 'completed'
  AND ordered_at >= '2026-01-01T00:00:00'
  AND ordered_at <  '2026-02-01T00:00:00';

Expected: 8 and 104000. Keep the count and total alongside the exported file. If a later run changes them, investigate whether the source changed, the contract changed or the implementation changed.

Parameterize the period in a reusable script

When the query becomes part of Python automation, pass values as parameters rather than concatenating them into SQL:

sql = """
SELECT order_id, order_total_paise
FROM orders
WHERE status = ? AND ordered_at >= ? AND ordered_at < ?
ORDER BY ordered_at, order_id
"""
rows = db.execute(sql, (
    "completed", "2026-01-01T00:00:00", "2026-02-01T00:00:00"
)).fetchall()

This uses Python's sqlite3 placeholder convention; other database drivers may use a different parameter style. Values are parameters, while table and column identifiers need separate, controlled handling. Python sqlite3 documentation.

Save enough information to rerun the extract

The deliverable should include the query, period boundaries, timezone assumption, status eligibility, amount units, run timestamp and source snapshot identifier if one exists. A row count and amount total provide compact checks, but they do not prove that the source itself is complete.

Exercise: change the request to include all January orders regardless of status. The population becomes nine, and the total becomes 119,000 paise. Explain why this is a different metric instead of treating it as a corrected version of the completed-order extract.

If your next step involves joining item or customer details, read SQL row grain before carrying the header amount into a different row structure.

The Data Analytics with Generative AI course connects this SQL foundation to Python pipelines and reporting. A reproducible extract is an early building block: it gives later dashboards and AI explanations a defined population to work from.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL row grain: stop double-counting orders before you query.
• Continue with SQL NULL values: why missing is not zero.