DOTS queries

qq.sql

SELECT COUNT(*) FROM dots.`2018_users`;

SELECT COUNT(*) FROM dots.`2018_problems`;

SELECT COUNT(*) FROM dots.`2018_solutions`;

SELECT COUNT(*) FROM dots.`2018_messages`;

SELECT * FROM dots.`2018_problems` WHERE complexity > 1;



SELECT
  contest_id,
  COUNT(user_id) as contenders_count
FROM
	dots.`2018_contest_users`
GROUP BY
	contest_id
ORDER BY
	contenders_count DESC
LIMIT 10;




SELECT
	contest_id,
	COUNT(problem_id) as problems_count
FROM
	dots.`2018_contest_problems`
GROUP BY
	contest_id
ORDER BY
	problems_count DESC;




SELECT
	MIN(s.problems_count),
	MAX(s.problems_count),
	AVG(s.problems_count)
FROM
	(
		SELECT COUNT(problem_id) as problems_count
		FROM
			dots.`2018_contest_problems`
		GROUP BY
			contest_id) as s;

	
	
	
SELECT
	contest_id,
	COUNT(solution_id) as solutions_count
FROM
	dots.`2018_solutions`
GROUP BY
	contest_id
ORDER BY
	solutions_count DESC
LIMIT 10;




SELECT
	user_id,
	COUNT(user_id) as solutions_count
FROM
	dots.`2018_solutions`
GROUP BY
	user_id
ORDER BY
	solutions_count DESC
LIMIT 10;




SELECT
	users.nickname,
	users.FIO,
	solutions.user_id,
	COUNT(solutions.user_id) as solutions_count
FROM
	dots.`2018_solutions` AS solutions
JOIN dots.`2018_users` AS users ON
	users.user_id = solutions.user_id
GROUP BY
	user_id
ORDER BY
	solutions_count DESC
LIMIT 10;




SELECT
	lang_id,
	COUNT(solution_id) as solutions_count
FROM
	dots.`2018_solutions`
GROUP BY
	lang_id
ORDER BY
	solutions_count DESC
LIMIT 10;




SELECT
	test_result,
	COUNT(solution_id) as solutions_count
FROM
	dots.`2018_solutions`
GROUP BY
	test_result
ORDER BY
	solutions_count DESC
LIMIT 10;