12_Databases_and_SQL/HomeWork_5.sql - master - Kostia/02_Programming_GeekBrains

12_Databases_and_SQL/HomeWork_5.sql
132 строки8 KB
Konstantin
Сommitted on пн 10.02.2025 09:38:15 AM MSK
10 фев 2025, 09:38
10 фев 2025, 09:38f820659
/*
Задача 1 Ранжированный список пользователей

Имеется база данных – социальная сеть.
База данных содержит сущности:
users – пользователи;
messages – сообщения;
friend_requests – заявки на дружбу;
communities – сообщества;
users_communities – пользователи сообществ;
media_types – типы медиа;
media – медиа;
likes – лайки;
profiles – профили пользователя.

У сущности «пользователи» имеются следующие поля(атрибуты):
id – идентификатор;
firstname – имя;
lastname - фамилия;
email - адрес электронной почты.

У сущности «сообщения» имеются следующие поля(атрибуты):
id – идентификатор;
from_user_id – отправитель;
to_user_id – получатель;
body - содержимое;
created_at - дата отправки.

Найти количество сообщений, отправленных каждым пользователей.
В зависимости от количества отправленных сообщений рассчитать ранг пользователей,
первое место присвоив пользователю(ям) с наибольшим количеством отправленных сообщений.

Вывести полученный ранг, имя, фамилия, пользователя и кол-во отправленных сообщений. Выводимый список необходимо отсортировать в порядке возрастания ранга.
*/
-- Вы работаете с MySQL

SELECT 
    DENSE_RANK() OVER (ORDER BY IFNULL(msg_count, 0) DESC) AS user_rank,
    u.firstname,
    u.lastname,
    COALESCE(msg_count, 0) AS msg_count
FROM users u
LEFT JOIN (
    SELECT 
        from_user_id,
        COUNT(*) AS msg_count
    FROM messages
    GROUP BY from_user_id
) AS msg_data ON u.id = msg_data.from_user_id
ORDER BY user_rank;

/*

Объяснение запроса:
Подзапрос (msg_data): Считаем количество отправленных сообщений каждого пользователя и группируем по from_user_id.
LEFT JOIN: Связываем всех пользователей с подсчитанными сообщениями, включая тех, кто не отправил ни одного сообщения.
DENSE_RANK() OVER (ORDER BY IFNULL(msg_count, 0) DESC): Используем DENSE_RANK() для назначения рангов.
IFNULL() заменяет NULL на 0, чтобы обеспечить, что пользователи без сообщений также получат ранг, и этот ранг будет последним среди тех, кто отправил сообщения.
DENSE_RANK() предоставляет последовательные ранги без пропусков, что позволяет учитывать всех пользователей с одинаковым количеством сообщений однородно.
COALESCE(msg_count, 0): Заменяем NULL на 0 в финальной выборке для корректного отображения количества сообщений.
SELECT и ORDER BY: Включаем в вывод ранг, имя, фамилию и количество сообщений, упорядочивая по рангу.
Таким образом, пользователи без сообщений будут иметь один и тот же ранг, и этот ранг будет следовать за рангами пользователей,
которые отправили хотя бы одно сообщение.

	user_rank	firstname	lastname	msg_count
1	1	Jaida		Kilback		7
2	2	Reuben		Nienow		4
3	2	Norene		West		4
4	3	Frederik	Upton		2
5	4	Unique		Windler		1
6	4	Mireya		Orn			1
7	4	Jordyn		Jerde		1
8	5	Frederick	Effertz		0
9	5	Victoria	Medhurst	0
10	5	Austyn		Braun		0
*/


/*
Задача 2 Нахождение разности соседних дат

Получите список сообщений, отсортированных по возрастанию даты отправки.
Вычислите разность между соседними значениями дат отправки. Разности выразите в минутах.

Выведите идентификатор сообщения, дату отправки, дату отправки следующего сообщения и разницу даты отправки соседних сообщений.
*/

SELECT 
    id AS message_id,
    created_at AS send_date,
    LEAD(created_at) OVER (ORDER BY created_at) AS next_send_date,
    TIMESTAMPDIFF(MINUTE, created_at, LEAD(created_at) OVER (ORDER BY created_at)) AS diff_minutes
FROM messages
ORDER BY created_at;

/*
Объяснение запроса:
id AS message_id: Выводит идентификатор сообщения.
created_at AS send_date: Выводит дату отправки текущего сообщения.
LEAD(created_at) OVER (ORDER BY created_at) AS next_send_date: Используя оконную функцию LEAD(),
 получаем дату отправки следующего сообщения в порядке отправки текущего списка сообщений.
 Функция LEAD(created_at) берет дату следующей записи, основываясь на заданном порядке.
TIMESTAMPDIFF(MINUTE, created_at, LEAD(created_at) OVER (ORDER BY created_at)) AS diff_minutes:
 Вычисляет разницу в минутах между текущей датой отправки и датой отправки следующего сообщения.
 Функция TIMESTAMPDIFF() используется для получения разницы в определенных единицах времени, здесь — в минутах.
 
Заметка:
Поле next_send_date для последнего сообщения в базе данных будет NULL, поскольку для него не будет следующей записи.
Разность в минутах для последнего сообщения также будет NULL.

message_id	send_date	next_send_date	diff_minutes
1	1	2023-10-16 19:50:52	2023-10-16 19:52:52	2
2	2	2023-10-16 19:52:52	2023-10-16 19:54:52	2
3	3	2023-10-16 19:54:52	2023-10-16 20:00:52	6
4	4	2023-10-16 20:00:52	2023-10-16 20:01:52	1
5	5	2023-10-16 20:01:52	2023-10-16 20:03:52	2
6	6	2023-10-16 20:03:52	2023-10-16 20:04:52	1
7	7	2023-10-16 20:04:52	2023-10-16 20:10:52	6
8	8	2023-10-16 20:10:52	2023-10-16 20:11:52	1
9	9	2023-10-16 20:11:52	2023-10-16 20:14:52	3
10	10	2023-10-16 20:14:52	2023-10-16 20:16:52	2
11	11	2023-10-16 20:16:52	2023-10-16 20:17:52	1
12	12	2023-10-16 20:17:52	2023-10-16 20:22:52	5
13	13	2023-10-16 20:22:52	2023-10-16 20:24:52	2
14	14	2023-10-16 20:24:52	2023-10-16 20:24:52	0
15	15	2023-10-16 20:24:52	2023-10-16 20:26:52	2
16	16	2023-10-16 20:26:52	2023-10-16 20:26:52	0
17	17	2023-10-16 20:26:52	2023-10-16 20:30:52	4
18	18	2023-10-16 20:30:52	2023-10-16 20:31:52	1
19	19	2023-10-16 20:31:52	2023-10-16 20:39:52	8
20	20	2023-10-16 20:39:52	null	null
*/