Finding Product Purchase Interval

Question

Its about Finding Product Purchase Interval

Problem Statement:

You are given with following data:

• List of Products
• List of Customers
• List of Orders (Assume each order contains one product only)

To Find:

For each product and customer, find the average gap between consecutive product purchases and the average across all customers.

Expected Output:

Data model for input data and output results. Display the output on screen as follows:

+----------------+-----------+------------+
|  SN |Product   | Customer  | AvgGap(days)
+-----------------------------------------+
|  1  | iPhone   | Snehal    | 21         |
|  *  |   ***    |  ***      | *          |
|     |          |           |            |
+-----+----------+-----------+------------+

---

My Approach:

1. Create an API to collect the data from the user about list of customers, products and all the orders placed to store into DB.

2. Store it in DB using following structure something like below (Since we want output to be product driven):

      +-------------+         +----------+        +--------------+
      | Product      <----->  |  Order   | <----> |  Customer    |
      +-------------+         +----------+        +--------------+
   
       product_id               order_id             customer_id
       product_name             product_id           customer_name
       price                    customer_id          address
       etc                      date_of_order        etc
   

3. To display output, create a map as < Product, List<Customer>> productAndCustomers, iterating which would give the results.

4. Iterate through list of orders (fetched from DB) to populate the map - productAndCustomers

---

For each order -

a. Get the product details and refer it with product

b. Get the customer details and refer it with customer

c. Add this customer to List<Customer> customers and check if this exists in the productAndCustomers map for the given product (key).

1. If exists, meaning customer has already ordered the product before. Update the gap attribute in customer [* How do I calculate the gap value? *]
2. If doesn’t exist, it means customer has never ordered this product. Create new list <Customer> customers and add this customer to list and put the [key, value] combination to map productAndCustomers. d. If It doesn’ t, it means the given product itself is not present in the map productAndCustomers. Put the key product and value customers list to this map with updated gap value.

So how do I calcualte the avg gap value? Should I maintain a list of pastOrderDates in orders to calculate it? But again how do I have that since gap is specific to product and customer combination. e.g. Snehal ordered iPhone on 5th and then ordered same again on 15th. Here gap being 10 days for specific combination. So before adding order entry to DB, do I need to check the combination and then update the pastOrderDates?

Answer 1

The only intricacy here is calculating the avg gaps between the purchases. This can be dealt while inserting the data to DB by maintaining the past order dates if the order has combination of same customer and product.

Answer 2

What you're trying to do seems very inconvenient to me. A database is partly there so you don't have to have local lists of everything.

First of all, infos on how to calculate the gap a.k.a. the time difference can be found here Calculating the difference between two Java date instances.

On how to to find a solution to you problem, I'm only giving a basic outline here:

1. Join the three tables using the customer_ID and product_ID, group them by customer_ID and then by product_ID.
2. Take all groups with 2 or more elements and calculate the gaps between the purchases and save them accordingly.
3. Calculate the average for each product.