EDA in SQL: understand the data before reporting a result

You receive a table, write SUM(amount) and get a number. You still may not know whether one row represents an order, an order line or another version of the same event. That is where I start exploratory data analysis, or EDA, in SQL: establish what is being counted and what remains unclear.

EDA helps reveal structure, missing values, distributions and suspicious observations. NIST describes it as an approach to understanding data, with a substantial role for graphics. SQL is useful for initial profiling and group comparisons, but aggregate tables alone do not complete an exploration. A chart may reveal a shape that an average cannot show.

The examples use PostgreSQL 18. All records are synthetic, deliberately imperfect and small enough to follow manually. Expected results come from the displayed records and documented rules. Syntax from one database engine should not be presented as universally interchangeable SQL.

Start with the question and the level of detail

Suppose the question concerns the value of paid orders during a period. Before adding a filter, I need a definition of “paid”, an explanation of the amount, and a rule for identifying one order. The amount may belong to the whole order or an individual line. The status may represent the current state or an event in its history.

This level of detail is often called the table’s grain. Our exercise expects one row per order. The raw source may violate that expectation, so I do not assume uniqueness simply because a column is named order_id.

I also distinguish the order period from the payment period. A report about payments received in October may need a different date from order creation. Here, order_date means the order date, and I describe the analysis accordingly. A requirement to filter by payment date would require additional data.

Inspect the structure using the right tool

For an actual table called public.orders_raw, I can inspect columns with:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders_raw'
ORDER BY ordinal_position;

The information_schema.columns view shows columns accessible to the current user. An empty result calls for checking the name, schema and object availability. It does not establish that no similar table exists anywhere in the database.

You may encounter DESCRIBE and SHOW COLUMNS in other engines. They are not a universal PostgreSQL table-inspection recipe. Data types and nullability also cannot supply business meaning. A numeric column can contain amounts in different units, and permission to store NULL does not explain why a value is absent.

I therefore ask about currency, refresh rules, time zones, historical coverage and corrections. For amounts, an appropriate numeric type may be useful, but choosing a type does not decide whether values in different currencies can be added together.

Six records you can follow manually

A CTE lets us describe the exercise without requiring a company database:

WITH orders_raw(order_id, customer_id, order_date, status, amount) AS (
    VALUES
        (101, 1, DATE '2026-10-01', 'paid',      100::numeric),
        (102, 1, DATE '2026-10-02', 'paid',       50::numeric),
        (102, 1, DATE '2026-10-02', 'paid',       50::numeric),
        (103, 2, DATE '2026-10-03', 'cancelled',   0::numeric),
        (104, NULL, DATE '2026-10-04', 'paid', NULL::numeric),
        (105, 3, DATE '2026-10-05', 'paid',      300::numeric)
)
SELECT *
FROM orders_raw
ORDER BY order_id, order_date, amount NULLS LAST;

This complete statement defines the source only for that statement. It does not create a persistent table. For each later example, retain the WITH ... AS (...) block and replace its final SELECT with the query shown. If you have an actual table with the same name, you can query it without the CTE.

There are six rows and five distinct order identifiers. Identifier 102 appears twice. Order 104 has no customer identifier or amount, while 103 is cancelled with an amount of zero. Each observation leads to a different question. Calling everything “bad data” would lose useful distinctions.

Count rows, known values and missing values separately

Replace the final selection with a profile:

SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS distinct_orders,
    COUNT(amount) AS known_amounts,
    COUNT(*) - COUNT(amount) AS missing_amounts,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_known_amount
FROM orders_raw;

We expect six rows, five identifiers, five known amounts and one missing amount. Known amounts are 100, 50, 50, 0 and 300. Their sum is 500, their average is 100, their minimum is 0 and their maximum is 300. These are characteristics of raw records, before establishing the final business measure.

The PostgreSQL aggregate documentation distinguishes COUNT(*) from COUNT(expression). The latter excludes NULL. Similarly, AVG(amount) averages known amounts. Replacing the missing value with zero would change both the denominator and the meaning of the average. That needs a specific rule rather than an automatic impulse to fill blanks.

