Advertisement
Pavel_Step

Untitled

Jul 5th, 2024
101
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH answer AS (SELECT u.id AS an_user_id,
  2.                   p.id AS answer_id,
  3.                   p.parent_id AS post_id,
  4.                   u.creation_date + INTERVAL '1 months' AS first_month,
  5.                   p.creation_date AS dt_answer
  6.            FROM stackoverflow.posts AS p
  7.            LEFT OUTER JOIN stackoverflow.users AS u ON p.user_id = u.id
  8.            LEFT OUTER JOIN stackoverflow.post_types AS pt ON p.post_type_id = pt.id
  9.            WHERE pt.type = 'Answer'),
  10.  
  11.       hundred_answer AS (SELECT a.an_user_id,
  12.                                 COUNT(p.user_id) AS cnt
  13.                          FROM answer AS a
  14.                          INNER JOIN stackoverflow.posts AS p ON a.post_id = p.id
  15.                          WHERE a.dt_answer < a.first_month
  16.                          GROUP BY a.an_user_id
  17.                          HAVING COUNT(a.answer_id) > 100)
  18.  
  19. SELECT u.display_name,
  20.        ha.cnt
  21. FROM hundred_answer AS ha
  22. INNER JOIN stackoverflow.users AS u ON ha.an_user_id = u.id
  23. ORDER BY u.display_name ASC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement