Проект_продвинутый SQL

--Задача 1

SELECT COUNT(id)
FROM stackoverflow.posts
WHERE (score>300 OR favorites_count >=100) AND post_type_id = 1

--Задача 2

WITH
cnt AS
(SELECT CAST(DATE_TRUNC('day', p.creation_date) AS date) AS date, COUNT(p.id)
FROM stackoverflow.posts p
WHERE post_type_id = 1
GROUP BY CAST(DATE_TRUNC('day', p.creation_date) AS date)
HAVING CAST(DATE_TRUNC('day', p.creation_date) AS date) BETWEEN '2008-11-01' AND '2008-11-18')

SELECT ROUND(AVG(count)) FROM cnt

--Задача 3

SELECT COUNT(DISTINCT(u.id))
FROM stackoverflow.users u
JOIN stackoverflow.badges b
ON u.id = b.user_id
WHERE CAST(DATE_TRUNC('day', u.creation_date) AS date) = CAST(DATE_TRUNC('day', b.creation_date) AS date)

--Задача 4

SELECT COUNT(DISTINCT(p.id))
FROM stackoverflow.posts p
JOIN stackoverflow.users u
ON p.user_id = u.id
RIGHT JOIN stackoverflow.votes v
ON p.id = v.post_id
WHERE u.display_name LIKE 'Joel Coehoorn'

--Задача 5

SELECT *, RANK() OVER (ORDER BY id DESC) AS rank
FROM stackoverflow.vote_types
ORDER BY id

--Задача 6

SELECT v.user_id, COUNT(v.id)
FROM stackoverflow.votes v
WHERE vote_type_id = 6
GROUP BY v.user_id
ORDER BY COUNT(v.id) DESC, v.user_id DESC
LIMIT 10

--Задача 7

SELECT b.user_id, COUNT(b.id), DENSE_RANK() OVER (ORDER BY COUNT(b.id) DESC)
FROM stackoverflow.badges b
WHERE CAST(creation_date AS date) BETWEEN '2008-11-15' AND '2008-12-15'
GROUP BY b.user_id
ORDER BY COUNT(b.id) DESC, b.user_id
LIMIT 10

--Задача 8

SELECT p.title, p.user_id, p.score,
ROUND(AVG(p.score) OVER (PARTITION BY p.user_id))
FROM stackoverflow.posts p
WHERE p.score <> 0 AND p.title IS NOT NULL

--Задача 9

SELECT p.title
FROM stackoverflow.posts p
WHERE p.user_id IN
(SELECT user_id FROM stackoverflow.badges GROUP BY user_id HAVING COUNT(id)>1000)
AND p.title IS NOT NULL

--Задача 10

SELECT id, views,
CASE
           WHEN views <100 THEN 3
           WHEN views <350 THEN 2
           WHEN views>=350 THEN 1
END
FROM stackoverflow.users
WHERE location LIKE '%United States%' AND views > 0

--Задача 11

WITH tb AS
(SELECT id, views,
CASE
           WHEN views <100 THEN 3
           WHEN views <350 THEN 2
           WHEN views>=350 THEN 1
END
FROM stackoverflow.users
WHERE location LIKE '%United States%' AND views > 0)

SELECT *
FROM tb
WHERE id IN (16587,9585,9094,33437,15079,19006,22732,403434,4829,3469)
ORDER BY views DESC, id

--Задача 12

WITH
t1 AS
(SELECT CAST(DATE_TRUNC('day', creation_date) AS date) AS dt, COUNT(id) AS val
FROM stackoverflow.users
GROUP BY CAST(DATE_TRUNC('day', creation_date) AS date)
ORDER BY CAST(DATE_TRUNC('day', creation_date) AS date))

SELECT RANK() OVER(ORDER BY dt) ,val,
SUM(val) OVER (ORDER BY dt) AS cum
FROM t1
WHERE CAST(DATE_TRUNC('day', dt) AS date) BETWEEN '2008-11-01' AND '2008-11-30'

--Задача 13

WITH p AS
(SELECT user_id, creation_date,
RANK() OVER (PARTITION BY user_id ORDER BY creation_date)  AS first_pub
FROM stackoverflow.posts

ORDER BY user_id)

SELECT user_id, p.creation_date - u.creation_date FROM p
JOIN stackoverflow.users u ON p.user_id = u.id
WHERE first_pub = 1

--## Часть 2
--### Задача 1

SELECT CAST(DATE_TRUNC('month', creation_date) AS date) AS month, SUM(views_count) AS sum
FROM stackoverflow.posts
WHERE creation_date::date BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY CAST(DATE_TRUNC('month', creation_date) AS date)
ORDER BY sum DESC

--Задача 2

SELECT display_name,
       COUNT(DISTINCT(user_id))
FROM stackoverflow.users AS u JOIN stackoverflow.posts AS p ON u.id=p.user_id
JOIN stackoverflow.post_types AS t ON p.post_type_id=t.id
WHERE (DATE_TRUNC('day', p.creation_date) <= DATE_TRUNC('day', u.creation_date) + INTERVAL '1 month') AND (p.post_type_id=2)
GROUP BY display_name
HAVING COUNT(p.id) > 100

--Задача 3

WITH
t1 AS
(SELECT u.id
FROM stackoverflow.users AS u JOIN stackoverflow.posts AS p ON u.id=p.user_id
WHERE (u.creation_date::date BETWEEN '2008-09-01' AND '2008-09-30')
AND ((p.creation_date::date BETWEEN '2008-12-01' AND '2008-12-31'))
GROUP BY u.id)

SELECT CAST(DATE_TRUNC('month', p.creation_date) AS date) AS month, COUNT(p.id) AS cnt
FROM stackoverflow.users AS u JOIN stackoverflow.posts AS p ON u.id=p.user_id
WHERE (p.creation_date::date BETWEEN '2008-01-01' AND '2008-12-31')
AND p.user_id IN (SELECT * FROM t1)
GROUP BY CAST(DATE_TRUNC('month', p.creation_date) AS date)
ORDER BY CAST(DATE_TRUNC('month', p.creation_date) AS date) DESC

--Задача 4

SELECT user_id, creation_date, views_count,
SUM(views_count) OVER (PARTITION BY user_id ORDER BY creation_date)
FROM stackoverflow.posts
ORDER BY user_id, creation_date

--Задача 5

WITH users AS (SELECT p.user_id,
      COUNT(distinct p.creation_date::date)
FROM stackoverflow.posts AS p
WHERE CAST(creation_date AS date) BETWEEN '2008-12-1' AND '2008-12-7'
GROUP BY p.user_id
HAVING COUNT(p.id)>=1)
SELECT ROUND(AVG(count))
FROM users

--Задача 6

with a AS (SELECT EXTRACT(month from creation_date) AS num, COUNT(id) AS cnt
           FROM stackoverflow.posts
           WHERE  creation_date::date BETWEEN '2008-09-01' AND '2008-12-31'
          GROUP BY 1)

          SELECT num, cnt, ROUND(((cnt::numeric/LAG(cnt) OVER (ORDER BY num))-1)*100,2)
          FROM a
--Задача 7

SELECT
DISTINCT(EXTRACT(week FROM creation_date::date)),
MAX(creation_date) OVER (ORDER BY EXTRACT(week FROM creation_date::date))
FROM stackoverflow.posts
WHERE user_id = 22656
AND creation_date::date BETWEEN '2008-10-01' AND '2008-10-31'