For empty input, COUNT returns zero, while aggregates including SUM and AVG return NULL. I distinguish an absence of records from records whose amounts sum to zero. A dashboard can easily make those states look identical even though they imply different things about the source.

A repeated identifier needs an explanation

Look for repeated values in the key expected to identify an order:

SELECT order_id, COUNT(*) AS occurrences
FROM orders_raw
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

The result contains identifier 102 with a count of 2. Under our exercise’s one-row-per-order rule, that violates an expectation. In another source, two rows might represent two order lines or historical versions. The table’s meaning determines whether the repetition is a technical duplicate.

I avoid repairing this with SUM(DISTINCT amount). That would remove repeated amounts even when they belong to separate legitimate orders. I also avoid selecting an arbitrary row without checking the other columns. Conflicting statuses or amounts require a rule for choosing the relevant version.

The two rows in our example are identical. Once a technical import duplication is confirmed, one copy of 50 could be excluded from the calculation. Order 104 would still have a missing amount. Resolving one issue does not establish the quality of the entire dataset.

Category frequencies reveal hidden definitions

Inspect the statuses actually present:

SELECT
    status,
    COUNT(*) AS rows_in_status,
    COUNT(amount) AS known_amounts,
    SUM(amount) AS known_total
FROM orders_raw
GROUP BY status
ORDER BY status;

For cancelled, there is one row, one known amount and a total of zero. For paid, there are five rows, four known amounts and a total of 500. The summary exposes both category coverage and completeness. A label of paid does not guarantee that an amount exists.

In a real source, I would also inspect case differences, spaces, empty strings and unfamiliar codes. Consolidating every variant may be appropriate, but it needs a mapping grounded in the source system’s meaning. A value of pending and an absent status need not mean the same thing.

Once categories are understood, I compare their shares across periods or sources. A sudden increase in missing values can suggest an export change. A different status mix can also reflect an actual operational change. EDA identifies avenues for investigation; frequency alone cannot determine the cause.

Time boundaries and period completeness

The exercise dates run from 1 October to 5 October 2026. That is not a complete month. I would avoid calling the total “October sales” without explaining the limited coverage. A maximum date describes the table’s contents rather than proving that ingestion is complete.

For an October filter, I would use a half-open interval:

SELECT order_id, order_date, status, amount
FROM orders_raw
WHERE order_date >= DATE '2026-10-01'
  AND order_date < DATE '2026-11-01'
ORDER BY order_date, order_id;

Those are clear monthly boundaries for a date column. With timestamps, the type and time zone need additional decisions. Comparing a timestamp to midnight at the start of the final day can accidentally exclude the rest of that day. I keep those cases separate instead of offering a universal recipe.

I also check dates with no records. No Sunday orders may be expected, while an entire missing day after an export failure needs investigation. An expected calendar and source information help distinguish zero activity from a missing delivery of data.

A large value is not automatically an error

An amount of 300 is the largest in this small dataset. That alone gives no reason to remove it. A large order may be correct, while an ordinary-looking small amount may have the wrong currency. I start with validity rules and the original record.

I prefer the statement “one observation has an amount of 300” over an anomaly label based on an arbitrary threshold. If the business has a review threshold, I can prepare a list of records exceeding it. The threshold remains a named process rule that should be explained alongside the result.

The familiar mean plus or minus three standard deviations can be a useful heuristic in an appropriate setting, but it is not a universal detector of incorrect records. Amount distributions may be skewed, and a small sample makes such boundaries particularly difficult to interpret. Here, I inspect records and compare groups before choosing a method.

Relationships and correlation need the right unit

Before joining orders to customers, I check whether customer identifiers are unique on the other side. Several history rows for a customer can multiply order amounts in a simple JOIN. After joining, I compare row counts and control totals with the input. The LEFT JOIN guide explains the underlying mechanics.

I distinguish orders without a customer identifier from non-empty keys with no matching customer. Those are different conditions: an absent key and a key missing from the reference table. Combining both under “unknown” may help presentation, but fixing ingestion requires retaining the distinction.

PostgreSQL provides corr(Y, X), but I would not correlate customer identifiers with order amounts. Customer numbers do not form a meaningful quantitative scale. Even with two suitable variables, I check complete pairs, the observation unit and a chart. Correlation alone cannot establish the direction or cause of a relationship.

