I wanted to get an abandoned carts report in Magento the other day. What I wanted to see was what SKUs were in carts and how many of each SKU. A long search turned up nothing of use, so I went straight to the database and started typing SQL. In the end this is what I got and it seems reasonably corrrect, if my assumptions are also correct.
SUM (item.qty) AS total_qty,
FROM sales_flat_quote AS quote,
sales_flat_quote_item AS item
WHERE quote.entity_id = item.quote_id
AND quote.is_active = 1
AND quote.updated_at > date_add(now(), interval - 30 day )
GROUP BY item.sku
ORDER BY total_qty DESC
INTO OUTFILE '/tmp/cart_products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
This will create a CSV of the results at /tmp/cart_products.csv. Hope this is useful for others.