LeetCode SQL Medium

Currently Playing: Leetcode MEDIUM 1098 - Unpopular Books DATE_SUB() TIMESTAMPDIFF - Explained by Everyday Data Science

Question: https://leetcode.com/problems/unpopular-books/description/ SQL Schema: Create table If Not Exists Books (book_id int, name varchar(50), available_from date) Create table If Not Exists Orders (order_id int, book_id int, quantity int, dispatch_date date) Truncate table Books insert into Books (book_id, name, available_from) values ('1', 'Kalila And Demna', '2010-01-01') insert into Books (book_id, name, available_from) values ('2', '28 Letters', '2012-05-12') insert into Books (book_id, name, available_from) values ('3', 'The Hobbit', '2019-06-10') insert into Books (book_id, name, available_from) values ('4', '13 Reasons Why', '2019-06-01') insert into Books (book_id, name, available_from) values ('5', 'The Hunger Games', '2008-09-21') Truncate table Orders insert into Orders (order_id, book_id, quantity, dispatch_date) values ('1', '1', '2', '2018-07-26') insert into Orders (order_id, book_id, quantity, dispatch_date) values ('2', '1', '1', '2018-11-05') insert into Orders (order_id, book_id, quantity, dispatch_date) values ('3', '3', '8', '2019-06-11') insert into Orders (order_id, book_id, quantity, dispatch_date) values ('4', '4', '6', '2019-06-05') insert into Orders (order_id, book_id, quantity, dispatch_date) values ('5', '4', '5', '2019-06-20') insert into Orders (order_id, book_id, quantity, dispatch_date) values ('6', '5', '9', '2009-02-02') insert into Orders (order_id, book_id, quantity, dispatch_date) values ('7', '5', '8', '2010-04-13') Pandas Schema: data = [[1, 'Kalila And Demna', '2010-01-01'], [2, '28 Letters', '2012-05-12'], [3, 'The Hobbit', '2019-06-10'], [4, '13 Reasons Why', '2019-06-01'], [5, 'The Hunger Games', '2008-09-21']] books = pd.DataFrame(data, columns=['book_id', 'name', 'available_from']).astype({'book_id':'Int64', 'name':'object', 'available_from':'datetime64[ns]'}) data = [[1, 1, 2, '2018-07-26'], [2, 1, 1, '2018-11-05'], [3, 3, 8, '2019-06-11'], [4, 4, 6, '2019-06-05'], [5, 4, 5, '2019-06-20'], [6, 5, 9, '2009-02-02'], [7, 5, 8, '2010-04-13']] orders = pd.DataFrame(data, columns=['order_id', 'book_id', 'quantity', 'dispatch_date']).astype({'order_id':'Int64', 'book_id':'Int64', 'quantity':'Int64', 'dispatch_date':'datetime64[ns]'}) #datascience #mysqltutorials #leetcodesolutions