Описание проекта:
Данный проект посвящен решению SQL-задач и анализу данных с использованием базы данных StackOverflow в системе управления базами данных PostgreSQL. StackOverflow - это популярный сервис вопросов и ответов, посвященный программированию и связанным с ним темам.
В рамках проекта будет использоваться версия базы данных, содержащая данные о постах, опубликованных на сайте в 2008 году и ранее. Проект состоит из двух основных частей, призванных продемонстрировать практическое применение SQL и аналитические возможности.
Первая часть включает в себя решение ряда задач в SQL-тренажёре. Эти задачи позволят вам применить на практике ваши знания SQL и научиться извлекать информацию из базы данных, отработать навыки написания запросов, манипуляции данными и использования различных функций и операторов SQL.
Вторая часть проекта носит аналитический характер. В этой части будет прадеставленн анализ данных StackOverflow, используя мощные инструменты SQL анализ данных, извлекать из них ценные сведения и делать выводы. Эта часть проекта позволит развить навыки анализа данных и научиться использовать SQL для получения ценной информации из больших наборов данных.
Проект предоставляет отличную возможность не только улучшить свои навыки SQL, но и получить практический опыт работы с реальной базой данных и углубить понимание анализа данных.
Цель проекта:
Продемонстрировать практическое применение SQL и аналитические возможности на реальном наборе данных. Проект направлен на решение SQL-задач и анализ данных из базы данных сервиса StackOverflow, посвященного программированию. Первая часть включает в себя решение задач в SQL-тренажёре, а вторая часть - это аналитический анализ данных с извлечением ценных сведений и выводов. Проект позволит улучшить навыки SQL, получить опыт работы с базой данных и углубить понимание анализа данных.
Описание данных
-
Таблица
stackoverflow.badgesХранит информацию о значках, которые присуждаются за разные достижения. Например, пользователь, правильно ответивший на большое количество вопросов про PostgreSQL, может получить значок postgresql.
| Поле | Описание |
|---|---|
| id | Идентификатор значка, первичный ключ таблицы |
| name | Название значка |
| user_id | Идентификатор пользователя, которому присвоили значок, внешний ключ, отсылающий к таблице users |
| creation_date | Дата присвоения значка |
Таблица stackoverflow.post_types
Содержит информацию о типе постов. Их может быть два:
Question— пост с вопросом;Answer— пост с ответом.
| Поле | Описание |
|---|---|
| id | Идентификатор типа поста, первичный ключ таблицы |
| type | Тип поста |
Таблица stackoverflow.posts
Содержит информацию о постах.
| Поле | Описание |
|---|---|
| id | Идентификатор поста, первичный ключ таблицы |
| title | Заголовок поста |
| creation_date | Дата создания поста |
| favorites_count | Число, которое показывает, сколько раз пост добавили в «Закладки» |
| last_activity_date | Дата последнего действия в посте, например комментария |
| last_edit_date | Дата последнего изменения поста |
| user_id | Идентификатор пользователя, который создал пост, внешний ключ к таблице users |
| parent_id | Если пост написали в ответ на другую публикацию, в это поле попадёт идентификатор поста с вопросом |
| post_type_id | Идентификатор типа поста, внешний ключ к таблице post_types |
| score | Количество очков, которое набрал пост |
| views_count | Количество просмотров |
Таблица stackoverflow.users
Содержит информацию о пользователях.
| Поле | Описание |
|---|---|
| id | Идентификатор пользователя, первичный ключ таблицы |
| creation_date | Дата регистрации пользователя |
| display_name | Имя пользователя |
| last_access_date | Дата последнего входа |
| location | Местоположение |
| reputation | Очки репутации, которые получают за хорошие вопросы и полезные ответы |
| views | Число просмотров профиля пользователя |
Таблица stackoverflow.vote_types
Содержит информацию о типах голосов. Голос — это метка, которую пользователи ставят посту. Типов бывает несколько:
- UpMod — такую отметку получают посты с вопросами или ответами, которые пользователи посчитали уместными и полезными.
- DownMod — такую отметку получают посты, которые показались пользователям наименее полезными.
- Close — такую метку ставят опытные пользователи сервиса, если заданный вопрос нужно доработать или он вообще не подходит для платформы.
- Offensive — такую метку могут поставить, если пользователь ответил на вопрос в грубой и оскорбительной манере, например, указав на неопытность автора поста.
- Spam — такую метку ставят в случае, если пост пользователя выглядит откровенной рекламой.
| Поле | Описание |
|---|---|
| id | Идентификатор типа голоса, первичный ключ |
| name | Название метки |
Таблица stackoverflow.votes
Содержит информацию о голосах за посты.
| Поле | Описание |
|---|---|
| id | Идентификатор голоса, первичный ключ |
| post_id | Идентификатор поста, внешний ключ к таблице posts |
| user_id | Идентификатор пользователя, который поставил посту голос, внешний ключ к таблице users |
| bounty_amount | Сумма вознаграждения, которое назначают, чтобы привлечь внимание к посту |
| vote_type_id | Идентификатор типа голоса, внешний ключ к таблице vote_types |
| creation_date | Дата назначения голоса |
Найдите количество вопросов, которые набрали больше 300 очков или как минимум 100 раз были добавлены в «Закладки».
| Code |
SELECT COUNT(id)
FROM stackoverflow.posts
WHERE (score > 300 OR favorites_count >= 100) AND post_type_id = 1; |
| Result |
|
|
Сколько в среднем в день задавали вопросов с 1 по 18 ноября 2008 включительно? Результат округлите до целого числа.
| Code |
WITH
questions AS (SELECT DISTINCT(CAST(DATE_TRUNC('day', p.creation_date) AS DATE)),
COUNT(p.id) OVER (PARTITION BY CAST(DATE_TRUNC('day', p.creation_date) AS DATE))
FROM stackoverflow.posts AS p
LEFT JOIN stackoverflow.post_types AS pt ON p.post_type_id = pt.id
WHERE pt.type = 'Question' AND (CAST(DATE_TRUNC('day', p.creation_date) AS DATE) BETWEEN '2008.11.01' AND '2008.11.18'))
SELECT ROUND(AVG(count))::int
FROM questions; |
| Result |
|
|
Сколько пользователей получили значки сразу в день регистрации? Выведите количество уникальных пользователей.
| Code |
SELECT COUNT(DISTINCT(u.id))
FROM stackoverflow.users u
LEFT JOIN stackoverflow.badges b ON u.id=b.user_id
WHERE DATE_TRUNC('day',u.creation_date)::DATE = DATE_TRUNC('day',b.creation_date)::DATE; |
| Result |
|
|
Сколько уникальных постов пользователя с именем Joel Coehoorn получили хотя бы один голос?
| Code |
WITH
name_id AS (SELECT id
FROM stackoverflow.users
WHERE display_name = 'Joel Coehoorn'),
post_votes AS (SELECT post_id
FROM stackoverflow.votes
GROUP BY post_id
HAVING COUNT(*) > 0)
SELECT COUNT(DISTINCT p.id)
FROM stackoverflow.posts p
JOIN name_id ni ON p.user_id = ni.id
JOIN post_votes pv ON p.id = pv.post_id; |
| Result |
|
|
Выгрузите все поля таблицы vote_types. Добавьте к таблице поле rank, в которое войдут номера записей в обратном порядке. Таблица должна быть отсортирована по полю id.
| Code |
SELECT *,
RANK () OVER (ORDER BY id DESC) AS rank
FROM stackoverflow.vote_types
ORDER BY id
LIMIT 5; |
| Result |
|
|
Отберите 10 пользователей, которые поставили больше всего голосов типа Close. Отобразите таблицу из двух полей: идентификатором пользователя и количеством голосов. Отсортируйте данные сначала по убыванию количества голосов, потом по убыванию значения идентификатора пользователя.
| Code |
SELECT DISTINCT(user_id),
COUNT(id) OVER(PARTITION BY user_id) AS vote
FROM stackoverflow.votes
WHERE vote_type_id = 6
ORDER BY vote DESC, user_id DESC
LIMIT 10; |
| Result |
|
|
Отберите 10 пользователей по количеству значков, полученных в период с 15 ноября по 15 декабря 2008 года включительно.
Отобразите несколько полей:
- идентификатор пользователя;
- число значков;
- место в рейтинге — чем больше значков, тем выше рейтинг.
Пользователям, которые набрали одинаковое количество значков, присвойте одно и то же место в рейтинге.
Отсортируйте записи по количеству значков по убыванию, а затем по возрастанию значения идентификатора пользователя.
| Code |
SELECT user_id,
COUNT(id),
DENSE_RANK() OVER(ORDER BY COUNT(id) DESC)
FROM stackoverflow.badges
WHERE creation_date::DATE BETWEEN '2008-11-15' AND '2008-12-15'
GROUP BY user_id
ORDER BY COUNT (id) DESC,user_id
LIMIT 10; |
| Result |
|
|
Сколько в среднем очков получает пост каждого пользователя?
Сформируйте таблицу из следующих полей:
-
заголовок поста;
-
идентификатор пользователя;
-
число очков поста;
-
среднее число очков пользователя за пост, округлённое до целого числа.
Не учитывайте посты без заголовка, а также те, что набрали ноль очков.
| Code |
SELECT title,
user_id,
score,
ROUND(AVG(score) OVER (PARTITION BY user_id))
FROM stackoverflow.posts
WHERE title IS NOT NULL AND score != 0
LIMIT 5; |
| Result |
|
|
Отобразите заголовки постов, которые были написаны пользователями, получившими более 1000 значков. Посты без заголовков не должны попасть в список.
| Code |
WITH
users AS (SELECT user_id,
COUNT(creation_date) count_badges
FROM stackoverflow.badges b
GROUP BY user_id
HAVING COUNT(creation_date) > 1000)
SELECT title
FROM stackoverflow.posts p
WHERE user_id = (SELECT user_id FROM users) AND title IS NOT NULL; |
| Result |
|
|
Напишите запрос, который выгрузит данные о пользователях из Канады (англ. Canada). Разделите пользователей на три группы в зависимости от количества просмотров их профилей:
-
пользователям с числом просмотров больше либо равным 350 присвойте группу 1;
-
пользователям с числом просмотров меньше 350, но больше либо равно 100 — группу 2;
-
пользователям с числом просмотров меньше 100 — группу 3.
Отобразите в итоговой таблице идентификатор пользователя, количество просмотров профиля и группу. Пользователи с количеством просмотров меньше либо равным нулю не должны войти в итоговую таблицу.
| Code |
SELECT id,
views,
CASE
WHEN views < 100 THEN 3
WHEN views >= 100 AND views < 350 THEN 2
ELSE 1
END AS group
FROM stackoverflow.users
WHERE location LIKE '%Canada%' AND views > 0
LIMIT 5; |
| Result |
|
|
Дополните предыдущий запрос. Отобразите лидеров каждой группы — пользователей, которые набрали максимальное число просмотров в своей группе. Выведите поля с идентификатором пользователя, группой и количеством просмотров. Отсортируйте таблицу по убыванию просмотров, а затем по возрастанию значения идентификатора.
| Code |
WITH
data AS (SELECT id,
views,
CASE
WHEN views < 100 THEN 3
WHEN views >= 100 AND views < 350 THEN 2
ELSE 1
END AS group_user
FROM stackoverflow.users
WHERE location LIKE '%Canada%' AND views > 0)
SELECT id, group_user, views
FROM (SELECT id,
views,
group_user,
MAX(views) OVER (PARTITION BY group_user ORDER BY views DESC) AS max_views
FROM data) AS dat
WHERE max_views = views
ORDER BY views DESC, id; |
| Result |
|
|
Посчитайте ежедневный прирост новых пользователей в ноябре 2008 года. Сформируйте таблицу с полями:
-
номер дня;
-
число пользователей, зарегистрированных в этот день;
-
сумму пользователей с накоплением.
| Code |
SELECT EXTRACT(DAY FROM day),
count_user,
SUM(count_user) OVER(ORDER BY day)
FROM (SELECT DISTINCT(creation_date::DATE) AS day,
COUNT(id) OVER(PARTITION BY creation_date::DATE) AS count_user
FROM stackoverflow.users
WHERE creation_date::DATE BETWEEN '2008-11-01' AND '2008-11-30') AS dat; |
| Result |
|
|
Для каждого пользователя, который написал хотя бы один пост, найдите интервал между регистрацией и временем создания первого поста. Отобразите:
-
идентификатор пользователя;
-
разницу во времени между регистрацией и первым постом.
| Code |
SELECT user_id,
post_date-creation_date
FROM (SELECT DISTINCT(p.user_id) user_id,
MIN(p.creation_date) OVER(PARTITION BY p.user_id) post_date,
u.creation_date creation_date
FROM stackoverflow.posts p
JOIN stackoverflow.users u ON p.user_id=u.id) dat; |
| Result |
|
|
Выведите общую сумму просмотров у постов, опубликованных в каждый месяц 2008 года. Если данных за какой-либо месяц в базе нет, такой месяц можно пропустить. Результат отсортируйте по убыванию общего количества просмотров.
| Code |
SELECT DISTINCT(DATE_TRUNC('month',creation_date)::DATE),
SUM(views_count) OVER(PARTITION BY DATE_TRUNC('month',creation_date)::DATE)
FROM stackoverflow.posts
WHERE EXTRACT(YEAR FROM creation_date) = 2008
ORDER BY SUM(views_count) OVER(PARTITION BY DATE_TRUNC('month',creation_date)::DATE) DESC
LIMIT 5; |
| Result |
|
|
Выведите имена самых активных пользователей, которые в первый месяц после регистрации (включая день регистрации) дали больше 100 ответов. Вопросы, которые задавали пользователи, не учитывайте. Для каждого имени пользователя выведите количество уникальных значений user_id. Отсортируйте результат по полю с именами в лексикографическом порядке.
| Code |
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
WHERE p.creation_date::date BETWEEN u.creation_date::date AND (u.creation_date::date + INTERVAL '1 month')
AND post_type_id = 2
GROUP BY u.display_name
HAVING COUNT(p.id) > 100
ORDER BY u.display_name
LIMIT 5; |
| Result |
|
|
Выведите количество постов за 2008 год по месяцам. Отберите посты от пользователей, которые зарегистрировались в сентябре 2008 года и сделали хотя бы один пост в декабре того же года. Отсортируйте таблицу по значению месяца по убыванию.
| Code |
WITH
use AS (SELECT id
FROM stackoverflow.users
WHERE DATE_TRUNC('month',creation_date)::DATE = '2008-09-01'),
use_id AS (SELECT DISTINCT(p.user_id)
FROM stackoverflow.posts p
JOIN use ON p.user_id=use.id
WHERE DATE_TRUNC('month',p.creation_date)::DATE = '2008-12-01')
SELECT DISTINCT(DATE_TRUNC('month',creation_date)::DATE),
COUNT(id) OVER(PARTITION BY DATE_TRUNC('month',creation_date)::DATE)
FROM stackoverflow.posts p
JOIN use_id ON p.user_id=use_id.user_id
WHERE DATE_TRUNC('year',p.creation_date)::DATE = '2008-01-01'
ORDER BY DATE_TRUNC('month',creation_date)::DATE DESC; |
| Result |
|
|
Используя данные о постах, выведите несколько полей:
-
идентификатор пользователя, который написал пост;
-
дата создания поста;
-
количество просмотров у текущего поста;
-
сумма просмотров постов автора с накоплением.
Данные в таблице должны быть отсортированы по возрастанию идентификаторов пользователей, а данные об одном и том же пользователе — по возрастанию даты создания поста.
| Code |
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
LIMIT 5; |
| Result |
|
|
Сколько в среднем дней в период с 1 по 7 декабря 2008 года включительно пользователи взаимодействовали с платформой? Для каждого пользователя отберите дни, в которые он или она опубликовали хотя бы один пост. Нужно получить одно целое число — не забудьте округлить результат.
| Code |
WITH
post AS (SELECT user_id,
COUNT(DISTINCT(DATE_TRUNC('day', creation_date)::date))
FROM stackoverflow.posts
WHERE creation_date::date BETWEEN '2008-12-01' AND '2008-12-07'
GROUP BY user_id)
SELECT ROUND(AVG(count))
FROM post; |
| Result |
|
|
На сколько процентов менялось количество постов ежемесячно с 1 сентября по 31 декабря 2008 года? Отобразите таблицу со следующими полями:
-
Номер месяца.
-
Количество постов за месяц.
-
Процент, который показывает, насколько изменилось количество постов в текущем месяце по сравнению с предыдущим.
Если постов стало меньше, значение процента должно быть отрицательным, если больше — положительным. Округлите значение процента до двух знаков после запятой.
Напомним, что при делении одного целого числа на другое в PostgreSQL в результате получится целое число, округлённое до ближайшего целого вниз. Чтобы этого избежать, переведите делимое в тип numeric.
| Code |
WITH
dat AS (SELECT DISTINCT(EXTRACT(MONTH FROM creation_date)) month_creat,
COUNT(id) OVER(PARTITION BY EXTRACT(MONTH FROM creation_date)) count_month
FROM stackoverflow.posts p
WHERE creation_date::DATE BETWEEN '2008-09-01' AND '2008-12-31'),
lag_dat AS (SELECT month_creat,
count_month,
LAG(count_month) OVER() lag
FROM dat)
SELECT month_creat,
count_month,
ROUND((count_month::numeric/lag-1)*100,2)
FROM lag_dat; |
| Result |
|
|
Найдите пользователя, который опубликовал больше всего постов за всё время с момента регистрации. Выведите данные его активности за октябрь 2008 года в таком виде:
-
номер недели;
-
дата и время последнего поста, опубликованного на этой неделе.
| Code |
WITH
max_post AS (SELECT DISTINCT(user_id),
COUNT(id) OVER(PARTITION BY user_id)
FROM stackoverflow.posts
ORDER BY COUNT(id) OVER(PARTITION BY user_id) DESC
LIMIT 1)
SELECT EXTRACT(WEEK FROM p.creation_date) week_creation,
MAX(p.creation_date) creation_date
FROM max_post m
JOIN stackoverflow.posts p ON m.user_id=p.user_id
WHERE DATE_TRUNC('month', p.creation_date)::date = '2008-10-01'
GROUP BY EXTRACT(WEEK FROM p.creation_date); |
| Result |
|
|
