Skip to content

2292. Products With Three or More Orders in Two Consecutive Years

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
WITH
  OrdersNeighbors AS (
    SELECT
      product_id,
      YEAR(purchase_date) AS curr_year,
      LEAD(YEAR(purchase_date)) OVER(
        PARTITION BY product_id
        ORDER BY YEAR(purchase_date)
      ) AS next_year
    FROM orders
    GROUP BY 1, 2
    HAVING COUNT(order_id) >= 3
  )
SELECT DISTINCT product_id
FROM OrdersNeighbors
WHERE curr_year + 1 = next_year;