Increasing Orders

    

Write an SQL query to fetch all the ID and Name which has higher orders compared to its previous dates(yesterday) from the table.

Input :-

Orders table :-
ID is the Primary Key column for the Orders table.

ID	OrderDate	Orders	Name
1	2018-05-05	30	Shirts
2	2018-05-06	45	Pants
3	2018-05-07	23	Sweaters
4	2018-05-08	28	Joggers

Output :-

ID	Name
2	Pants
4	Joggers

Query :-

select o1.id, o1.name
from orders o1, Orders o2
where DATEDIFF(o1.OrderDate,o2.OrderDate)=1 and o1.Orders>o2.Orders;

Explanation : - 

This SQL query selects the ID and name of orders from a table called ‘Orders’. It joins the ‘Orders’ table with itself, creating two different instances of the table referred to as ‘o1’ and ‘o2’. The query then applies two conditions to filter the results.

The first condition is the DATEDIFF function, which calculates the difference in days between the OrderDate of ‘o1’ and ‘o2’. If the difference is exactly one day, the condition is met.

The second condition compares the Orders column of ‘o1’ with the Orders column of ‘o2’. It checks if the value in ‘o1.Orders’ is greater than the value in ‘o2.Orders’.

In summary, the query retrieves orders where the order dates of two instances from the ‘Orders’ table have a one-day difference, and the number of orders in the first instance is greater than the number of orders in the second instance. The result will include the ID and name of these qualifying orders.