Orders with Category Data

orders-with-category-data.sql

# Get the category name attribute id
SELECT attribute_id
FROM eav_attribute 
WHERE entity_type_id = 3
AND attribute_code = 'name';

# Get recent orders with category data
SELECT o.created_at
, o.entity_id
, i.sku 
, i.product_id
, cp.category_id
, c.value AS category
FROM sales_flat_order_item AS i
LEFT JOIN sales_flat_order AS o ON o.entity_id = i.order_id
LEFT JOIN catalog_category_product AS cp ON cp.product_id = i.product_id
LEFT JOIN catalog_category_entity_varchar AS c ON c.entity_id = cp.category_id AND c.attribute_id = 35
WHERE o.created_at > date_sub(now(), INTERVAL 3 MONTH)
LIMIT 1000