What is the percentage of all the trains with at least 4 cars? List the directions of the said trains.
at least 4 cars refers to trailPosi > = 4; calculation = MULTIPLY(DIVIDE(count(trailPosi > = 4 then id), count(id)), 100)
SELECT CAST(COUNT(CASE WHEN T2.trailPosi >= 4 THEN T1.id ELSE NULL END) AS REAL) * 100 / COUNT(T1.id) FROM trains AS T1 INNER JOIN ( SELECT train_id, MAX(position) AS trailPosi FROM cars GROUP BY train_id ) AS T2 ON T1.id = T2.train_id UNION ALL SELECT T1.direction FROM trains AS T1 INNER JOIN ( SELECT train_id, MAX(position) AS trailPosi FROM cars t GROUP BY train_id ) AS T2 ON T1.id = T2.train_id AND T2.trailPosi >= 4
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
);
