00:00

2507. Normalized Order Items for Active Products

MediumSQLITE105 / 1584

Relevant companies

Amazon

Walmart

Target

Instacart

Shopify

eBay

Etsy

Wayfair

Chewy

Kroger

Costco

Best Buy

Home Depot

IKEA

Alibaba

Flipkart

Mercado Libre

Rakuten

Faire

Coupang

2507. Normalized Order Items for Active Products

Medium105 / 1584

Relevant companies

Amazon

Walmart

Target

Instacart

Shopify

eBay

Etsy

Wayfair

Chewy

Kroger

Costco

Best Buy

Home Depot

IKEA

Alibaba

Flipkart

Mercado Libre

Rakuten

Faire

Coupang

Sign in to Practice

Write SQL and Python, run instantly in your browser, and track your progress.

Runs live in your browser — no setup
Save progress across all 1,500+ problems
Submit and validate your answer instantly

Business Problem

You are a data engineer at Wayfair. You are responsible for building the core data models for the company's new data warehouse. Your current task is to build the fct_order_items table, which will be the source of truth for all sales analysis.

This requires joining the raw order_items and products tables, and then cleaning and standardizing the data. The final table should only contain items from active products, with valid prices and quantities, and exclude any items that were canceled or returned.

Your query should produce this clean, standardized feed, sorted by product category.

Schema: order_items

Column Name	Type
order_item_id

Business Problem

Your query should produce this clean, standardized feed, sorted by product category.

Schema: order_items

Column Name	Type
order_item_id

quantity

unit_price

Normalized Order Items for Active Products — SQL Medium | Let's Data Science | Let's Data Science

Column Name	Type
product_id	INTEGER
sku	TEXT
product_name	TEXT
category	TEXT
price	REAL
currency	TEXT

Column Name	Type
product_id	INTEGER
sku	TEXT
product_name	TEXT
category	TEXT
price	REAL
currency	TEXT

order_item_id	normalized_sku	category_upper	fulfillment_upper	line_cents
8	SKU10019	APPAREL	SHIPPED	15675
44	SKU10046	APPAREL	ALLOCATED	59844
46	SKU10046	APPAREL	DELIVERED	45426

order_item_id	normalized_sku	category_upper	fulfillment_upper	line_cents
8	SKU10019	APPAREL	SHIPPED	15675
44	SKU10046	APPAREL	ALLOCATED	59844
46	SKU10046	APPAREL	DELIVERED	45426

2507. Normalized Order Items for Active Products

Relevant companies

Tags

2507. Normalized Order Items for Active Products

Relevant companies

Tags

Sign in to Practice