SQL ACADEMY ответы и решения заданий (часть 4, задания 67-76)

Ниже представлены решения заданий № 67-76 из онлайн тренажера на сайте SQL ACADEMY (sql-academy.org).

Ответы на задания 1-22 (часть 1) здесь.

Ответы на задания 23-44 (часть 2) тут.

Ответы на задания 45-66 (часть 3) тут.

Задание 67. Вывести время отлета и время прилета для каждого перелета в формате “ЧЧ:ММ, ДД.ММ – ЧЧ:ММ, ДД.ММ”, где часы и минуты с ведущим нулем, а день и месяц без.

SELECT 
CONCAT(DATE_FORMAT(time_out, '%H:%i, %e.%c'), ' - ', DATE_FORMAT(time_in, '%H:%i, %e.%c')) AS flight_time 
FROM Trip;

Задание 68. Для каждой комнаты, которую снимали как минимум 1 раз, найдите имя человека, снимавшего ее последний раз, и дату, когда он выехал.

SELECT room_id, name, end_date
FROM Reservations
JOIN Users 
    ON Reservations.user_id = Users.id
WHERE end_date IN 
    (SELECT MAX(end_date) FROM Reservations GROUP BY room_id);

Задание 69. Вывести идентификаторы всех владельцев комнат, что размещены на сервисе бронирования жилья и сумму, которую они заработали.

SELECT owner_id, IFNULL(SUM(total), 0) AS total_earn
FROM Rooms
LEFT JOIN Reservations 
    ON Rooms.id = Reservations.room_id 
GROUP BY owner_id;

Задание 70. Необходимо категоризовать жилье на economy, comfort, premium по цене соответственно <= 100, 100 < цена < 200, >= 200. В качестве результата вывести таблицу с названием категории и количеством жилья, попадающего в данную категорию.

SELECT
CASE
    WHEN price <= 100 THEN 'economy'
    WHEN  price > 100 AND price < 200 THEN 'comfort'
    WHEN price >= 200 THEN 'premium'
END AS category, 
COUNT(*) as count
FROM Rooms
GROUP BY category;

Задание 71. Найдите какой процент пользователей, зарегистрированных на сервисе бронирования, хоть раз арендовали или сдавали в аренду жилье. Результат округлите до сотых.

SELECT ROUND((SELECT COUNT(*) FROM 
    (SELECT DISTINCT owner_id FROM Rooms
    JOIN Reservations ON Rooms.id = Reservations.room_id
    UNION 
    SELECT DISTINCT user_id FROM Reservations) AS active_users)*100/(SELECT COUNT(id) FROM Users), 2) AS percent;

Задание 72. Выведите среднюю стоимость бронирования для комнат, которых бронировали хотя бы один раз. Среднюю стоимость необходимо округлить до целого значения вверх.

SELECT room_id, CEILING(AVG(price)) AS avg_price
FROM Reservations
GROUP BY room_id;

Задание 73. Выведите id тех комнат, которые арендовали нечетное количество раз.

SELECT room_id, COUNT(room_id) AS count
FROM Reservations
GROUP BY room_id
HAVING count % 2 != 0;

Задание 74. Выведите идентификатор и признак наличия интернета в помещении. Если интернет в сдаваемом жилье присутствует, то выведите «YES», иначе «NO».

SELECT id, IF(has_internet, "YES", "NO") AS has_internet
FROM Rooms;

Задание 75. Выведите фамилию, имя и дату рождения студентов, кто был рожден в мае.

SELECT last_name, first_name, birthday
FROM Student
WHERE MONTH(birthday) = 5;

Задание 76. Вывести имена всех пользователей сервиса бронирования жилья, а также два признака: является ли пользователь собственником какого-либо жилья (is_owner) и является ли пользователь арендатором (is_tenant). В случае наличия у пользователя признака необходимо вывести в соответствующее поле 1, иначе 0.

SELECT name, 
IF(id IN (SELECT DISTINCT owner_id FROM Rooms), 1, 0) AS is_owner,
IF(id IN (SELECT user_id FROM Reservations), 1, 0) AS is_tenant
FROM Users;