1-schema.sql

DROP TABLE IF EXISTS company;
CREATE TABLE company (
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
rating INT(11) NOT NULL,
is_paid TINYINT(1) NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB;
 
DROP TABLE IF EXISTS company_product;
CREATE TABLE company_product (
id INT(11) NOT NULL AUTO_INCREMENT,
company_id INT(11) DEFAULT NULL,
product_id INT(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE INDEX UK_company_product (company_id, product_id)
)
ENGINE = INNODB;

2-data.sql

INSERT INTO company VALUES 
  (1, 'company 1', 12, 0),
  (2, 'company 2', 30, 0),
  (3, 'company 3', 12, 0),
  (4, 'company 4', 5, 1),
  (5, 'company 5', 5, 1);

INSERT INTO company_product VALUES 
  (1, 1, 1),
  (2, 1, 2),
  (3, 1, 3),
  (4, 2, 4),
  (5, 2, 5),
  (6, 3, 6),
  (7, 4, 7),
  (8, 4, 8),
  (9, 5, 9);

3-query.sql

SELECT
  pc.product_id, c.title, c.is_paid, c.rating
FROM company_product pc
JOIN company_product b ON pc.company_id = b.company_id AND pc.id <= b.id
JOIN company c ON pc.company_id = c.id
GROUP BY pc.id
ORDER BY c.is_paid DESC, c.rating DESC, COUNT(*), pc.id

4-result.csv

8	company 4	1	5
9	company 5	1	5
7	company 4	1	5
5	company 2	0	30
4	company 2	0	30
3	company 1	0	12
6	company 3	0	12
2	company 1	0	12
1	company 1	0	12