SQL Tricks. WINDOWS

SQL Tricks WINDOWS.sql

> Subtract two months from a date, then return the new date
SELECT DATEADD(month, -2, '2017/08/25') AS DateAdd;
> https://www.w3schools.com/sql/func_sqlserver_dateadd.asp
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

>> `Windows Function` maintains `result row` where as `GROUP BY` squashes them to One

Most commonly used window functions in SQL:

SUM(), AVG(), MIN(), MAX()::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
  used to calculate the sum, average, minimum, and maximum of a group of rows, respectively.
  if you want to see data other than just aggregation we use window function
  1 Eg:
  SELECT *, MAX(salary) OVER() as max_salary
  FROM employee as e
  
  # `OVER()` whole table treated as single window
  
  Above will add max_salary with other columns
  
  2 Eg:
  SELECT *, MAX(salary) OVER(PARTITION BY dept_name) as max_salary
  FROM employee as e
  
  3 you cannot query windo directly in WHERE Eg:
  SELECT *, MAX(salary) OVER(PARTITION BY dept_name) as max_salary
  FROM employee as e
  WHERE max_salary > 200; WRONG
  
  RIGHT Method:
  SELECT *
  FROM (
      SELECT *, MAX(salary) OVER(PARTITION BY dept_name) as max_salary
      FROM employee as e
  ) x
  WHERE x.max_salary > 200;
  
  -OR-
  
  SELECT d.*
  FROM dept_name as d, (
      SELECT MAX(salary) OVER(PARTITION BY dept_name) as max_salary
      FROM employee
  ) x
  WHERE x.max_salary > 200;

RANK():::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
  used to rank each row with a unique integer. The rows that have the same values will receive the same rank. 
  However, the next rank in the sequence will be skipped. So, if two rows receive rank 1, the next rank assigned will be 3.
  Eg:
  

DENSE_RANK()::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
  it doesnt skip any rank. So, if two rows receive rank 1, the next rank assigned will be 2.
  SO, What is your IIT-JEE Dense Rank ??

ROW_NUMBER()::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
  assigns a unique row number to each row, regardless of the values in the rows.

LEAD() and LAG()::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
  are used to access data from the next row (LEAD) or the previous row (LAG) without using a self-join.
  > LEAD(col, <offset, default=1>, <value if goes out of scope of window, default=null>)
  > LAG (expression/col, <offset, default=1>, <value if goes out of scope of window, default=null>)
  > window reset to refault value whenever new paration starts/ends.
  
  Q. Table of mobiles (Brand, month, price), there is trend i.e. for 1 brand price is increasing month by month and for other price
    is decreasing month by month, Find for which Brand price is increasing and for which is decreasing ?
  A: will need to take care of if comparing last entry of brand will not compare with next record
  
  
  Q: https://leetcode.com/problems/rising-temperature/submissions/1206872931/
  using flag technique + checking if data has skipped some date or not
  
  SELECT x.id
  FROM (
    SELECT id,
      if(temperature - lag(temperature, 1, 99999) over(ORDER BY recordDate) > 0, 1, 0) as temp_change,
      if(recordDate - lag(recordDate, 1) over(ORDER BY recordDate) = 1, 1, 0) as omit_date
    FROM Weather
  ) as x
  WHERE x.temp_change = 1 AND x.omit_date = 1
  
  Q:
  
  
  
  
 Advanced.................................................
 Q. Find the difference of salary from average salary
 A. select emp_id, salary - avg(salary) over (partition by dept_id) from emp