Hackerrank is one of the top websites that help us to test our skills in different areas like C#, SQL, JAVA. Following is the SQL Query asked at Hackerrank.

Problem Statement:

There are two tables CUSTOMERS and ORDERS where we maintain customers and the information of their order as shown below.

CUSTOMERS

CustomerID

CustomerName

OrderID

1

Sai

100

2

Raj

101

ORDERS

OrderID

Qty

Price

OrderDate

100

2

100

10/02/1998

101

4

30

01/01/1999

Now, we need to write a query to get the name of the customer and Order Price who done maximum order within 10 years of the first recorded order date.

SQL Query

Following are the SQL Query will solve the above problem statement

SELECT TOP 1 C.NAME, O.PRICE 
FROM CUSTOMERS C INNER JOIN ORDERS O 
ON C.ORDER_ID = O.ID
WHERE O.ORDER_DATE <= DATEADD(year, 10, (SELECT MIN(ORDER_DATE) FROM ORDERS))
ORDER BY O.PRICE DESC

Note: Assuming we are running this query on SQL SERVER.