Skip to content

Instantly share code, notes, and snippets.

@accentinteractive
Created July 11, 2020 07:43
Show Gist options
  • Save accentinteractive/ef466117f593695f1e29610766134e49 to your computer and use it in GitHub Desktop.
Save accentinteractive/ef466117f593695f1e29610766134e49 to your computer and use it in GitHub Desktop.
Sometimes a manufacturer issues a recall, for instance for a polluted food product. You can run this query to quickly retrieve all customers that bought and succesfully paid for a particular product in a certain period.
--
-- RECALL ACTION QUERY
--
-- Return a list of customers,
-- both logged in and not,
-- that purchased product X,
-- between dates Y and Z,
-- with an order status that indicates their payment was received.
SET @date_from = '2020-01-01';
SET @date_to = '2020-02-01';
SET @sku = '18292302';
SELECT customer_email, customer_prefix, customer_firstname, customer_middlename, customer_lastname, state, sales_order.created_at FROM sales_order
WHERE state IN ('processing', 'complete')
AND entity_id IN (
SELECT order_id FROM sales_order_item
WHERE created_at BETWEEN @date_from AND @date_to
AND sku = @sku
)
GROUP BY customer_email;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment