SQL Queries Interview Questions - Oracle Analytical Functions

SQL Queries Interview Questions - Oracle Analytical Functions

Analytic functions compute aggregate values based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. Most of the SQL developers won't use analytical functions because of its cryptic syntax or uncertainty about its logic of operation. Analytical functions saves lot of time in writing queries and gives better performance when compared to native SQL.

Before starting with the interview questions, we will see the difference between the aggregate functions and analytic functions with an example. I have used SALES TABLE as an example to solve the interview questions. Please create the below sales table in your oracle database.

CREATE TABLE SALES

(

SALE_ID INTEGER,

PRODUCT_ID INTEGER,

YEAR INTEGER,

Quantity INTEGER,

PRICE INTEGER

);

INSERT INTO SALES VALUES ( 1, 100, 2008, 10, 5000);

INSERT INTO SALES VALUES ( 2, 100, 2009, 12, 5000);

INSERT INTO SALES VALUES ( 3, 100, 2010, 25, 5000);

INSERT INTO SALES VALUES ( 4, 100, 2011, 16, 5000);

INSERT INTO SALES VALUES ( 5, 100, 2012, 8, 5000);

INSERT INTO SALES VALUES ( 6, 200, 2010, 10, 9000);

INSERT INTO SALES VALUES ( 7, 200, 2011, 15, 9000);

INSERT INTO SALES VALUES ( 8, 200, 2012, 20, 9000);

INSERT INTO SALES VALUES ( 9, 200, 2008, 13, 9000);

INSERT INTO SALES VALUES ( 10,200, 2009, 14, 9000);

INSERT INTO SALES VALUES ( 11, 300, 2010, 20, 7000);

INSERT INTO SALES VALUES ( 12, 300, 2011, 18, 7000);

INSERT INTO SALES VALUES ( 13, 300, 2012, 20, 7000);

INSERT INTO SALES VALUES ( 14, 300, 2008, 17, 7000);

INSERT INTO SALES VALUES ( 15, 300, 2009, 19, 7000);

COMMIT;

SELECT * FROM SALES;

SALE_ID PRODUCT_ID YEAR QUANTITY PRICE

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

1 100 2008 10 5000

2 100 2009 12 5000

3 100 2010 25 5000

4 100 2011 16 5000

5 100 2012 8 5000

6 200 2010 10 9000

7 200 2011 15 9000

8 200 2012 20 9000

9 200 2008 13 9000

10 200 2009 14 9000

11 300 2010 20 7000

12 300 2011 18 7000

13 300 2012 20 7000

14 300 2008 17 7000

15 300 2009 19 7000

Difference Between Aggregate and Analytic Functions:

Q. Write a query to find the number of products sold in each year?

The SQL query Using Aggregate functions is

SELECT Year,

COUNT(1) CNT

FROM SALES

GROUP BY YEAR;

YEAR CNT

---------

2009 3

2010 3

2011 3

2008 3

2012 3

The SQL query Using Aanalytic functions is

SELECT SALE_ID,

PRODUCT_ID,

Year,

QUANTITY,

PRICE,

COUNT(1) OVER (PARTITION BY YEAR) CNT

FROM SALES;

SALE_ID PRODUCT_ID YEAR QUANTITY PRICE CNT

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

9 200 2008 13 9000 3

1 100 2008 10 5000 3

14 300 2008 17 7000 3

15 300 2009 19 7000 3

2 100 2009 12 5000 3

10 200 2009 14 9000 3

11 300 2010 20 7000 3

6 200 2010 10 9000 3

3 100 2010 25 5000 3

12 300 2011 18 7000 3

4 100 2011 16 5000 3

7 200 2011 15 9000 3

13 300 2012 20 7000 3

5 100 2012 8 5000 3

8 200 2012 20 9000 3

From the ouputs, you can observe that the aggregate functions return only one row per group whereas analytic functions keeps all the rows in the gorup. Using the aggregate functions, the select clause contains only the columns specified in group by clause and aggregate functions whereas in analytic functions you can specify all the columns in the table.

