Products With Three or More Orders in Two Consecutive Years — LeetCode 2292 Python Solution

Table: Orders +---------------+------+ | Column Name | Type | +---------------+------+ | order_id | int | | product_id | int | | quantity | int | | purchase_date | date | +---------------+------+ order_id contains unique values. Each row in this table contains the ID of an order, the id of the product purchased, the quantity, and the purchase date.

+---------------+------+
| Column Name   | Type |
+---------------+------+
| order_id      | int  |
| product_id    | int  |
| quantity      | int  |
| purchase_date | date |
+---------------+------+
order_id contains unique values.
Each row in this table contains the ID of an order, the id of the product purchased, the quantity, and the purchase date.

import duckdb
import pandas as pd

def solution(orders: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Orders", orders)
    return con.execute("""WITH
    P AS (
        SELECT product_id, YEAR(purchase_date) AS y, COUNT(1) >= 3 AS mark
        FROM Orders
        GROUP BY 1, 2
    )
SELECT DISTINCT p1.product_id
FROM
    P AS p1
    JOIN P AS p2 ON p1.y = p2.y - 1 AND p1.product_id = p2.product_id
WHERE p1.mark AND p2.mark;""").df()