Finding volatile items using SQL

In my last blog we i showed you how to identify volatile items with help of simple mathematical calculation. Today i am going to show you how to identify volatile items using SQL.

First we will need to create table in Oracle. This time we are not going to write sql to create and insert data into table. This time we will use oracle application builder to create new table and add data from external source (.xls).

As appose to traditional SQL method, creating a table and adding records to it is fairly simple if you use application builder. You can either copy and paste your data directly to application builder or you can import to it. Once we create table and add data from external source, we are ready to write SQL to calculate Standard deviation and average of each rooms.

 Select Distinct Room, STDEV,  AVERAGE from
(select Period_date,
Room, Qty,
Round(STDDEV(QTY) Over(Partition by Room),2) STDEV,
Round(AVG(QTY) OVER(Partition by Room),2) as AVerage
from hotel ) CV_CALC ;

ROOM                           STDEV                  AVERAGE               

------------------------------ ---------------------- ----------------------

Embassy                       27.37                  22.33                 

Honeymoon                 36.41                  48.5                  

Apartment                    3.61                   6.5                   

Classic                            0.89                   10.67                 

Presidential                   3.75                   6.42    

Since we calculate standard deviation and average, it is now time to calculate coefficient of variation. We will be using coefficient of variation to identify product volatility.

Select Room,
Round((STDEV/AVERAGE),2) as CV,
Case when  Round((STDEV/AVERAGE),2) > 1 then 'High'
When  Round((STDEV/AVERAGE),2) between .50 and 1 then 'Med'
when  Round((STDEV/AVERAGE),2) < .50 then 'Low'
end as CV_Volatility
from
(Select Distinct Room,STDEV, AVERAGE from
(select Period_date,
Room, Qty,
Round(STDDEV(QTY) Over(Partition by Room),2) STDEV,
Round(AVG(QTY) OVER(Partition by Room),2) as AVerage
from hotel ) CV_CALC ) CV ;

ROOM                           CV                     CV_VOLATILITY

------------------------------ ---------------------- -------------

Embassy                        1.23                   High         

Honeymoon                  0.75                   Med          

Apartment                     0.56                   Med          

Classic                             0.08                  Low          

Presidential                   0.58                   Med          

That is it for now. Will see you in next blogs.