Analysis of the completeness of the availability of products from the ClickHouse SQL database

Documentation: https://clickhouse.tech/docs/en/

Goal: 85% of the brand’s product range should be available for purchase

1. Calculate the number of products of each brand by availability (maxItems > 0)
2. Make a breakdown of brands by the availability of the assortment to purchase:

• Green: > 85%
• Yellow: 70-84%
• Red: 0-69%

1. Done:

SELECT brand, COUNT(1) AS cnt
    FROM products
        WHERE maxItems > 0
    GROUP BY brand
    ORDER BY cnt DESC;

Ok.

2. Below is an example to solve:

Each brand has an assortment, but it is not all available, but only some %. You need to calculate this percentage for each brand using the formula:

(Number of all products (id) – The number of products that are not available (maxItems = 0)) / count(id) * 100% = result %

Below is an example of the request:

SELECT brand, 
    (((SELECT COUNT(1) FROM products) -
    (SELECT COUNT(1) FROM products WHERE maxItems = 0)) / 
    (SELECT COUNT(1) FROM products) * 100) as cnt
    FROM products
        WHERE cnt > 85
    GROUP BY brand
    ORDER BY cnt DESC
    LIMIT 1000;

Result:

brand   cnt
Amorem  99.27102236131287
VALENS  99.27102236131287
FARFAL  99.27102236131287
VIAILA  99.27102236131287
4Kids   99.27102236131287

What should I fix in the code to count the percentage only by brand? Thank you.

Answer

SELECT brand, (count() - countIf(maxItems = 0)) / count() * 100 as cnt
    FROM products
    GROUP BY brand
    HAVING cnt > 85
    ORDER BY cnt DESC
    LIMIT 1000;