By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE books (id INTEGER, name TEXT, genre TEXT, pages INTEGER, price INTEGER);
INSERT INTO books (id, name, genre, pages, price) VALUES
(1, 'name_1', 'genre_1', 100, 10),
(2, 'name_2', 'genre_2', 200, 20),
(3, 'name_3', 'genre_3', 300, 30);
CREATE TABLE orders (id INTEGER, name TEXT, id_book INTEGER, date INTEGER);
INSERT INTO orders (id, name, id_book, date) VALUES
(1, 'name_1', 1, 1),
(2, 'name_2', 2, 2),
(3, 'name_3', 3, 3);
WITH last_students AS (
SELECT DISTINCT name
FROM orders
ORDER BY date DESC LIMIT 3
)
SELECT o.name AS student_name,
b.name AS book_title
FROM books b INNER JOIN orders o
ON o.id_book = b.id
WHERE o.name IN last_students
| student_name | book_title |
|---|---|
| name_1 | name_1 |
| name_2 | name_2 |
| name_3 | name_3 |