Help compose 2 SQL queries

Question

MySQL

There is a table with three columns:
Company - Customer - Product
Conditions:
- one customer can be associated with only one company
- A customer can have several products
- A company can have several customers
- association of companies with products is not considered
Accordingly, the records look something like this:

company_1 | customer_1 | item_1
company_1 | customer_1 | item_2
company_1 | customer_1 | item_3
company_1 | customer_2 | item_2
company_2 | customer_3 | item_1
company_2 | customer_4 | item_3
company_2 | customer_3 | item_2

Task:
1. to build the TOP-100 companies by the number of buyers who bought more than 10 different products. Records in the table to the fig.
2. having this or that value of the company, find out its place in this rating

Answer 1

SELECT  `company` , customer, COUNT(  `item` ) AS items
FROM  `test` 
GROUP BY company
HAVING COUNT(  `item` ) >10
ORDER BY COUNT(  `customer` ) DESC 

I would upload it to a separate table and there I would already look for the rating of the right one. Though it is possible to test a calculated field in this request to squeeze.

Answer 2

-- checked on 4M rows

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company` int(11) NOT NULL,
  `customer` int(11) NOT NULL,
  `item` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `search` (`company`,`customer`,`item`)
) ENGINE=MyISAM AUTO_INCREMENT=4000001 DEFAULT CHARSET=utf8;

-- 1) ~ 2.2sec
SELECT company, count(customer) as `customers_count`
FROM (SELECT customer, company FROM test GROUP BY customer HAVING COUNT(DISTINCT item) > 5) as `temp`
GROUP BY company
ORDER BY customers_count DESC
LIMIT 100

-- 2) ~2.2sec

SET @rank = 0;
SELECT rank, company FROM (
  SELECT @rank := @rank + 1 as `rank`, company FROM (
    SELECT company, count(customer) as `customers_count`
    FROM (SELECT customer, company FROM test GROUP BY customer HAVING COUNT(DISTINCT item) > 5) as `temp`
    GROUP BY company
    ORDER BY customers_count DESC
    LIMIT 100
  ) as `temp2`
) as `temp3`
WHERE company = 159

Answer 3

You need to make heavy use of the GROUP BY and DISTRINCT operators. A little later I will write a more detailed solution.