MySQL Query Customers Who Have Not Bought

Question

Background:

We are setting up a promotions system to give away free products to registered customers. We're trying to design a database which is flexible enough to handle multiple products, and giveaways. The requirements are that products may be given away on a drip basis on a first come basis to qualified customers.

Example:

Apple wants to give away 1000 ipads in March.

They want to give away maximum of 1 per hour.

They want to give it to customers who are in California or New York.

They want to limit how many free ipads a customer can get (limit 1 per 15 days).

Data Structure:

• Products - 1 entry per unique product. e.g. Apple iPad

• ProductGiveAways

  • ProductID: AppleIpad
  • Quantity:1000
  • StartDate: 03/01/2014
  • End Date 03/31/2014
  • CustomerState: California,NewYork
  • PurchaseLimitDays: 15

Problem:

With the above structure we are able to do a query against our customers table and find out which are qualified for the promotion.

What I cannot figure out is the best way to:

1. Query customers in California or New York (is this a good use case for a join and another table?)
2. When a customer logs in to see what free items are not available to him, how can I exclude the Apple iPad if the customer has already gotten this freebie?

In other words:

• Say amazon.com wants to show me DVDs which I have not already bought. What is the proper way to query that?

• Is the right approach to first get a list of previously bought products and then Query with a NOT clause?

Answer 1

For Laravel We Use Something Like this, i hope you can relate to this query or you can use online laravel query converter for using it in mysql ( orator )

$user_id = auth()->user()->id;

Product::where('status', 'active')
->whereNotIn('id', function($query) use ($user_id) {
$query->select('product_id')->from(new OrderProduct->getTable())
->where('user_id', $user_id)->where('status', 'delivered');
});

Answer 2

Some database design

First, when you set the CustomerState to California,NewYork you are violating the First Normal Form of database design.

So let's reorganize your domain model.

State - 1 Entry per unique state ...

Customer - 1 Entry per unique customer StateId: (California|NewYork|...) ...

Product - 1 Entry per unique product ...

ProductGiveAways - Many entries per product ProductID Quantity StartDate End Date PurchaseLimitDays ...

ProductGiveAways_State ProductGiveAwaysId StateId ...

Customer_Product - 1 Entry per bought product by customer CustomerId ProductId PurchaseDate ...

Technical issues

When you want to query custoners in California or New York, all you have to do now is :

// This is just an example, you have to change the 'California', 'New York' with their ids
SELECT * FROM Customer WHERE StateId IN ('California', 'New York')

When a customer logs in to see what free items are available to him :

// It's not an accurate sql, just an example
SELECT Product.* 
FROM Product 
JOIN ProductGiveAways ON ProductId
JOIN ProductGiveAways_State ON ProductGiveAwaysId
WHERE ProductId NOT IN (
    SELECT ProductId FROM Customer_Product JOIN ProductGiveAways ON ProductId
    WHERE CustomerId = /* the customer id */
    AND ( TO_DAYS(now()) - TO_DAYS(PurchaseDate) ) < PurchaseLimitDays
) 
AND StateId = /* customer StateId */
AND StartDate < now() < End Date // Elligible ProductGiveAways

Answer 3

Because there's not a definitive data structure defined, I'm going to use the following which you can tailor to whatever data structure you have designed yourself:

• Product
  • ProductId - INTEGER (IDENTITY and PRIMARY KEY)
  • ProductName - VARCHAR
• States
  • StateId - INTEGER (IDENTITY and PRIMARY KEY)
  • StateName - VARCHAR
• Customer
  • CustomerId - INTEGER (IDENTITY and PRIMARY KEY)
  • StateId - INTEGER (FOREIGN KEY)
• Promotion
  • PromotionId - INTEGER (IDENTITY and PRIMARY KEY)
  • ProductId - INTEGER (FOREIGN KEY)
  • Quantity - INTEGER
  • StartDate - DATETIME
  • End Date - DATETIME
  • PurchaseLimitDays - INTEGER
• PromotionState
  • PromotionId - INTEGER (FOREIGN KEY)
  • StateId - INTEGER (FOREIGN KEY)

So in answer to your questions:

Query customers in California or New York (is this a good use case for a join and another table?)

Personally I would join to a centralized state table (PromotionState) in my above example, I'm sure there's a better way but you could do a condition such as:

WHERE  
    (SELECT COUNT * FROM PromotionState x WHERE x.PromotionId = p.PromotionId) = 0
    OR NOT(ps.PromotionId IS NULL)

Alternatively you could do a GROUP BY and HAVING, using all the other columns as the items to GROUP BY and something like HAVING COUNT * = 0 OR HAVING SUM CASE WHEN (Conditions met) THEN 1 ELSE 0 END = 0

When a customer logs in to see what free items are not available to him, how can I exclude the Apple iPad if the customer has already gotten this freebie?

Say amazon.com wants to show me DVDs which I have not already bought. What is the proper way to query that?

As I've said you could use GROUP BY and HAVING to determine whether an item has been previously "won" by either using COUNT or SUM

Is the right approach to first get a list of previously bought products and then Query with a NOT clause?

There are probably better ways, sub queries can get very heavy and sluggish, I'd recommend trying some of the above techniques and then using a profiler to hopefully make it more efficient.

Answer 4

I'm assuming you'll have a table for what has been given away. In this table I would include a column for recipient id which can map back to the customer table. You can then create queries to find eligible recipients by searching for customers who have not met disqualifying conditions.

select customerid 
from customer
where customerid not in (
                          select recipientid
                          from givenaway
                          where ..... and ....
                         )