How ovoid duplicate code in sql query subQuery

user_14832311 0
Viewed 32

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
sql
2 Answers

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
)
user_62199790

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;
user_13222680
Related