Separate exploration, repair and the final measure

During EDA, I preserve raw values and identify which rules would require transformations. Each finding includes its scope: six records, five identifiers, one missing amount and one repeated order. Another person can then follow where the uncertainty comes from.

Removing the confirmed technical copy of 50 would reduce the known total from 500 to 450. Four paid order identifiers would remain, with amounts known for three of them. I would avoid presenting 450 as the complete value of all paid orders because order 104 still has an unknown amount. It is the sum of known values after a specified correction.

That description is more useful than a number without conditions. It identifies what is known, which decisions have been made and what is still needed to answer the original question. More complicated calculations make sense once those foundations are clear. The guide to SQL clause order can help organise filtering and grouping.

Finish the first pass with useful questions

I organise my analytical notes around questions requiring answers instead of copying a dozen unexplained query results. For repeated order 102, I need confirmation of the import rule. For 104, I need to know whether the amount is awaiting settlement. For the date range, I need to establish whether delivery is complete. Each question has a direct effect on the future measure.

I also record when the data was read. If a table changes during analysis, the first aggregate and a later sample may reflect different states. A repeatable comparison needs an agreed way to read the same scope. Our six exercise rows are fixed; a live operational source may refresh while the analysis is in progress.

On a large table, I choose query scope and cost with the environment in mind. A small sample helps reveal formats, but cannot prove the absence of rare errors. Complete counts can require a wider read. I avoid promising faster execution merely from rearranging the written column order: cost depends on the engine, data structure and available indexes.

A useful first exploration therefore describes the observation unit, time coverage, missing values and conditions for using the data. If an assumption remains unresolved, I keep that limitation beside the conclusion. Someone making a decision needs to know whether a number is ready for reporting or still points towards further investigation.

Keep the filtering rule with the result

A filter of status = 'paid' answers a question about records carrying that exact code. It does not establish whether an order was ever paid or later refunded. If the source retains only the latest status, a historical answer may require an event table. Discover that limit before preparing a chart covering many months.

Similarly, customer_id IS NULL detects an absent identifier rather than an absent customer description. I avoid interpreting it as “anonymous customer” without knowing the process. A guest checkout, a delayed reference-table update or an import failure could produce the same missing value. Those explanations lead to different actions.

I check whether a filter removes the very records being investigated. Profiling only known amounts with WHERE amount IS NOT NULL makes the missing-value count zero by definition. I first describe the complete agreed scope, then show any filtered version and explain the difference.

For a before-and-after cleaning comparison, I choose consistent control figures. Here, they are row count, distinct identifiers, missing values and the sum of known amounts. If a correction removes one copy of order 102, I expect row count and sum to change while the distinct-identifier count remains unchanged. That expectation can be stated clearly before the transformation.

Some questions need the process owner’s explanation. The table alone cannot establish whether a cancelled order should retain its earlier amount or be set to zero. I do not infer company policy from a few records. Instead, I distinguish observed values from process decisions and explain the effect of each possible interpretation.

As exploration progresses, I revisit the initial assumption about grain. An order-line number may reveal a two-column key. Daily snapshots may require a snapshot date as well. The repeated order identifier would then have a different explanation, changing how the eventual measure should be prepared.

That is a useful point to pause automatic deduplication. Choosing one historical version without a defined reference date can change the report in a way that is difficult to justify later. I prefer a first assessment with a precise open question to deleting necessary records and declaring the dataset ready. Exploration should reduce uncertainty while retaining the evidence needed for the next step.

The cancelled order with a zero amount does not increase the sum, but it remains in both the raw row count and the average of known raw amounts. A total alone therefore does not describe the included observations. I keep the filter and counting unit with every measure.

Exercise: reason through one additional record

On paper, add synthetic order 106 for customer 3, with status paid, amount 50 and date 6 October. Predict the new row count, known-amount count and distinct-order count. Explain why a separate legitimate order worth 50 should survive duplicate handling.

Next, consider three versions of order 104: the amount remains unknown, it is confirmed as zero, or it is confirmed as 100. Describe how completeness and the average of known amounts change. This exercise shows why a useful first aggregate starts with the meaning of the records. That is how I approach practical EDA in SQL.