Get Top 10 Products for Every Category

Get top 10 products for every category

There are probably reasons not to use analytical functions, but using analytical functions alone:

select am, rf, rfm, rownum_rf2, rownum_rfm
from
(
    -- the 3nd level takes the subproduct ranks, and for each equally ranked
    -- subproduct, it produces the product ranking
    select am, rf, rfm, rownum_rfm,
      row_number() over (partition by rownum_rfm order by rownum_rf) rownum_rf2
    from
    (
        -- the 2nd level ranks (without ties) the products within
        -- categories, and subproducts within products simultaneosly
        select am, rf, rfm,
          row_number() over (partition by am order by count_rf desc) rownum_rf,
          row_number() over (partition by am, rf order by count_rfm desc) rownum_rfm
        from
        (
            -- inner most query counts the records by subproduct
            -- using regular group-by. at the same time, it uses
            -- the analytical sum() over to get the counts by product
            select tg.am, ttc.rf, ttc.rfm,
              count(*) count_rfm,
              sum(count(*)) over (partition by tg.am, ttc.rf) count_rf
            from tg inner join ttc on tg.value = ttc.value
            group by tg.am, ttc.rf, ttc.rfm
        ) X
    ) Y
    -- at level 3, we drop all but the top 5 subproducts per product
    where rownum_rfm <= 5   -- top  5 subproducts
) Z
-- the filter on the final query retains only the top 10 products
where rownum_rf2 <= 10  -- top 10 products
order by am, rownum_rf2, rownum_rfm;

I used rownum instead of rank so you don't ever get ties, or in other words, ties will be randomly decided. This also doesn't work if the data is not dense enough (less than 5 subproducts in any of the top 10 products - it may show subproducts from some other products instead). But if the data is dense (large established database), the query should work fine.

---

The below makes two passes of the data, but returns correct results in each case. Again, this is a rank-without-ties query.

select am, rf, rfm, count_rf, count_rfm, rownum_rf, rownum_rfm
from
(
    -- next join the top 10 products to the data again to get
    -- the subproduct counts
    select tg.am, tg.rf, ttc.rfm, tg.count_rf, tg.rownum_rf, count(*) count_rfm,
        ROW_NUMBER() over (partition by tg.am, tg.rf order by 1 desc) rownum_rfm
    from (
        -- first rank all the products
        select tg.am, tg.value, ttc.rf, count(*) count_rf,
            ROW_NUMBER() over (order by 1 desc) rownum_rf
        from tg
        inner join ttc on tg.value = ttc.value
        group by tg.am, tg.value, ttc.rf
        order by count_rf desc
        ) tg
    inner join ttc on tg.value = ttc.value and tg.rf = ttc.rf
    -- filter the inner query for the top 10 products only
    where rownum_rf <= 10
    group by tg.am, tg.rf, ttc.rfm, tg.count_rf, tg.rownum_rf
) X
-- filter where the subproduct rank is in top 5
where rownum_rfm <= 5
order by am, rownum_rf, rownum_rfm;

columns:

count_rf : count of sales by product
count_rfm : count of sales by subproduct
rownum_rf : product rank within category (rownumber - without ties)
rownum_rfm : subproduct rank within product (without ties)

Finding the 3 top selling products in each category

You can join the tables and aggregate to get the total sales for each product.

Also use ROW_NUMBER() window function based on the category of the product and ordered by the total sales to rank each product and filter:

SELECT id, category, sales
FROM (
  SELECT p.id, 
         MAX(category) category, 
         SUM(o.quantity) sales,
         ROW_NUMBER() OVER (PARTITION BY MAX(category) ORDER BY SUM(o.quantity) DESC) rn
  FROM Products p INNER JOIN OrderItems o
  ON o.prodID = p.id
  GROUP BY p.id
) t
WHERE rn <= 3
ORDER BY id;

Or, aggregate first in OrderItems and then join:

SELECT id, category, sales
FROM (
  SELECT p.id, 
         p.category, 
         o.sales,
         ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY o.sales DESC) rn
  FROM Products p 
  INNER JOIN (
    SELECT prodID, SUM(quantity) sales
    FROM OrderItems
    GROUP BY prodID
  ) o ON o.prodID = p.id
) t
WHERE rn <= 3
ORDER BY id;

See the demo.

How to write SQL Query (Get all Categories and get 10 Products on each Category)

A simple way is a lateral join:

SELECT *
FROM categories c LEFT JOIN LATERAL
     (SELECT p.*
      FROM products p
      WHERE c.id = p.category_id
      ORDER_BY p.price
      LIMIT 10
     ) p
     ON 1 = 1

How to SELECT top 10 products most sells?

If you are using Oracle 12c, you can use the row limiting clause, something like

SELECT   codigo_orden AS ORDER_ID, COUNT(codigo_producto) AS PRODUCTS_SOLD
FROM     cs_items
GROUP BY codigo_orden
ORDER BY 2 DESC
FETCH FIRST 10 ROWS ONLY

If you are not using 12c, then you can use a window function, for example

select order_id, products_sold
from (
    SELECT   codigo_orden AS ORDER_ID, 
             COUNT(codigo_producto) AS PRODUCTS_SOLD,
             rank() over (order by count(codigo_producto) ) as rnk
    FROM     cs_items
    GROUP BY codigo_orden
)
where rnk <= 10
order by products_sold

You might need to use dense_rank() vs rank(), depending on how you want to handle ties.

Query to display top product for each category in MySQL

To get total sales per category along with top product you can use correlated/dependent sub query as

select t.category, 
       sum(t.sale) sales,
        (select product 
        from demo 
        where  category = t.category
        group by product
        order by count(*) desc
        limit 1) top_product
from demo t
group by t.category

Demo

MySQL Select top 10 items for each category

Does this work?

SELECT 
    yourtable.* 
FROM 
    yourtable 
    JOIN (
        SELECT 
            t1.name, 
            t1.value, 
            COUNT(t2.name) AS theCount 
        FROM yourtable t1 
        LEFT JOIN yourtable t2 ON t1.name = t2.name AND t1.value > t2.value 
        WHERE t1.name in ('a', 'b')
        GROUP BY t1.name, t1.value 
        HAVING theCount < 2
    ) AS dt USING (name, value);

Source: http://thenoyes.com/littlenoise/?p=36

Select top N most frequently purchased items in each category

Use row_number() and group by:

SELECT category, item, freq
FROM (SELECT category, item, COUNT(*) AS freq,
             ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(*) DESC) as seqnum
      FROM mytable 
      GROUP BY category, item
     ) ci
WHERE seqnum = 1;

This returns one row per category, even when there are ties for the most common. If you want all possibilities in the case of ties, use rank() instead of row_number().

---

Related Topics