Untitled
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...