Untitled
unknown
pgsql
a year ago
6.1 kB
3
Indexable
Never
--- SELECT COUNT(id) FROM stackoverflow.posts WHERE post_type_id=1 AND (score>300 OR favorites_count >= 100) GROUP BY post_type_id; --- SELECT ROUND(AVG(t.count),0) FROM ( SELECT COUNT(id), creation_date::date FROM stackoverflow.posts WHERE post_type_id = 1 GROUP BY creation_date::date HAVING creation_date::date BETWEEN '2008-11-01' AND '2008-11-18') AS t; --- SELECT COUNT(DISTINCT u.id) FROM stackoverflow.badges AS b JOIN stackoverflow.users AS u ON b.user_id=u.id WHERE b.creation_date::date = u.creation_date::date; --- SELECT COUNT(t.id) FROM ( SELECT p.id FROM stackoverflow.posts AS p JOIN stackoverflow.votes AS v ON p.id = v.post_id JOIN stackoverflow.users AS u ON p.user_id = u.id WHERE u.display_name LIKE 'Joel Coehoorn' GROUP BY p.id HAVING COUNT(v.id)>=1) as t; --- SELECT *, ROW_NUMBER() OVER(ORDER BY id DESC) AS rank FROM stackoverflow.vote_types ORDER BY id; --- SELECT * FROM ( SELECT v.user_id, COUNT(vt.id) AS cnt FROM stackoverflow.votes AS v JOIN stackoverflow.vote_types as vt ON vt.id = v.vote_type_id WHERE vt.name LIKE 'Close' GROUP BY v.user_id ORDER BY cnt DESC LIMIT 10 ) AS t ORDER BY t.cnt DESC, t.user_id DESC; --- SELECT *, DENSE_RANK() OVER (ORDER BY t.cnt DESC) AS n FROM (SELECT COUNT(id) AS cnt, user_id FROM stackoverflow.badges WHERE creation_date::date BETWEEN '2008-11-15' AND '2008-12-15' GROUP BY 2 ORDER BY cnt DESC, user_id LIMIT 10) as t; --- WITH t AS ( SELECT ROUND(AVG(score)) AS avg_score, user_id FROM stackoverflow.posts WHERE title IS NOT NULL AND score <> 0 GROUP BY user_id ) SELECT p.title, t.user_id, p.score, t.avg_score FROM t JOIN stackoverflow.posts AS p ON t.user_id=p.user_id WHERE p.title IS NOT NULL AND p.score <> 0; --- SELECT title FROM stackoverflow.posts WHERE user_id IN ( SELECT user_id FROM stackoverflow.badges GROUP BY user_id HAVING COUNT(id) >1000 ) AND title IS NOT NULL; --- SELECT id, views, CASE WHEN views>=350 THEN 1 WHEN views<100 THEN 3 ELSE 2 END AS group FROM stackoverflow.users WHERE location LIKE '%United States%' AND views > 0; --- WITH tab AS (SELECT t.id, t.views, t.group, MAX(t.views) OVER (PARTITION BY t.group) AS max FROM (SELECT id, views, CASE WHEN views>=350 THEN 1 WHEN views<100 THEN 3 ELSE 2 END AS group FROM stackoverflow.users WHERE location LIKE '%United States%' AND views > 0 ) as t ) SELECT tab.id, tab.views, tab.group FROM tab WHERE tab.views = tab.max ORDER BY tab.views DESC, tab.id; --- SELECT *, SUM(t.cnt_id) OVER (ORDER BY t.days) as nn FROM ( SELECT EXTRACT(DAY FROM creation_date::date) AS days, COUNT(id) AS cnt_id FROM stackoverflow.users WHERE creation_date::date BETWEEN '2008-11-01' AND '2008-11-30' GROUP BY EXTRACT(DAY FROM creation_date::date) ) as t; --- WITH p AS (SELECT DISTINCT user_id, MIN(creation_date) OVER (PARTITION BY user_id) AS min_dt FROM stackoverflow.posts ) SELECT p.user_id, (p.min_dt - u.creation_date) AS diff FROM stackoverflow.users AS u JOIN p ON u.id = p.user_id; --- SELECT SUM(views_count), DATE_TRUNC('month', creation_date)::date AS mnth FROM stackoverflow.posts GROUP BY DATE_TRUNC('month', creation_date)::date ORDER BY SUM(views_count) DESC; --- SELECT u.display_name, COUNT(DISTINCT p.user_id) FROM stackoverflow.posts AS p JOIN stackoverflow.users AS u ON p.user_id = u.id JOIN stackoverflow.post_types AS pt ON pt.id = p.post_type_id WHERE p.creation_date::date BETWEEN u.creation_date::date AND (u.creation_date::date + INTERVAL '1 month') AND pt.type LIKE '%Answer%' GROUP BY u.display_name HAVING COUNT(p.id) > 100 ORDER BY u.display_name; --- WITH t AS ( SELECT u.id FROM stackoverflow.posts AS p JOIN stackoverflow.users AS u ON p.user_id = u.id WHERE DATE_TRUNC('month', u.creation_date)::date = '2008-09-01' AND DATE_TRUNC('month', p.creation_date)::date = '2008-12-01' GROUP BY u.id HAVING COUNT(p.id)>0 ) SELECT COUNT(p.id), DATE_TRUNC('month', p.creation_date)::date FROM stackoverflow.posts AS p WHERE p.user_id IN (SELECT * FROM t) AND DATE_TRUNC('year', p.creation_date)::date = '2008-01-01' GROUP BY DATE_TRUNC('month', p.creation_date)::date ORDER BY DATE_TRUNC('month', p.creation_date)::date DESC; --- SELECT user_id, creation_date, views_count, SUM(views_count) OVER (PARTITION BY user_id ORDER BY creation_date) FROM stackoverflow.posts; --- SELECT ROUND(AVG(t.cnt)) FROM ( SELECT user_id, COUNT(DISTINCT creation_date::date) AS cnt FROM stackoverflow.posts WHERE creation_date::date BETWEEN '2008-12-01' AND '2008-12-07' GROUP BY user_id ) AS t --- WITH t AS ( SELECT EXTRACT(MONTH from creation_date::date) AS month, COUNT(DISTINCT id) FROM stackoverflow.posts WHERE creation_date::date BETWEEN '2008-09-01' AND '2008-12-31' GROUP BY month ) SELECT *, ROUND(((count::numeric / LAG(count) OVER (ORDER BY month)) - 1) * 100,2) AS user_growth FROM t; --- WITH t AS ( SELECT user_id, COUNT(DISTINCT id) AS cnt FROM stackoverflow.posts GROUP BY user_id ORDER BY cnt DESC LIMIT 1), t1 AS ( SELECT p.user_id, p.creation_date, extract('week' from p.creation_date) AS week_number FROM stackoverflow.posts AS p JOIN t ON t.user_id = p.user_id WHERE DATE_TRUNC('month', p.creation_date)::date = '2008-10-01' ) SELECT DISTINCT week_number::numeric, MAX(creation_date) OVER (PARTITION BY week_number) FROM t1 ORDER BY week_number;