What are the names of the establishments that met all the required standards for 4 consecutive years?
establishment has the same meaning as business; score of 90 or more refers to score ≥ 90; year(date) = 2015; ; met all required standards for 4 consecutive years refers to COUNT(year(date)) = 4 where score = 100;
SELECT DISTINCT T4.name FROM ( SELECT T3.name, T3.years, row_number() OVER (PARTITION BY T3.name ORDER BY T3.years) AS rowNumber FROM ( SELECT DISTINCT name, STRFTIME('%Y', `date`) AS years FROM inspections AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id WHERE T1.score = 100 ) AS T3 ) AS T4 GROUP BY T4.name, date(T4.years || '-01-01', '-' || (T4.rowNumber - 1) || ' years') HAVING COUNT(T4.years) = 4
CREATE TABLE `businesses` (
  `business_id` INTEGER NOT NULL,
  `name` TEXT NOT NULL,
  `address` TEXT DEFAULT NULL,
  `city` TEXT DEFAULT NULL,
  `postal_code` TEXT DEFAULT NULL,
  `latitude` REAL DEFAULT NULL,
  `longitude` REAL DEFAULT NULL,
  `phone_number` INTEGER DEFAULT NULL,
  `tax_code` TEXT DEFAULT NULL,
  `business_certificate` INTEGER NOT NULL,
  `application_date` DATE DEFAULT NULL,
  `owner_name` TEXT NOT NULL,
  `owner_address` TEXT DEFAULT NULL,
  `owner_city` TEXT DEFAULT NULL,
  `owner_state` TEXT DEFAULT NULL,
  `owner_zip` TEXT DEFAULT NULL,
  PRIMARY KEY (`business_id`)
);
CREATE TABLE `inspections` (
  `business_id` INTEGER NOT NULL,
  `score` INTEGER DEFAULT NULL,
  `date` DATE NOT NULL,
  `type` TEXT NOT NULL,
  FOREIGN KEY (`business_id`) REFERENCES `businesses` (`business_id`)
);
CREATE TABLE `violations` (
  `business_id` INTEGER NOT NULL,
  `date` DATE NOT NULL,
  `violation_type_id` TEXT NOT NULL,
  `risk_category` TEXT NOT NULL,
  `description` TEXT NOT NULL,
  FOREIGN KEY (`business_id`) REFERENCES `businesses` (`business_id`)
);
