SQL Tutorial 32: Write a Query to Find Users Who Borrowed Books Back-to-Back
Watch the full SQL playlist here: • Learn SQL
Question: Identify all users who borrowed books in a back-to-back manner, meaning they borrowed a new book exactly one day after returning a previous one. Only direct consecutive borrowings should be considered—that is, cases where the borrow date of a book is precisely one day after the return date of an earlier book by the same user. For each such occurrence, list the user along with the IDs of both the previous and the next borrowing records, showing each valid pair only once.
Sample Table Schema:
CREATE TABLE BookBorrowing (
BorrowID INT PRIMARY KEY,
UserID INT,
BookID INT,
BorrowDate DATE,
ReturnDate DATE
);
INSERT INTO BookBorrowing (BorrowID, UserID, BookID, BorrowDate, ReturnDate) VALUES
(1, 101, 1, '2024-01-01', '2024-01-10'),
(2, 101, 2, '2024-01-11', '2024-01-20'),
(3, 101, 3, '2024-01-22', '2024-01-30'),
(4, 102, 4, '2024-02-01', '2024-02-10'),
(5, 102, 5, '2024-02-15', '2024-02-25'),
(6, 103, 6, '2024-03-01', '2024-03-05'),
(7, 104, 7, '2024-04-01', NULL); -- Book not yet returned
-- Expected Output:
-- UserID PreviousBorrowID NextBorrowID
-- 101 1 2
Perfect for: SQL interview prep, Intermediate SQL learners
and Data analysts & engineers
Don’t forget to like, subscribe, and comment if this helped you!
Hashtags:
#SQL #SQLQuery #SQLInterview #LearnSQL #DataScience #TechInterview #Programming #SQLServer #WindowFunctions #CodeChallenge