Find New Buyers, and Returning Buyers from the Orders tables

Question

I have an existing E-commerce database with the rather standard sales tables. The relevant tables are:

1. Orders table. The fields are like: OrderID, CustomerID, OrderDate, ...

2. Customers table. CustoerID, CustomerFirstName, CustomerLastName, ...

I need to find two values, namely:

1. Total new buyers (within a certain time period *)

   • Basically, those are fist time buyers within a certain time period

2. Total returning buyers (within a certain time period *)

   • Basically, these are buyers who have bought before, prior to the time period

   • time period, we will provide as the inputs, such as within 1 week

My database is in MySQL.

Question:

What is the easiest and most efficient way to get the two totals? 1. Total new buyers 2. Total returning buyers

Do I need to write a program in PHP? Or I can simply use SQL statements to achieve this?

Thanks for any help.

Answer 1

This can be done purely in SQL:

Number of First time buyers:

SELECT
  COUNT(DISTINCT CustomerID) 
FROM Orders 
WHERE OrderDate BETWEEN <startdate> AND <enddate>
/* Buyers with only one order record */
AND CustomerID IN (SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*)=1)

Number of repeat buyers:

SELECT
  COUNT(DISTINCT CustomerID) 
FROM Orders 
WHERE OrderDate BETWEEN <startdate> AND <enddate>
/* Buyers with more than one order record */
AND CustomerID IN (SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*)>1)