SQL

Задание 68 - Sql-academy

Помогите решить 68 задание из sql-academy. org

Для каждой комнаты, которую снимали как минимум 1 раз, найдите имя человека, снимавшего ее последний раз, и дату, когда он выехал
Александр Столяров
Александр Столяров
95
with get_data as (
select room_id,
max(end_date) as end_date
from Reservations
group by room_id
having count(*) >= 1
)
select rs.room_id,
u.name,
rs.end_date
from Reservations as rs
join get_data as gd on gd.room_id = rs.room_id
and gd.end_date = rs.end_date
join users as u on u.id = rs.user_id
Константин Калегин
Константин Калегин
1 328
Лучший ответ
Александр Столяров Спасибо, помогли)
with get_data as (
select room_id,
max(end_date) as end_date
from Reservations
group by room_id
having count(*) >= 1
)
select rs.room _id,
u.name ,
rs.end_date
from Reservations as rs
join get_data as gd on gd.room _id = rs.room _id
and gd.end_date = rs.end_date
join users as u on u.id = rs.user_id
Неизвестно Неизвестно
Неизвестно Неизвестно
1 073
SELECT a.room_id, name, a.end_date
FROM (SELECT room_id,
MAX(end_date ) AS end_date
FROM Reservations
GROUP BY room_id) AS a
JOIN Reservations
ON a.end_date = Reservations.end_date
AND a.room_id = Reservations.room_id
JOIN Users
ON Reservations.user_id = Users.id
Ерлан Нургазиев
Ерлан Нургазиев
331 
 WITH Table1 AS ( 

  SELECT room_id, 

    MIN(TIMESTAMPDIFF(SECOND, end_date, NOW())) AS time 

  FROM Reservations 

  GROUP BY 1

) 

SELECT Reservations.room_id, 

  name, 

  end_date 

FROM Reservations 

  JOIN Table1 ON Reservations.room_id = Table1.room_id 

  JOIN Users ON user_id = Users.id 

WHERE TIMESTAMPDIFF(SECOND, end_date, NOW()) = Table1.time
Николай Добрынин
Николай Добрынин
329 
 SELECT Reservations.room_id, 

	Users.name, 

	Reservations.end_date 

FROM Reservations 

	JOIN Users ON Reservations.user_id = Users.id 

WHERE (Reservations.room_id, Reservations.end_date) IN ( 

		SELECT Reservations.room_id, 

			MAX(end_date) 

		FROM Reservations 

			JOIN Users ON Reservations.user_id = Users.id 

		GROUP BY Reservations.room_id 

	)
Алексей Тороповв
Алексей Тороповв
164

Похожие вопросы