magento2 Mysql count number customers who ordered more then once in a specific date range

Question

I have a Magento 2.2.5 based website and I want to get a list of emails of customers who have made more than 2 orders within a specific date range.

I know I can do the below MySQL query to list number times ordered and how many customers. not sure how to maybe alter this MySQL query.

SELECT num_orders AS OrderCount, count(customer_id) as CustomerCount
FROM (
       SELECT
          COUNT(1) AS num_orders,
          sfo.customer_id
          FROM sales_order_grid AS sfo
          WHERE sfo.status='completed_on_sap' AND sfo.created_at BETWEEN '2018-01-01' AND '2018-12-25'
       GROUP BY sfo.customer_id
     ) AS T
GROUP BY num_orders
ORDER BY 1

Answer 1

Here's a modified version of a script I used for something similar:

SELECT
    customers.email AS Email,
    COUNT(orders.customer_id) AS OrderCount
    FROM sales_order AS orders
        INNER JOIN customer_grid_flat AS customers
            ON customers.entity_id = orders.customer_id
    WHERE orders.status = 'completed_on_sap'
        AND orders.created_at BETWEEN '2018-01-01' AND '2018-12-25'
    GROUP BY orders.customer_id
    HAVING COUNT(orders.customer_id) > 1;

First you have to group by the customer, then only show customers which have a count higher than 1.