Skip to content

Commit 9afb08a

Browse files
IvanbyoneIvanbyone
committed
task: #3570 & All fremium easy completed
1 parent 146cc79 commit 9afb08a

File tree

2 files changed

+60
-0
lines changed

2 files changed

+60
-0
lines changed

‎README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,7 @@ Useful for preparing for technical interviews and improving your SQL skills.
159159
- [2356. Number of Unique Subjects Taught by Each Teacher](./leetcode/easy/2356.%20Number%20of%20Unique%20Subjects%20Taught%20by%20Each%20Teacher.sql)
160160
- [3436. Find Valid Emails](./leetcode/easy/3436.%20Find%20Valid%20Emails.sql)
161161
- [3465. Find Products with Valid Serial Numbers](./leetcode/easy/3465.%20Find%20Products%20with%20Valid%20Serial%20Numbers.sql)
162+
- [3570. Find Books with No Available Copies](./leetcode/easy/3570.%20Find%20Books%20with%20No%20Available%20Copies.sql)
162163
2. [Medium](./leetcode/medium/)
163164
- [176. Second Highest Salary](./leetcode/medium/176.%20Second%20Highest%20Salary.sql)
164165
- [180. Consecutive Numbers](./leetcode/medium/180.%20Consecutive%20Numbers.sql)

‎leetcode/easy/3570. Find Books with No Available Copies.sql

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
/*
2+
Question 3570. Find Books with No Available Copies
3+
Link: https://leetcode.com/problems/find-books-with-no-available-copies/description/?envType=problem-list-v2&envId=database
4+
5+
Table: library_books
6+
7+
+------------------+---------+
8+
| Column Name | Type |
9+
+------------------+---------+
10+
| book_id | int |
11+
| title | varchar |
12+
| author | varchar |
13+
| genre | varchar |
14+
| publication_year | int |
15+
| total_copies | int |
16+
+------------------+---------+
17+
book_id is the unique identifier for this table.
18+
Each row contains information about a book in the library, including the total number of copies owned by the library.
19+
Table: borrowing_records
20+
21+
+---------------+---------+
22+
| Column Name | Type |
23+
+---------------+---------+
24+
| record_id | int |
25+
| book_id | int |
26+
| borrower_name | varchar |
27+
| borrow_date | date |
28+
| return_date | date |
29+
+---------------+---------+
30+
record_id is the unique identifier for this table.
31+
Each row represents a borrowing transaction and return_date is NULL if the book is currently borrowed and hasn't been returned yet.
32+
Write a solution to find all books that are currently borrowed (not returned) and have zero copies available in the library.
33+
34+
A book is considered currently borrowed if there exists a borrowing record with a NULL return_date
35+
Return the result table ordered by current borrowers in descending order, then by book title in ascending order.
36+
*/
37+
38+
WITH br AS (
39+
SELECT
40+
book_id,
41+
COUNT(book_id) AS current_borrowers
42+
FROM borrowing_records
43+
WHERE return_date IS NULL
44+
GROUP BY book_id
45+
)
46+
47+
SELECT
48+
lb.book_id,
49+
lb.title,
50+
lb.author,
51+
lb.genre,
52+
lb.publication_year,
53+
br.current_borrowers
54+
FROM library_books AS lb
55+
INNER JOIN
56+
br
57+
ON lb.book_id = br.book_id
58+
WHERE (lb.total_copies - br.current_borrowers) = 0
59+
ORDER BY br.current_borrowers DESC, lb.title ASC

0 commit comments

Comments
 (0)