SQL

-- Row Number :
SELECT Salary,EmpName
FROM
  (
   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
   FROM EMPLOYEE
   ) As A
WHERE A.RowNum IN (2,3)

-- Sub Query :
SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )

-- Top Keyword :
SELECT TOP 1 salary
FROM (
      SELECT DISTINCT TOP n salary
      FROM employee
      ORDER BY salary DESC
      ) a
ORDER BY salary

select age, sum(weight) as total_weight
from cats 
group by age 
having sum(weight) > 12 order by age DESC

CASE input_expression /* usually a column_name */
WHEN conditional_expression1 THEN result_expression1
[WHEN conditional_expression2 THEN result_expression2]
[ELSE result_expression]
END

-- Example
SELECT LANGUAGE_NAME, RATING_CODE AS RATING, 
CASE RATING_CODE
WHEN '3' THEN 'DIFFICULT'
WHEN '2' THEN 'FAIRLY DIFFICULT'
WHEN '1' THEN 'EASY'
ELSE 'UNKNOWN'
END AS RATING_DESCRIPTION
FROM PROGRAMMING_LANGUAGES;

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

-- Running total
select name, sum(weight) over (order by name) as running_total_weight
from cats
order by name

-- Partitioned running total
select name, breed, sum(weight) over (partition by breed order by name) as running_total_weight
from cats

-- Counting - ROW_NUMBER()
select row_number() over (order by color,name) as unique_number, name, color 
from cats

-- Ordering - RANK()
select rank() over (order by weight desc) as ranking, weight, name 
from cats
order by ranking, name DESC

-- ntile() lets us divide our data into percentiles/quartiles. 
-- If we only need to see the lowest X% this can be done via ntile()
-- Quartiles - NTILES()
select name, weight, ntile(4) over ( order by weight) as weight_quartile
from cats 
order by weight_quartile, name

-- Dense Rank - DENSE_RANK() 
-- assigns each group a rank sequentially
select dense_rank() over (order by age DESC) as r, name,age
from cats
order by r, name

-- Compare previous row
-- LAG() lets us compare to the previous rows [and lead() the next rows] [min() also works] It is useful for looking for strange step ups/downs in data
select name, weight, coalesce(weight - lag(weight, 1) over (order by weight), 0) as weight_to_lose
FROM cats
order by weight

-- Compare previous with partition
select name, breed, weight, coalesce(weight - lag(weight, 1) over (partition by breed order by weight), 0) as weight_to_lose
from cats
order by weight, name

-- FIRST_VALUE()
-- lets us get the first value in a subgroup
select name, color, first_value(weight) over (partition by color order by weight) as lowest_weight_by_color
from cats
order by color, name

-- Window
select name, weight, ntile(2) over ntile_window as by_half, ntile(3) over ntile_window as thirds, ntile(4) over ntile_window as quart
from cats window ntile_window AS ( ORDER BY weight)
order by weight, name