SQLite 3.27

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