Q1_easy.sql

SELECT
	t6.quest,
	t6.conv_proh
FROM
	(
	SELECT
		t5.quest,
		t5.cnt_game,
		t5.cnt_start,
		(t5.cnt_start*1.0 / t5.cnt_game*1.0)*100 as conv_game,
		t5.cnt_finish,
		(t5.cnt_finish*1.0 / t5.cnt_start*1.0)*100 as conv_proh
	FROM
		(
		SELECT
			t3.quest,
			t3.cnt_game,
			t3.cnt_start,
			t4.cnt_finish
		FROM
			(
			SELECT 
				t1.quest,
				t1.cnt_game,
				t2.cnt_start
			FROM
				(
				SELECT 
					game.quest_rk as quest,
					count(game.quest_rk) as cnt_game
				FROM
					msu_special.game
				GROUP BY 
					game.quest_rk
				) as t1
				LEFT JOIN
				(
				SELECT 
					game.quest_rk as quest,
					count(game.game_flg) as cnt_start
				FROM
					msu_special.game
				WHERE 
					game.game_flg = 1
				GROUP BY
					game.quest_rk
				) as t2
				ON
					t1.quest = t2.quest
			) as t3
			LEFT JOIN
			(
			SELECT 
				game.quest_rk as quest,
				count(game.finish_flg) as cnt_finish
			FROM
				msu_special.game
			WHERE 
				game.finish_flg = 1
			GROUP BY
				game.quest_rk
			) as t4
			ON 
				t3.quest = t4.quest
		) as t5
	) as t6
ORDER BY conv_proh DESC
LIMIT 1
;

Q1_hard.sql

SELECT
	t6.quest,
	t6.conv_proh
FROM
	(
	SELECT
		t5.quest,
		t5.cnt_game,
		t5.cnt_start,
		(t5.cnt_start*1.0 / t5.cnt_game*1.0)*100 as conv_game,
		t5.cnt_finish,
		(t5.cnt_finish*1.0 / t5.cnt_start*1.0)*100 as conv_proh
	FROM
		(
		SELECT
			t3.quest,
			t3.cnt_game,
			t3.cnt_start,
			t4.cnt_finish
		FROM
			(
			SELECT 
				t1.quest,
				t1.cnt_game,
				t2.cnt_start
			FROM
				(
				SELECT 
					game.quest_rk as quest,
					count(game.quest_rk) as cnt_game
				FROM
					msu_special.game
				GROUP BY 
					game.quest_rk
				) as t1
				LEFT JOIN
				(
				SELECT 
					game.quest_rk as quest,
					count(game.game_flg) as cnt_start
				FROM
					msu_special.game
				WHERE 
					game.game_flg = 1
				GROUP BY
					game.quest_rk
				) as t2
				ON
					t1.quest = t2.quest
			) as t3
			LEFT JOIN
			(
			SELECT 
				game.quest_rk as quest,
				count(game.finish_flg) as cnt_finish
			FROM
				msu_special.game
			WHERE 
				game.finish_flg = 1
			GROUP BY
				game.quest_rk
			) as t4
			ON 
				t3.quest = t4.quest
		) as t5
	) as t6
ORDER BY conv_proh ASC
LIMIT 1
;

Q2_not_popular.sql

SELECT
	t6.quest,
	t6.conv_game
