From Buff Gibbon, 5 Months ago, written in Plain Text, viewed 81 times.
URL http://codebin.org/view/2ab44d9d Embed
Download Paste or View Raw
  1. WITH
  2. post AS (SELECT pos.id AS pos_id,
  3.                 pos.user_id AS usr_id,
  4.                  DATE_TRUNC('month', pos.creation_date)::date AS mon
  5.          FROM stackoverflow.posts AS pos
  6.          JOIN (SELECT id,
  7.                creation_date
  8.      FROM stackoverflow.users) AS us ON us.id=pos.user_id
  9.           WHERE DATE_TRUNC('month', us.creation_date)::date BETWEEN '2008-09-01' AND '2008-09-30'
  10.            AND (pos.id IS NOT NULL AND EXTRACT(month FROM pos.creation_date)='12')),
  11. posts AS (SELECT DISTINCT user_id,
  12.            DATE_TRUNC('month', creation_date)::date AS pos_month
  13.            FROM stackoverflow.posts)    
  14. SELECT COUNT(DISTINCT post.pos_id) AS cnt_post,
  15.              EXTRACT(MONTH FROM posts.pos_month) AS month
  16. FROM posts JOIN post ON post.usr_id=posts.user_id
  17. GROUP BY month
  18. ORDER by month DESC

Reply to "Untitled"

Here you can reply to the paste above