Magento EE SQL to get customers who purchased and newsletter subscribed

Question

I have a Magento EE 1.14.0.1 and I have access to mysql. What I am trying to do is write a query in SQL that will give me a listing of all customers first name, last name, email address AND the website ID that they created an account on (this magento install has two website stores on it) from the last 30 days who not only made a purchase on the site but also created an account AND signed up to the newsletter.

I have the current query (below) which I believe is giving me a listing of every ones email address who made a purchase. however I do not know how to alter the sql

SELECT email FROM customer_entity as ce JOIN newsletter_subscriber as ns ON ns.subscriber_email = ce.email AND ns.subscriber_status = 1 JOIN sales_flat_order as sfo ON sfo.customer_id = ce.entity_id JOIN sales_flat_order_item as sfoi ON sfo.entity_id = sfoi.order_id JOIN catalog_product_entity as cpe ON cpe.entity_id = sfoi.product_id WHERE cpe.sku !='' group by ce.email;

Answer 1

I think this meets all the requirements you mentioned, obviously you would need to decide what datestamp to compare against:

SELECT sfo.customer_firstname, sfo.customer_lastname, sfo.customer_email, cs.website_id
FROM sales_flat_order sfo
INNER JOIN core_store cs ON cs.store_id = sfo.store_id
INNER JOIN customer_entity ce ON ce.entity_id = sfo.customer_id
INNER JOIN newsletter_subscriber ns ON ns.customer_id = sfo.customer_id
WHERE sfo.created_at > '2015-03-03' AND ns.subscriber_status = 1
GROUP BY sfo.customer_id;