I have a website that sells lots of different products.
Part of the website lists the products in order of total sales.
I want to create a brand new way of ranking these products which will require rebuilding the ranking database every day.
How I want the new ranking system to work is this.
For every sale a product has made in the past 7 days it earns 1 ranking point.
For every sale made between 7-14 days ago it earns 0.8 ranking points.
Sales between 14-21 days ago earn 0.6 ranking points.
Sales between 21-28 days ago earn 0.5 ranking points.
Sales between 28-35 days ago earn 0.4 ranking points.
Sales between 35-42 days ago earn 0.3 ranking points.
Sales between 42-49 days ago earn 0.2 ranking points.
Sales between 49-56 days ago earn 0.1 ranking points.
All products that haven't made a sale the past 56 days are listed after all products that have, and in order of total sales made. The sales table already has a 'total_sales' column.
Every 24 hours the scores should be added up and the products listed in order of highest points first.
As of now these products are just listed by total sales, and can be searched by keyword and/or category.
On the results pages that display the products, I want to show the ranking points beside each product listing.