MySQL query for lowest price for majority of prices in table

Question

I'm trying to build a query where by I can extract the lowest price in the table, where the price is at least 10% or more of all the prices in the table.

I understand I can use MIN(price) to get the bottom rate in the table. However, I want to use the query to display a form price to the user which represents the lowest majority price.

This means it's fair, and demonstrates to the user that it's a realistic from price. As oppose to one product that might only be available once, and the likelihood is they'll never get that rate.

The price table is simple.

product_id, price.

It has thousands of rates, and I feel putting the lowest rate using MIN is misleading.

I'm really looking for some example queries of how others might tackle this. Is it possible in a single query?

Answer 1

Here we use rank() over partition by in a cte to number the records of each productID from lowest to highest. We thank take the lowest price where the rank number is higher than one tenth of the number of records for that article. If there are less than 10 records it will be the lowest price.
Below I have put the query from the CTE on it's own to show how it works. Finally there is the link to DBfiddle for the test schema.

with cte as
(select
  productid,
  price,
  rank() over (partition by productid
       order by price asc) as rn,
  count(productid) over (partition
       by productid) cn
from products)
select
  productid,
  min(price) low10
from cte
where
rn > cn/10
group by productid;

productid | low10
--------: | ----:
        1 |    24
        2 |    42
        3 |    56
        4 |    66
        5 |    99

select
  productid,
  price,
  rank() over (partition by productid
       order by price asc) as rn,
  count(productid) over (partition 
        by productid) as cn
from products
order by productid,price;

productid | price | rn | cn
--------: | ----: | -: | -:
        1 |    24 |  1 |  9
        1 |    32 |  2 |  9
        1 |    43 |  3 |  9
        1 |    45 |  4 |  9
        1 |    56 |  5 |  9
        1 |    56 |  5 |  9
        1 |    56 |  5 |  9
        1 |    85 |  8 |  9
        1 |    87 |  9 |  9
        2 |    34 |  1 | 11
        2 |    42 |  2 | 11
        2 |    44 |  3 | 11
        2 |    47 |  4 | 11
        2 |    55 |  5 | 11
        2 |    56 |  6 | 11
        2 |    57 |  7 | 11
        2 |    67 |  8 | 11
        2 |    74 |  9 | 11
        2 |    75 | 10 | 11
        2 |    87 | 11 | 11
        3 |    56 |  1 |  3
        3 |    67 |  2 |  3
        3 |    77 |  3 |  3
        4 |    66 |  1 |  1
        5 |    99 |  1 |  1

db<>fiddle here