Purpose

This independent portfolio exercise explores how SQL can summarize retail transaction rows by channel, product category, store, and customer tier. It was not commissioned by or performed for an actual retailer.

Data

The repository labels its dataset as synthetic, built with randomized values and Excel lookups. It does not represent actual customers, stores, or company performance.

A review of the CSV reveals an important integrity problem: different rows with the same order_id can have different customer_id and order_date values. In the sample, ORD00001 appears on four rows with differing customers and dates. The rows therefore cannot be safely treated as valid order lines belonging to one consistent order.

Approach

The SQL groups rows using GROUP BY and summarizes measures with SUM, COUNT, and AVG. It also uses CASE to count rows matching a return flag, NULLIF to avoid division by zero in margin calculations, and ordering and limiting to present ranked sample summaries.

Technology

SQL and a synthetic retail CSV dataset.

What the queries demonstrate

The included queries show how to produce descriptive row-level summaries across several dimensions. Their calculated values describe this synthetic dataset only; they are not real business results or evidence for operational decisions.

Data-quality limitations

  • COUNT(DISTINCT order_id) cannot be interpreted as a trustworthy order count while order IDs do not consistently identify one order.
  • The query labeled average order value calculates AVG(sales_amount) per row. It is an average row-level amount, not an order-level basket value.
  • Inconsistent dates and customer IDs prevent dependable time-based or customer-level interpretation of those rows.
  • Synthetic values do not establish actual retail performance, causation, forecasts, or business impact.

For a decision-grade analysis, the source data would first need consistent order, customer, and date relationships, followed by revalidation of the KPI definitions and query results.

Learning

Aggregate queries can be written correctly yet still produce misleading labels or conclusions when the underlying identifiers and grain are unclear. Checking row grain and key consistency is essential before interpreting counts, averages, or rankings.