Normalize Denormalized Orders — Solution & ExplanationProblems / Normalize Denormalized Orders / EditorialApproachDenormalized data repeats entity attributes across rows. To produce a clean fact table, we derive per-entity metrics and join them back.Strategy: Aggregate Then Join

Customer order count: SELECT customer_email, COUNT(DISTINCT order_id) — one row per customer.

Product average price: SELECT product_name, ROUND(AVG(unit_price), 2) — one row per product.

Join back: Enrich the original rows by joining on the natural keys.

Compute line_total: Simple arithmetic quantity × unit_price on each row.

Why Not Window Functions?COUNT(DISTINCT ...) is not supported in window functions in most SQL dialects. The subquery-join approach cleanly separates aggregation from enrichment.PySpark NoteThe .select().distinct().groupBy().count() pattern emulates COUNT(DISTINCT ...) safely within the DataFrame API.Hints

Use self-joins with aggregated subqueries to attach per-customer and per-product metrics to each row.

COUNT(DISTINCT order_id) counts unique orders per customer, not total rows.

In PySpark, use .select().distinct().groupBy().agg() to replicate COUNT(DISTINCT ...) since countDistinct may not be available.

Solve Normalize Denormalized Orders yourself →