Untitled

From Bitty Treeshrew, 2 Months ago, written in Plain Text, viewed 47 times.
URL http://codebin.org/view/0df07ffd Embed
Download Paste or View Raw
  1. WITH temp AS (
  2.     SELECT *
  3.     FROM stackoverflow.posts
  4.     WHERE creation_date::date BETWEEN '01-12-2008' AND '07-12-2008'
  5. ),
  6.  
  7. rang AS (
  8. SELECT user_id,
  9. EXTRACT(DAY FROM creation_date) as day,
  10. DENSE_RANK() OVER (PARTITION BY user_id ORDER BY EXTRACT(DAY FROM creation_date)) AS r
  11. FROM temp
  12. ),
  13.  
  14. top AS (
  15. SELECT user_id,
  16. MAX(r) OVER (ORDER BY user_id) AS ma
  17. FROM rang
  18. ),
  19.  
  20. av AS (
  21. SELECT user_id,
  22. AVG(ma) AS a
  23. FROM top
  24. GROUP BY user_id
  25.     )
  26.    
  27. SELECT FLOOR(AVG(a))
  28. FROM av

Reply to "Untitled"

Here you can reply to the paste above