How to get to my answer after author of question voluntarily removed it

Question

I put quite an effort into an answer to a question that was not very clear. Also helped him to clarify and simplify. I think I even answered it for the most part and added information on why and how. Nobody else even tried. That's why the author could still delete his question.

That's what he did today: withdraw his question, and posted a new version. The new version is clearer, he picked up a couple of my points and added more information. He is new to the site, so I do not assume any evil intent, he just wasn't sure how to do this.

Well, I am still rather new myself. So I wonder: is there a way to get to my deleted answer. All the work that has gone into it .. and it should cover most of what is needed for the question. I have studied a couple of similar posts, but there does not seem to be a way for a low-privilege user.

I think everybody should be able to see their own questions, answers and comments. Especially if there was nothing wrong with them to begin with! When postings get deleted, they should be moved to a private area of the author. Maybe with the exception of offensive stuff and spam.

The new question:
https://stackoverflow.com/questions/7492396/how-to-group-sub-queries

The removed question:
https://stackoverflow.com/questions/7478729/how-to-group-multiple-queries-by-similar-columns/7479053

Answer 1

It's not possible for you to retrieve your answer by yourself*. Since I'm 10k, I've copied your post for you:

Slightly confusing, you are right there. I am also not sure, why you computed a and b in separate queries as the WHERE clause is the same. From what you say and what I guess, this might be close to a solution:

Disclaimer: I have not actually run the query, don't know the table structure and data, so this is untested.

WITH myids AS (
  SELECT m.thread_id 
    FROM jivedw_message m
    JOIN jivedw_container c USING (container_id)    -- these ..
   WHERE c.creation_ts > '2009-11-19'::date         -- .. lines my

be redundant. GROUP BY 1 HAVING COUNT(m.message_id) = 1)

SELECT c.container_id
      ,a.a AS "Total Number of Un-Replied-To Threads"
      ,a.b AS "Average Age"
      ,b.c AS "1 Day"
  FROM jivedw_container c
  LEFT JOIN (
  SELECT m.container_id
          ,count(m.thread_id) AS a
          ,avg(date_part('days', current_date-99 - m.creation_ts)) AS b
    FROM jivedw_message m
    JOIN myids USING (thread_id)
   GROUP BY 1) a USING (container_id)
  LEFT JOIN (
  SELECT m.container_id
          ,count(m.thread_id) AS c
    FROM jivedw_message m
    JOIN myids USING (thread_id)
   WHERE date_part('days', current_date-99 - m.creation_ts) = 1
   GROUP BY 1) b USING (container_id)
 WHERE c.creation_ts > '2009-11-19'::date
 ORDER BY 1;

Among other things ..

• aliases to simplify code
• remove unnecessary brackets to simplify code
• CTE (WITH clause) for the repeated use -> simpler code, faster
• lumped a & b together as they have identical WHERE clause
• JOIN .. USING -> simpler code
• LEFT JOIN .. so we still get a result if one value is missing
• use ISO 8006 date format to make it independent of DateStyle

---

* Actually, if you know the post ID then you can go to the edit page, which still works. I can see the deleted question so I can tell you that you can still see the content of your post at https://stackoverflow.com/posts/7479053/edit: