MediumNo. 2292LeetCode Premium
Products With Three or More Orders in Two Consecutive Years — LeetCode 2292 Python Solution
- Topics
- Database
- Reading time
- 3 min
- Source
- leetcode.com
Table schema
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.Example
+---------------+------+
| 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.Python solution
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()Complexity
- Time
- O(n log n)(typical)
- Space · auxiliary
- O(n)
Related problems
| No. | Problem | Difficulty |
|---|---|---|
| 0175 | Combine Two Tables | Easy |
| 0176 | Second Highest Salary | Medium |
| 0177 | Nth Highest Salary | Medium |
| 0178 | Rank Scores | Medium |
| 0180 | Consecutive Numbers | Medium |
| 0181 | Employees Earning More Than Their Managers | Easy |
Frequently asked questions
- How hard is LeetCode 2292. Products With Three or More Orders in Two Consecutive Years?
LeetCode 2292. Products With Three or More Orders in Two Consecutive Years is rated Medium on LeetCode.
- What topics does LeetCode 2292. Products With Three or More Orders in Two Consecutive Years cover?
LeetCode 2292. Products With Three or More Orders in Two Consecutive Years is tagged Database on LeetCode.
- Is LeetCode 2292. Products With Three or More Orders in Two Consecutive Years a premium problem?
Yes. LeetCode 2292. Products With Three or More Orders in Two Consecutive Years is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.