SQLite book shop: Last students and all books they ordered

forum. I’m trying to run SQL for my local book-shop. The problem is to show last N students and all books they ordered.

My try:

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); 
--etc rows here

CREATE TABLE orders (
   id INTEGER,
   name TEXT,
   id_book INTEGER,
   date INTEGER --in SQLite INTEGER for a date is okay
);
INSERT INTO orders (id, name, id_book, date)
VALUES
(1, name_1, id_book_1, date_1),
(2, name_2, id_book_2, date_2),
(3, name_3, id_book_3, date_3); 
--etc rows here

WITH last_students AS(
SELECT orders.name
FROM orders
ORDER BY orders.id DESC
LIMIT 3) --creates temp table with last 3 names of students
SELECT orders.name, books.name
FROM books
INNER JOIN last_students
    ON books.id = last_students.id_book;

Is it okay code? Syntax check only shows mistake in a row 8, which I don’t get. Thank you in advance

Answer

You can join books to orders and filter the results for the last N students:

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

The CTE returns the last 3 students (by date and not by id) who ordered a book.

See the demo.