Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Untitled

a guest May 25th, 2018 77 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. --Сколько раз решили ту или иную задачу
  2. SELECT Task.text, tmp.cnt
  3. FROM Task
  4. LEFT JOIN (
  5.     SELECT Task.task_id AS task_id, count(*) AS cnt FROM
  6.       task
  7.       LEFT JOIN Solved_task ON Solved_task.task_id = task.task_id AND last_commit = 'OK'
  8.        GROUP BY task.task_id
  9.     ) tmp ON tmp.task_id = Task.task_id
  10. ORDER BY tmp.cnt DESC;
  11.  
  12.  
  13. --select count(*) from solved_task WHERE last_commit = 'OK';
  14.  
  15. --Общий рейтинг
  16. SELECT "User".login, coalesce(sum(Task.rating), 0) AS total
  17. FROM "User"
  18. LEFT JOIN solved_task AS st ON st.user_id = "User".user_id AND st.last_commit = 'OK'
  19. LEFT JOIN task ON st.task_id = task.task_id
  20. GROUP BY "User".login
  21. ORDER BY total DESC;
  22.  
  23. --Гавно - рейтинг по контесту не рабочий!
  24. SELECT "User".login, sum(Task.rating) AS total
  25. FROM Contest
  26. JOIN contest_task ON contest.contest_id = contest_task.contest_id AND contest_name = 'Codeforces Round #1 (рейтинговый, Div. 1, по задачам VK Cup 2018 Раунд 2)'
  27. JOIN task ON task.task_id = contest_task.task_id
  28. JOIN solved_task ON task.task_id = solved_task.task_id
  29. JOIN "User" ON "User".user_id = solved_task.user_id
  30. GROUP BY "User".login
  31. ORDER BY total DESC;
  32.  
  33. --Все посты пользователя с определенным никнеймом
  34. SELECT "User".login, Post.name
  35. FROM "User" JOIN Post ON "User".user_id = Post.creator_id
  36. WHERE "User".login = 'mnbvmar';
  37.  
  38. --Задачи по определенным критериям для тренировки
  39. SELECT Task.text, Task.samples, Task.difficulty
  40. FROM Task
  41. JOIN (
  42.     SELECT Theme.name, Task_themes.task_id
  43.     FROM Theme JOIN Task_themes ON Theme.theme_id = Task_themes.theme_id
  44.     ) tmp ON Task.task_id = tmp.task_id
  45. WHERE name = 'geometry' AND difficulty >= (5.0)
  46. ORDER BY  difficulty DESC;
  47.  
  48. --Все комментарии под определенным постом с указанием автора
  49. SELECT "User".login, t.content
  50. FROM "User" JOIN (
  51.     SELECT comment.creator_id, comment.content FROM
  52.       Post JOIN comment ON post.post_id = comment.post_id
  53.       WHERE post.name = 'Integer solution of commivoyger'
  54.   ) t ON "User".user_id = t.creator_id
  55.  
  56. --Информация о спонсорах данного контеста
  57. SELECT name, website, photo, level FROM Contest
  58.   JOIN contest_sponsors ON contest.contest_id = contest_sponsors.contest_id AND contest_name = 'Hello 2018'
  59.   JOIN sponsor ON contest_sponsors.sponsor_id = sponsor.sponsor_id
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top