Add dynamic categories to pie chart in Apache Superset?

Question

I have two matters I need help with in using superset.

the client wants to see the top 10 products from table Sales (ie: product count >300), and the rest of them displayed as "Others" (in a pie chart). How do I get the other products to be grouped into "Others", dynamically?

If they filter by one of the products (which would fall under "Others" category), they want to be able to see the data. Any ideas?

Many thanks in advance.

I tried the following query but it is not doing what I expected:

SELECT
    CASE
        WHEN t.product IN (SELECT product FROM Sales GROUP BY product HAVING COUNT(*) >= 30) THEN t.product
        ELSE 'Others'
    END AS grouped_product,
    COUNT(*) AS product_count
FROM
    Sales AS t
GROUP BY
    grouped_product;

Output: Selection of top 10 ranking products without hardcoding 300 and the rest would be in "Others". Using Maria-DB

Answer 1

You can perform aggregation to obtain the count of products and then use ROW_NUMBER() (or DENSE_RANK() in cases where values are tied) to assign a sequential ranking number within each partition :

And by employing integer division using DIV, we can pinpoint the first n products ( replace 3 with any desired number ) :

SELECT (CASE WHEN rn DIV 3 = 0 THEN product ELSE 'Others' END) AS product, 
       SUM(product_count) AS product_count
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY product_count DESC) - 1 AS rn
  FROM (
    SELECT product, COUNT(*) AS product_count
    FROM sales
    GROUP BY product
  ) AS s
) AS g
GROUP BY 1

Which for this dataset :

create table sales (
  product VARCHAR(20),
  order_id int
);

insert into sales values
('AA', 1),
('AA', 2),
('AA', 3),
('AA', 4),
('BB', 5),
('BB', 6),
('BB', 7),
('CC', 8),
('CC', 9),
('DD', 10);

Will results :

product product_count
AA      4
BB      3
CC      2
Others  1

Demo here