--Сколько раз решили ту или иную задачу
SELECT Task.text, tmp.cnt
FROM Task
LEFT JOIN (
    SELECT Task.task_id AS task_id, count(*) AS cnt FROM
      task
      LEFT JOIN Solved_task ON Solved_task.task_id = task.task_id AND last_commit = 'OK'
       GROUP BY task.task_id
    ) tmp ON tmp.task_id = Task.task_id
ORDER BY tmp.cnt DESC;


--select count(*) from solved_task WHERE last_commit = 'OK';

--Общий рейтинг
SELECT "User".login, coalesce(sum(Task.rating), 0) AS total
FROM "User"
LEFT JOIN solved_task AS st ON st.user_id = "User".user_id AND st.last_commit = 'OK'
LEFT JOIN task ON st.task_id = task.task_id
GROUP BY "User".login
ORDER BY total DESC;

--Гавно - рейтинг по контесту не рабочий!
SELECT "User".login, sum(Task.rating) AS total
FROM Contest
JOIN contest_task ON contest.contest_id = contest_task.contest_id AND contest_name = 'Codeforces Round #1 (рейтинговый, Div. 1, по задачам VK Cup 2018 Раунд 2)'
JOIN task ON task.task_id = contest_task.task_id
JOIN solved_task ON task.task_id = solved_task.task_id
JOIN "User" ON "User".user_id = solved_task.user_id
GROUP BY "User".login
ORDER BY total DESC;

--Все посты пользователя с определенным никнеймом
SELECT "User".login, Post.name
FROM "User" JOIN Post ON "User".user_id = Post.creator_id
WHERE "User".login = 'mnbvmar';

--Задачи по определенным критериям для тренировки
SELECT Task.text, Task.samples, Task.difficulty
FROM Task
JOIN (
    SELECT Theme.name, Task_themes.task_id
    FROM Theme JOIN Task_themes ON Theme.theme_id = Task_themes.theme_id
    ) tmp ON Task.task_id = tmp.task_id
WHERE name = 'geometry' AND difficulty >= (5.0)
ORDER BY  difficulty DESC;

--Все комментарии под определенным постом с указанием автора
SELECT "User".login, t.content
FROM "User" JOIN (
    SELECT comment.creator_id, comment.content FROM
      Post JOIN comment ON post.post_id = comment.post_id
      WHERE post.name = 'Integer solution of commivoyger'
  ) t ON "User".user_id = t.creator_id

--Информация о спонсорах данного контеста
SELECT name, website, photo, level FROM Contest
  JOIN contest_sponsors ON contest.contest_id = contest_sponsors.contest_id AND contest_name = 'Hello 2018'
  JOIN sponsor ON contest_sponsors.sponsor_id = sponsor.sponsor_id