How ovoid duplicate code in sql query subQuery-CodePudding

I'm new in SQL. Need some help to improve my query to ovoid duplicate code.

SELECT customers.name, orders.price
FROM customers 
JOIN orders ON orders.id = customers.order_id
WHERE customers.order_id IN (
SELECT orders.id
FROM orders

WHERE orders.price = (
    SELECT orders.price
    FROM orders
    WHERE orders.order_date BETWEEN
        (SELECT MIN(orders.order_date) FROM orders) 
        AND  
        (SELECT DATE_ADD(MIN(orders.order_date), INTERVAL 10 year)FROM orders)
    ORDER BY orders.price DESC LIMIT 1
    )
    AND orders.order_date BETWEEN 
    (SELECT MIN(orders.order_date) FROM orders) 
    AND  
    (SELECT DATE_ADD(MIN(orders.order_date), INTERVAL 10 year)FROM orders)
)

I would like ovoid duplicate code here

SELECT MIN(orders.order_date) FROM orders

and

SELECT DATE_ADD(MIN(orders.order_date), INTERVAL 10 year)FROM orders

CodePudding user response：

You can use WITH to get first 10 years orders. By defitinion there exists no orders with the date < min(date), so you needn't between, just <= .

firstOrders as (
  SELECT *
  FROM orders
  WHERE order_date <=  
        (SELECT DATE_ADD(MIN(o.order_date), INTERVAL 10 year)
         FROM orders o)
)
SELECT customers.name, orders.price
FROM customers 
JOIN FirsrOrders orders ON orders.id = customers.order_id
AND orders.price = (
   select price
   from firstOrders
   order py price desc
   limit 1
)

CodePudding user response：

You want orders from the first ten years where the price was equal to the maximum price among those orders. So rank by price and grab those holding the #1 spot.

with data as (
    select *,
        date_add(min(order_date) over (), interval 10 year) as max_date,
        rank() over (order by price desc) as price_rank
    from orders
)
select *
from data
where order_date <= max_date and price_rank = 1;