Magento Best Sellers - Configurable Products

configurable_best_sellers.sql

SELECT
	cpf.entity_id,
	cpf.name,
	cpf.type_id,
	(
		SELECT SUM(sbam.qty_ordered)
		FROM sales_bestsellers_aggregated_monthly sbam
		LEFT JOIN catalog_product_super_link cpsl
			ON cpsl.product_id = sbam.product_id
		WHERE sbam.product_id = cpf.entity_id
		OR cpsl.parent_id = cpf.entity_id
	) qty_ordered
FROM catalog_product_flat_1 cpf
INNER JOIN catalog_category_product_index ccpi
	ON ccpi.product_id = cpf.entity_id
	AND ccpi.store_id = 1
	AND ccpi.visibility IN (2, 4)
ORDER BY qty_ordered DESC;

catalog_product_relation might actually be the best bet

Here's the query we want using catalog_product_relation

SELECT 
    cpf.entity_id,
    cpr.child_id,
    cpf.name,
    cpf.type_id,
    SUM(sbam.qty_ordered) simple_qty_ordered,
    SUM(sbam2.qty_ordered) related_qty_ordered,
    IF(SUM(sbam.qty_ordered) IS NOT NULL, SUM(sbam.qty_ordered), SUM(sbam2.qty_ordered)) total_qty_ordered
FROM catalog_product_flat_1 cpf
LEFT JOIN sales_bestsellers_aggregated_monthly sbam
    ON cpf.entity_id = sbam.product_id
LEFT JOIN catalog_product_relation cpr
    ON cpr.parent_id = cpf.entity_id
LEFT JOIN sales_bestsellers_aggregated_monthly sbam2
    ON cpr.child_id = sbam2.product_id
WHERE sbam.store_id = 0 OR sbam.store_id IS NULL
GROUP BY cpf.entity_id
ORDER BY total_qty_ordered DESC;

If we don't need to add orders of child simple products we wind up with this...

SELECT 
    cpf.entity_id,
    cpf.name,
    cpf.type_id,
    SUM(sbam.qty_ordered) simple_qty_ordered
FROM catalog_product_flat_1 cpf
LEFT JOIN sales_bestsellers_aggregated_monthly sbam
    ON cpf.entity_id = sbam.product_id
WHERE sbam.store_id = 0 OR sbam.store_id IS NULL
GROUP BY cpf.entity_id
ORDER BY simple_qty_ordered DESC;