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

12_Databases_and_SQL/HomeWork_4.sql
211 строк12 KB
Konstantin
Сommitted on пн 10.02.2025 09:38:15 AM MSK
10 фев 2025, 09:38
10 фев 2025, 09:38f820659
/*
Лайки

Имеется база данных – социальная сеть.

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

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

У сущности «профиль пользователя» имеются следующие поля(атрибуты):
user_id – идентификатор;
gender – пол;
birthday - дата рождения;
photo_id - аватарка;
hometown - город.

Атрибут «пол» сущности «профиль пользователя» может принимать следующие значения:
'f' - женский;
'm' - мужской.

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

У сущности «медиа» имеются следующие поля(атрибуты):
id – идентификатор;
user_id – пользователь – владелец медиа;
body - содержимое;
filename – ссылка на файл;
created_at - дата создания;
updated_at - дата последнего обновления.

Найдите общее количество лайков, которые получили пользователи женского пола.
*/

-- Вы работаете с MySQL
-- Введите свой код ниже

SELECT COUNT(*)
FROM likes
JOIN media ON likes.media_id = media.id
JOIN users ON media.user_id = users.id
JOIN profiles ON users.id = profiles.user_id
WHERE profiles.gender = 'f';

/*
Объяснение запроса:
JOIN media: Присоединяем таблицу media к таблице likes по media_id, чтобы связать каждый лайк с соответствующим медиа.
JOIN users: Присоединяем таблицу users к результату предыдущего соединения по user_id из таблицы media. Это позволяет связать каждое медиа с его владельцем.
JOIN profiles: Присоединяем таблицу profiles к результату, чтобы установить связь между пользователями и их профилями, где указан пол.
WHERE profiles.gender = 'f': Фильтруем результаты запроса так, чтобы остались только записи, соответствующие пользователям женского пола.
COUNT(*): Подсчитываем общее количество записей в итоговом результате, что соответствует общему количеству лайков.
*/


/*
Найдите количество лайков, которые поставили пользователи женского пола и мужского пола.
Выведите название пола (преобразовав значение атрибута f в женский, а значение ‘m` - мужской) и количество, поставленных лайков, применив к количеству лайков сортировку по убыванию.
*/


SELECT 
    CASE 
        WHEN profiles.gender = 'f' THEN 'женский'
        WHEN profiles.gender = 'm' THEN 'мужской'
    END AS gender_name,
    COUNT(likes.id) AS like_count
FROM likes
JOIN users ON likes.user_id = users.id
JOIN profiles ON users.id = profiles.user_id
GROUP BY profiles.gender
ORDER BY like_count DESC;

/*
Объяснение запроса:
CASE WHEN profiles.gender: Используется для трансформации значений f и m в более понятные строки "Женский" и "Мужской" соответственно.
COUNT(likes.id): Считает количество лайков для каждой группы, определённой по полу.
FROM likes JOIN users ON... JOIN profiles ON...: Определяет, какие таблицы и как связывать для достижения нужной выборки данных. Здесь likes связывается с users по user_id, а users - с profiles по user_id.
GROUP BY profiles.gender: Группирует результаты по полу пользователя.
ORDER BY like_count DESC: Сортирует результаты по количеству лайков в убывающем порядке.
*/

/*
Имеется база данных – социальная сеть.

База данных содержит сущности:
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 - дата отправки.

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

SELECT users.id
FROM users
LEFT JOIN messages ON users.id = messages.from_user_id
WHERE messages.from_user_id IS NULL;
/*
Объяснение запроса:
FROM users: Основная таблица, из которой выбираются пользователи.
LEFT JOIN messages ON users.id = messages.from_user_id: Присоединяем таблицу messages по полю from_user_id, чтобы проверить, есть ли сообщения, отправленные каждым пользователем. LEFT JOIN гарантирует, что в результирующем наборе будут все пользователи, даже если они не имеют соответствующих записей в messages.
WHERE messages.from_user_id IS NULL: Фильтр, который оставляет только тех пользователей, у которых нет отправленных сообщений. Поскольку LEFT JOIN включает в себя все записи из левой таблицы (users), записи в правой таблице (messages), которых нет, будут иметь значения NULL в полях, зависящих от messages.
*/


/*
Друзья

Инструкция по использованию платформы



Имеется база данных – социальная сеть.

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

У сущности «заявки на дружбу» имеются следующие поля(атрибуты):
initiator_user_id – инициатор;
target_user_id – получатель;
status - статус;
requested_at - дата создания;
updated_at - дата последнего обновления.

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

Друзья — это пользователи у которых имеется соответствующая запись (заявка) в сущности «заявки на дружбу» и в атрибуте status данной сущности указано значение 'approved'.

Найдите количество друзей у каждого пользователя. Выведите для каждого пользователя идентификатор пользователя, имя, фамилию и количество друзей cnt. Сортировка выводимых записей в порядке возрастания идентификатора пользователя.
*/

SELECT u.id, u.firstname, u.lastname, COUNT(f.user_id) AS cnt
FROM users u
LEFT JOIN (
    SELECT initiator_user_id AS user_id
    FROM friend_requests
    WHERE status = 'approved'
    UNION ALL
    SELECT target_user_id AS user_id
    FROM friend_requests
    WHERE status = 'approved'
) f ON u.id = f.user_id
GROUP BY u.id, u.firstname, u.lastname
ORDER BY u.id;

/*
Объяснение запроса:
Создание подзапроса f: Этот подзапрос выбирает user_id из всех одобренных заявок на дружбу, включая как инициаторов, так и получателей. Используется UNION ALL для объединения инициаторов и получателей в один набор данных, где каждый user_id представляет пользователя, который имеет одобренные друзей.
LEFT JOIN: Присоединяем этот подзапрос к таблице users, соединяя по id пользователя, чтобы учесть всех пользователей, включая тех, у кого нет друзей.
COUNT(f.user_id): Считаем количество записей в подзапросе f для каждого пользователя, что дает количество друзей. Так как мы используем UNION ALL, друзья, которые могут быть как инициаторами, так и получателями заявки, учитываются корректно.
GROUP BY u.id, u.firstname, u.lastname: Группировка результатов по идентификатору, имени и фамилии пользователя для корректного подсчета.
ORDER BY u.id: Упорядочивание результатов по id пользователя в порядке возрастания для удобства просмотра.
*/

Ожидаемый ответ:

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