Unpopular Books — LeetCode 1098 Python Solution

Table: Books +----------------+---------+ | Column Name | Type | +----------------+---------+ | book_id | int | | name | varchar | | available_from | date | +----------------+---------+ book_id is the primary key (column with unique values) of this table. Table: Orders +----------------+---------+ | Column Name | Type | +----------------+---------+ | order_id | int | | book_id | int | | quantity | int | | dispatch_date | date | +----------------+---------+ order_id is the primary key (column with unique values) of this table.

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id is the primary key (column with unique values) of this table.

import duckdb
import pandas as pd

def solution(books: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Books", books)
    con.register("Orders", orders)
    return con.execute("""SELECT book_id, name
FROM
    Books
    LEFT JOIN Orders USING (book_id)
WHERE available_from < '2019-05-23'
GROUP BY 1
HAVING SUM(IF(dispatch_date >= '2018-06-23', quantity, 0)) < 10;""").df()