The PARTITION BY clause is similar to GROUP By clause, it specifies the window of rows that the analytic funciton should operate on.

I hope you got some basic idea about aggregate and analytic functions. Now lets start with solving the Interview Questions on Oracle Analytic Functions.

1. Write a SQL query using the analytic function to find the total sales(QUANTITY) of each product?

Solution:

SUM analytic function can be used to find the total sales. The SQL query is

SELECT PRODUCT_ID,

QUANTITY,

SUM(QUANTITY) OVER( PARTITION BY PRODUCT_ID ) TOT_SALES

FROM SALES;

PRODUCT_ID QUANTITY TOT_SALES

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

100 12 71

100 10 71

100 25 71

100 16 71

100 8 71

200 15 72

200 10 72

200 20 72

200 14 72

200 13 72

300 20 94

300 18 94

300 17 94

300 20 94

300 19 94

2. . Write a SQL query to find the Median of sales of a product?

Solution:

The SQL query for calculating the median is

SELECT PRODUCT_ID,

QUANTITY,

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY QUANTITY ASC)

OVER (PARTITION BY PRODUCT_ID) MEDIAN

FROM SALES;

PRODUCT_ID QUANTITY MEDIAN

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

100 8 12

100 10 12

100 12 12

100 16 12

100 25 12

200 10 14

200 13 14

200 14 14

200 15 14

200 20 14

300 17 19

300 18 19

300 19 19

300 20 19

300 20 19

3. Write a SQL query to find the sum of sales of current row and previous 2 rows in a product group? Sort the data on sales and then find the sum.

Solution:

The sql query for the required ouput is

SELECT PRODUCT_ID,

QUANTITY,

SUM(QUANTITY) OVER(

PARTITION BY PRODUCT_ID

ORDER BY QUANTITY DESC

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) CALC_SALES

FROM SALES;

PRODUCT_ID QUANTITY CALC_SALES

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

100 25 25

100 16 41

100 12 53

100 10 38

100 8 30

200 20 20

200 15 35

200 14 49

200 13 42

200 10 37

300 20 20

300 20 40

300 19 59

300 18 57

300 17 54

The ROWS BETWEEN clause specifies the range of rows to consider for calculating the SUM.

4 Write a SQL query to find the cumulative sum of sales(QUANTITY) of each product? Here first sort the QUANTITY in ascendaing order for each product and then accumulate the QUANTITY.
Cumulative sum of QUANTITY for a product = QUANTITY of current row + sum of QUANTITIES all previous rows in that product.

Solution:

We have to use the option "ROWS UNBOUNDED PRECEDING" in the SUM analytic function to get the cumulative sum. The SQL query to get the ouput is

SELECT PRODUCT_ID,

QUANTITY,

SUM(QUANTITY) OVER( PARTITION BY PRODUCT_ID

ORDER BY QUANTITY ASC

ROWS UNBOUNDED PRECEDING) CUM_SALES

FROM SALES;

PRODUCT_ID QUANTITY CUM_SALES

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

100 8 8

100 10 18

100 12 30

100 16 46

100 25 71

200 10 10

200 13 23

200 14 37

200 15 52

200 20 72

300 17 17

300 18 35

300 19 54

300 20 74

300 20 94

The ORDER BY clause is used to sort the data. Here the ROWS UNBOUNDED PRECEDING option specifies that the SUM analytic function should operate on the current row and the pervious rows processed.

5. Write a SQL query to find the minimum sales of a product without using the group by clause.

Solution:

The SQL query is

SELECT PRODUCT_ID,

YEAR,

QUANTITY

FROM

(

SELECT PRODUCT_ID,

YEAR,

QUANTITY,

ROW_NUMBER() OVER(PARTITION BY PRODUCT_ID

ORDER BY QUANTITY ASC) MIN_SALE_RANK

FROM SALES

) WHERE MIN_SALE_RANK = 1;

PRODUCT_ID YEAR QUANTITY

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

100 2012 8

200 2010 10

300 2008 17