FROM
	(
	SELECT
		t5.quest,
		t5.cnt_game,
		t5.cnt_start,
		(t5.cnt_start*1.0 / t5.cnt_game*1.0)*100 as conv_game,
		t5.cnt_finish,
		(t5.cnt_finish*1.0 / t5.cnt_start*1.0)*100 as conv_proh
	FROM
		(
		SELECT
			t3.quest,
			t3.cnt_game,
			t3.cnt_start,
			t4.cnt_finish
		FROM
			(
			SELECT 
				t1.quest,
				t1.cnt_game,
				t2.cnt_start
			FROM
				(
				SELECT 
					game.quest_rk as quest,
					count(game.quest_rk) as cnt_game
				FROM
					msu_special.game
				GROUP BY 
					game.quest_rk
				) as t1
				LEFT JOIN
				(
				SELECT 
					game.quest_rk as quest,
					count(game.game_flg) as cnt_start
				FROM
					msu_special.game
				WHERE 
					game.game_flg = 1
				GROUP BY
					game.quest_rk
				) as t2
				ON
					t1.quest = t2.quest
			) as t3
			LEFT JOIN
			(
			SELECT 
				game.quest_rk as quest,
				count(game.finish_flg) as cnt_finish
			FROM
				msu_special.game
			WHERE 
				game.finish_flg = 1
			GROUP BY
				game.quest_rk
			) as t4
			ON 
				t3.quest = t4.quest
		) as t5
	) as t6
ORDER BY conv_game ASC
LIMIT 1
;

Q2_popular.sql

SELECT
	t6.quest,
	t6.conv_game
FROM
	(
	SELECT
		t5.quest,
		t5.cnt_game,
		t5.cnt_start,
		(t5.cnt_start*1.0 / t5.cnt_game*1.0)*100 as conv_game,
		t5.cnt_finish,
		(t5.cnt_finish*1.0 / t5.cnt_start*1.0)*100 as conv_proh
	FROM
		(
		SELECT
			t3.quest,
			t3.cnt_game,
			t3.cnt_start,
			t4.cnt_finish
		FROM
			(
			SELECT 
				t1.quest,
				t1.cnt_game,
				t2.cnt_start
			FROM
				(
				SELECT 
					game.quest_rk as quest,
					count(game.quest_rk) as cnt_game
				FROM
					msu_special.game
				GROUP BY 
					game.quest_rk
				) as t1
				LEFT JOIN
				(
				SELECT 
					game.quest_rk as quest,
					count(game.game_flg) as cnt_start
				FROM
					msu_special.game
				WHERE 
					game.game_flg = 1
				GROUP BY
					game.quest_rk
				) as t2
				ON
					t1.quest = t2.quest
			) as t3
			LEFT JOIN
			(
			SELECT 
				game.quest_rk as quest,
				count(game.finish_flg) as cnt_finish
			FROM
				msu_special.game
			WHERE 
				game.finish_flg = 1
			GROUP BY
				game.quest_rk
			) as t4
			ON 
				t3.quest = t4.quest
		) as t5
	) as t6
ORDER BY conv_game DESC
LIMIT 1
;

table.sql

SELECT
	t5.quest,
	t5.cnt_game,
	t5.cnt_start,
	(t5.cnt_start*1.0 / t5.cnt_game*1.0)*100 as conv_game,
	t5.cnt_finish,
	(t5.cnt_finish*1.0 / t5.cnt_start*1.0)*100 as conv_proh
FROM
	(
	SELECT
		t3.quest,
		t3.cnt_game,
		t3.cnt_start,
		t4.cnt_finish
	FROM
		(
		SELECT 
			t1.quest,
			t1.cnt_game,
			t2.cnt_start
		FROM
			(
			SELECT 
				game.quest_rk as quest,
				count(game.quest_rk) as cnt_game
			FROM
				msu_special.game
			GROUP BY 
				game.quest_rk
			) as t1
			LEFT JOIN
			(
			SELECT 
				game.quest_rk as quest,
				count(game.game_flg) as cnt_start
			FROM
				msu_special.game
			WHERE 
				game.game_flg = 1
			GROUP BY
				game.quest_rk
			) as t2
			ON
				t1.quest = t2.quest
		) as t3
		LEFT JOIN
		(
		SELECT 
			game.quest_rk as quest,
			count(game.finish_flg) as cnt_finish
		FROM
			msu_special.game
		WHERE 
			game.finish_flg = 1
		GROUP BY
			game.quest_rk
		) as t4
		ON 
			t3.quest = t4.quest
	) as t5
;