Query that returns pairs of users who have borrowed the same number of books each year

Hi everyone. So I have this table

CREATE TABLE Borrow ( RequestID CHAR(5), BookCode CHAR(5), User VARCHAR2(200), Date DATE, BorrowDeadline DATE, ReturnDate DATE, -- It can be NULL if the book is still not returned ReminderDate DATE, -- It can be NULL if the deadline is still not expired ) 

which represents the borrows system of a library. I want a query which returns pairs of users who hava borrowed the same number of books each year; this means that if two user are matched, they always took the same number of books.

I’ve tried this query:

SELECT DISTINCT User1, User2 FROM ((SELECT User AS User1, YEAR(Date) AS Year, COUNT(RequestID) AS BorrowsNumber FROM Borrow AS B GROUP BY User) AS R0 INNER JOIN (SELECT User AS User2, YEAR(Date) AS Year, COUNT(RequestID) AS BorrowsNumber FROM Borrow AS B GROUP BY User) R1 ON R0.User1 != R1.User2 AND R0.Year = R1.Year AND R0.BorrowsNumber = R1.BorrowNumbers); 

My idea was to join the table with itself to get the pairs using the condition of R0.User1 != R1.User2 to avoid the obvious ‘user-sameuser’ pair and the conditions R0.Year = R1.Year AND R0.BorrowsNumber = R1.BorrowNumbers to get the same numbers of book in the same year. This partially works because if I have this sample table:

RequestID BookCode User Date BorrowDate ReturnDate ReminderDate
A0034 24012 AUser 2020-12-12 2021-01-31 NULL NULL
F1562 91283 AnotherUser 2020-10-03 2020-11-25 NULL NULL

The INNER JOIN would return this:

User1 User2 Year BorrowsNumber
AUser AnotherUser 2020 1
AnotherUser AUser 2020 1

The second row annoys me a little bit but nevermind, it is okay after all.

Now let’s say I add 2 more rows to the sample table:

RequestID BookCode User Date BorrowDate ReturnDate ReminderDate
A0034 24012 AUser 2020-12-12 2021-01-31 NULL NULL
F1562 91283 AnotherUser 2020-10-03 2020-11-25 NULL NULL
E1234 57839 AnotherUser 2021-01-02 2021-02-28 NULL NULL
T2839 78291 ANewUser 2021-02-14 2021-04-01 NULL NULL

Then the query returns me:

User1 User2 Year BorrowsNumber
AUser AnotherUser 2020 1
AnotherUser AUser 2020 1
AnotherUser ANewUser 2021 1
ANewUser AnotherUser 2021 1

which is wrong because the couple [AUser – AnotherUser]/[AnotherUser – AUser] should not be in the table due user ‘AUser’ never got 1 book in 2021. Can somebody help? I have read the rules which would me to add a flair to this post, depending which DBMS I’m using. The thing is that I’m not using a DBMS but a sort of ‘pure’ SQL. It’s just an exercise from my book but no DBMS is specified. But because the book often releates to Oracle I’ll put the Oracle flair. Just in case. Thanks everybody in advance!

submitted by /u/ZoSo-bin
[link] [comments]


Go to Source of this post
Author Of this post: /u/ZoSo-bin
Title Of post: Query that returns pairs of users who have borrowed the same number of books each year
Author Link: {authorlink}