SQL Essentials - Level 2

Liked the masterclass! Give it a shot for free

Rajiv is all pumped up after acquiring new skills to perform data operations like filtering, writing conditional logic, aggregating, processing text and dates. SQL has become the most powerful skill in his repository.

Robert being a great promoter of SQL from the management side has got few more ad-hoc reporting requests from customers. He decided to connect to his favorite guy, Rajiv.

Rajiv received a mail from Robert.

Hey Rajiv,

I am amazed at the sleek reports you could create and perform complex text and date calculations which appeared to take considerable time in Excel. I remember the report I asked you to create previously.

Here's a simpler format but with a request for re-ordering :

Note the following :

The report should be first sorted by customer_id : The customer would like to check order details for one customer at a time.
Once sorted by customer_id, the report should be sorted by order purchase date but latest order first.

Is this doable in SQL?

Regards,

Robert

Rajiv knows how to sort data in SQL. Here's a quick code to sort orders data by order purchase date :

select * from orders
order by order_purchase_date

Similarly he can sort the data with customer_id too :

select * from orders
order by customer_id

However, he's confused how to bring both together, by customer_id the sorting should be ascending while within each customer_id, the sorting should be latest date first( descending).

He thought to do some research on Google and went to quickly validate with Sharon. Let's check out their conversation.

Rajiv : Hi Sharon. Don't mean to disturb you. I got a request for a minor tweak in the report I created earlier. Just wanted to validate if I am doing right.

Sharon : Hey, no worries. Tell me.

Rajiv : I am required to sort a table differently on two different columns. Here's what I did on Google.

I found the below example at w3schools.com link :

Sharon : You're absolutely right. Here we are telling the database to first sort the Customers table by Country in ascending order and then by CustomerName in descending order.

Rajiv : But, after sorting once by Country, if I sort by CustomerName, won't it disturb the earlier sorting?

Sharon : That's the beauty of SQL. It understands this problem very well. That's why the sorting follows an order from left to right. It will first sort by Country, then within each country it will sort by CustomerName.

In your case, In Excel if you order by customer_id first, it would look like this :

Now, if you order by order purchase date, it would look like this :

If you see, the ordering of customer_id got disturbed.

But, with SQL, it's just the query I am showing below :

-- orders data sorted by customer_id first and then by order_purchase_date
select
order_id,
customer_id,
vendor_id,
ship_mode,
order_status,
order_purchase_date,
order_delivered_customer_date
from orders
order by customer_id asc, order_purchase_date desc

-- Remember asc clause is optional. If nothing provided, the given column by default sorts in ascending order
-- Let's remove asc and then check

select
order_id,
customer_id,
vendor_id,
ship_mode,
order_status,
order_purchase_date,
order_delivered_customer_date
from orders
order by customer_id, order_purchase_date desc

-- Finally, you can save some effort typing the full column name in the order by clause. You can simply provide the column index (Just like you give in VLOOKUP in Excel)

select
order_id,
customer_id,
vendor_id,
ship_mode,
order_status,
order_purchase_date,
order_delivered_customer_date
from orders
order by 2,6 desc

-- Here 2 is the column index (position of customer_id) and 6 is the position of order_purchase_date

Rajiv : Amazing. SQL never let's us down :) Let me try this quickly and send the report.

Sharon : Cool best of luck! Just before we depart, Pick up the following 2 ideas too. Imagine, if Robert comes back asking :

Can you provide me list of top 3 best orders by delivery duration?
Can you provide me list of top 5 worst orders by delivery duration?

How will you approach this?

Rajiv : 1 sec. Searching Google. Here's my ask :

I got the following examples for SQL Server :

SELECT TOP 3 * FROM Customers;

So, I guess my results will be :


-- Top 3 orders by quickest delivery


select top 3
order_id,
order_purchase_date,
order_delivered_customer_date,
cast(order_delivered_customer_date - order_purchase_date as float) as delivery_duration
from orders
where order_status = 'delivered'
order by 4

-- Top 5 orders by slowest delivery

select top 5
order_id,
order_purchase_date,
order_delivered_customer_date,
cast(order_delivered_customer_date - order_purchase_date as float) as delivery_duration
from orders
where order_status = 'delivered'
order by 4 desc

Sharon : Superb! Finally, let me give this a tweak. What if Robert says : Give me top 3 orders only from 10th record onwards.

Rajiv : Ummm. Not getting an idea.

Sharon : Let me show you real quick. It's the idea of OFFSET, FETCH. Read about this here.

Here's how it will work in your case :

-- Top 3 quickest delivery after 10th record from top

select
order_id,
order_purchase_date,
order_delivered_customer_date,
cast(order_delivered_customer_date - order_purchase_date as float) as delivery_duration
from orders
where order_status = 'delivered'
order by 4
offset 10 rows
fetch next 3 rows only

-- -- Top 5 slowest delivery after 10th record from top

select
order_id,
order_purchase_date,
order_delivered_customer_date,
cast(order_delivered_customer_date - order_purchase_date as float) as delivery_duration
from orders
where order_status = 'delivered'
order by 4 desc
offset 10 rows
fetch next 5 rows only

--

Rajiv : Amazing. SQL never let's us down :) Let me try this quickly and send the report

Sharon : Cool best of luck!

Rajiv sends across the report to Robert. Very soon he received a new request as follows :

Can the ordering be like customer_id ascending and then order_delivered_customer_date descending?

His query started throwing the below error :

His query is :

select
order_id,
customer_id,
vendor_id,
ship_mode,
order_status,
order_purchase_date,
order_delivered_customer_date
from orders
order by 2, 9 desc

He quickly mailed his query and error to Sharon

Rajiv : Hi Sharon. Not sure what I am doing wrong here. But, I am getting an error. I mailed it to you.

Sharon : Dear Rajiv, there is no 9th column in your select list. Check this :

In order by clause, change 9 to 7 and it will work.

Rajiv : Ohh! I got it. I assumed that I have to take the column index from the main table. If you see in the orders table, the order_delivered_customer_date has index 9.

Sharon : No worries. Hope you got it. There is something called Order of Operations in SQL. It's an extremely important concept. According to it, the order by clause is the last operation which executes in an SQL query.

In the above code, here's how SQL works :

Step 1 : Refer orders table (FROM clause executes first)
Step 2 : Choose the required columns (SELECT clause executes next)
Step 3 : Apply ordering as instructed (ORDER BY clause executes last)

Although there are concepts you still need to know, But, the following diagram will be super helpful for you whenever you wish to understand how a typical SQL query is working :