I am struggling to build a complex ordering algorithm for the search results page.
I would like to order my items by rating (rating count, average rating), but I only want the rating take between 60-80% of the results page. One page has 12 items. They should be distributed randomly on a page.
I want to apply simple ordering as a secondary criteria, such as
I ended up using a solution which includes a chance of not rated items to end up in the middle of rated items. The idea of the algorithm is as follows:
ORDER BY CASE WHEN rating IS NOT NULL OR RANDOM() < 0.0x THEN 1 + RANDOM()ELSE RANDOM() END DESC NULLS LAST