Untitled

 avatar
unknown
pgsql
2 years ago
1.0 kB
17
Indexable
WITH top_user AS (SELECT user_id,
                         COUNT(DISTINCT id) AS posts
                  FROM stackoverflow.posts
                  GROUP BY user_id
                  ORDER BY posts DESC
                  LIMIT 1),
                  
data_of_user AS (SELECT p.user_id,
                        p.creation_date,
                 EXTRACT('week' from p.creation_date) AS week
                 FROM stackoverflow.posts AS p
                 JOIN top_user ON top_user.user_id = p.user_id
                 WHERE p.creation_date BETWEEN '2008-10-01' AND '2008-10-31')

SELECT DISTINCT week,
       LAST_VALUE(creation_date) OVER (PARTITION BY EXTRACT ('week' FROM creation_date::date) ORDER BY creation_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM data_of_user
ORDER BY week


--(PARTITION BY EXTRACT ('week' FROM creation_date::date) ORDER BY creation_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)    
-- (PARTITION BY week))::timestamp AS last_date

--LAST_VALUE(creation_date)
--MAX(creation_date)
Editor is loading...