Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH answer AS (SELECT u.id AS an_user_id,
- p.id AS answer_id,
- p.parent_id AS post_id,
- u.creation_date + INTERVAL '1 months' AS first_month,
- p.creation_date AS dt_answer
- FROM stackoverflow.posts AS p
- LEFT OUTER JOIN stackoverflow.users AS u ON p.user_id = u.id
- LEFT OUTER JOIN stackoverflow.post_types AS pt ON p.post_type_id = pt.id
- WHERE pt.type = 'Answer'),
- hundred_answer AS (SELECT a.an_user_id,
- COUNT(p.user_id) AS cnt
- FROM answer AS a
- INNER JOIN stackoverflow.posts AS p ON a.post_id = p.id
- WHERE a.dt_answer < a.first_month
- GROUP BY a.an_user_id
- HAVING COUNT(a.answer_id) > 100)
- SELECT u.display_name,
- ha.cnt
- FROM hundred_answer AS ha
- INNER JOIN stackoverflow.users AS u ON ha.an_user_id = u.id
- ORDER BY u.display_name ASC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement