What code to use to get the product into the right category?

Question

Marketing

Greetings.
Tell me if anyone knows or uses such actions as adding products to the right categories under different conditions.

My situation is such that I need to force the product to automatically fall into the right category under certain conditions:

For example: Category New Arrivals (New arrival) This category will be on the main page and there should be those products that have been added to the store or have reappeared in stock in stock. You can do up to 45 days. When this period passes, the product disappears from this category.
Now I have this category done manually and it is there for a very long time, I don’t have time to edit it in time and therefore one product can be in this category for more than a year. This is inconvenient

Category Back-In-Stock (Out of stock) This category should include goods that are out of stock and out of stock. So that the client can enter this category and find his product in the general list or using the category filter and subscribe to it, when the product is back in stock, the client will receive a notification (This notification function is available). Our task is to automate this. If I know that some product has ended and I will no longer sell it, then I remove this product locally through the admin panel and it will not be displayed anywhere.
It is also important that if, for example, a product from the MASK category is out of stock, this product should also disappear from the categories where it is located. In order not to appear along with those products that are in stock when a customer looks at a catalog or a certain category. You can display this category in the menu

Category BestSeller (Top of the week) . This category should be both in the menu and on the main page. Here I want to show the product that is the best in sales for 7 days. And also every 7 days the top products change depending on the statistics.

Category Limited Availability (limited offer) All products that have a warehouse value from 1 to 3 fall into this category. And also a product leaves this category if the warehouse value is higher than 3

Category Special Offers (Special offer) This category includes all products that are at a discount. The discount is set locally through the admin panel or using a special plugin (Finale-WooCommerce Sales Countdown Timer & Discount Plugin). It is perfectly optimized for WooCommerce, and when using this plugin, discounted products will also correctly fall into this category.

Has anyone faced such issues? There is a solution?

Answer 1

normal sql query where date > now -3 days
is similar to the quantity quantity =0
order by selling limit 10
is similar to the quantity quantity <4
where discont > 0