# 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.

1. 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.

```SELECT brand, (count() - countIf(maxItems = 0)) / count() * 100 as cnt