Oracle SQL Analytical queries

Note: Most of these examples are taken from the standard tables emp, dept that comes with Oracle Editions. So that it would be easy for every developer. 
 
1) Question: Analytical queries and aggregations
          Aggregations is a function of calculation on a certain data. In Oracle we have some functions like SUM, AVG and many to get the aggregated results. Analytic functions also operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they do not reduce the number of rows returned by the query.
       Example:
         SELECT deptno, AVG(sal) FROM   emp GROUP BY deptno ORDER BY deptno;  
          This will result One row for each Deptno. 
           But Analytics gives spredsheet kind of results which will keep the source and analytical results side by side.
      Example:
          SELECT ename, deptno, sal,
                AVG(sal) OVER () AS avg_sal
          FROM   emp;  
           This will result all the records in emp table along with additional column overall avarage salary.  

2) Question: PARTIONED BY clause
          To get deptno wise average salary you can use PARTITION BY clause as follows.
         Example:

                SELECT ename, deptno, sal, AVG(sal) OVER () AS avg_sal,

                AVG(sal) OVER (PARTIONED BY deptno) AS avg_dept_sal
          FROM   emp;

3) Question: FIRST_VALUE function          

         To get FIRST Value in the list of records ( Note is not least or highest salary)
         Example

                 SELECT empno, deptno, sal, 
                         FIRST_VALUE(sal) OVER (PARTITION BY deptno) AS first_sal_in_dept
                 FROM   emp;

4) Question: FIRST_VALUE function with ORDER clause          
         To get FIRST Value in the list of records combined with ORDER BY clause to get highest or lowest salary in the department. 
         Example

               SELECT empno, deptno, sal, 
                     FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal desc) 

                             AS highest_sal_in_dept,
                     FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal ) 

                             AS lowest_sal_in_dept
               FROM   emp;

5) Question: Using IGNORE NULLS and NULLS LAST          
         To get rid of NULLs your can use IGNORE NULLS and NULLS LAST as follows
         Example 
           SELECT empno, deptno, sal,
                 FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno ORDER BY sal desc NULLS LAST) AS highest_sal_in_dept,
                FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno ORDER BY sal NULLS LAST ) AS lowest_sal_in_dept
           FROM   emp;

6) Question: TRICKY AVG() function with ORDER BY why so           
          We will be back to AVG() function , what happens when you use AVG() function with 
          ORDER BY clause in the windowing rows. This will calculated the DEPT_AVG so far from first record to current record.
           Explained in Question 7          
          Example
            SELECT empno, deptno, sal,
                 AVG(sal) OVER (PARTITION BY deptno ORDER BY sal) AS avg_dept_sal_sofar
            FROM   emp; 

       Note: For analytic functions that support the windowing_clause, the default action 

                    is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". 

7) Question: RANGE BETWEEN Clause             
         The reason for the Question 6 behavior is the default ROW BETWEEN behavior
         So the above query 6 is equivalent to following 

            SELECT empno, deptno, sal, 
                AVG(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING 
                AND CURRENT ROW ) AS avg_dept_sal_sofar
          FROM   emp; 

 
 8) Question: ROWS BETWEEN Clause
         There are multiple clauses depending on your need will result differently
            Example
            SELECT empno, deptno, sal,
               AVG(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING) AS 
                    avg_curr_curr_sal,
               AVG(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) AS 
                    avg_curr_next_sal,
               AVG(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) AS 
                    avg_prev_curr_sal,
               AVG(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 
                    avg_prev_next_sal,
               AVG(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) 
                     AS avg_allprev_curr_sal,
               AVG(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) 
                     AS avg_prev_allnext_sal
           FROM   emp; 


9) Question: LAG() function with ORDER BY 
          The LAG() function is used to derive some caliculations on how much is one is lagging behind the other or next record.
          Example
            SELECT empno, ename, sal,
                  LAG(sal, 1, 0) OVER (ORDER BY sal) AS prev_salary,
                 sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS salary_diff
            FROM   emp;

10) Question: LEAD() function with ORDER BY            
         We On converse to the Question 9, LEAD() will help to know how much is one leading compared to next record.
         Example
            SELECT empno, ename, sal,
                 LEAD(sal, 1, 0) OVER (ORDER BY sal) AS next_salary,
                 LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS salary_diff
            FROM   emp;

     Most widely used Analytical functions RANK and DENSE_RANK discussed in 

              practice page at Question 3.