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.
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
The SQL query Using Aanalytic functions is
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
2. 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
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.
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
The ROWS BETWEEN clause specifies the range of rows to consider for calculating the SUM.
4. Write a SQL query to find the Median of sales of a product?
Solution:
The SQL query for calculating the median is
5. Write a SQL query to find the minimum sales of a product without using the group by clause.
Solution:
The SQL query is
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 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.
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 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
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
Comments