Advanced SQL (Data Analytics)
por Baraa Khatib Salkini
1. Setup Your Enviroement
2. Window Functions
2.1. Window Basics
2.2. Window vs Group By
2.3. Window Functions
2.3.1. Aggregate Func.
2.3.1.1. COUNT()
2.3.1.2. SUM()
2.3.1.3. AVG()
2.3.1.4. MAX()
2.3.1.5. MIN()
2.3.2. Rank Func.
2.3.2.1. ROW_NUMBER()
2.3.2.2. RANK()
2.3.2.3. DENSE_RANK()
2.3.2.4. NTILE()
2.3.3. Distribution Func.
2.3.3.1. CUME_DISTC()
2.3.3.2. PERCENT_RANK()
2.3.4. Value Func.
2.3.4.1. LAG()
2.3.4.2. LEAD()
2.3.4.3. FIRST_VALUE()
2.3.4.4. LAST_VALUE()
2.3.4.5. NTH_VALUE()
2.4. Window Frame
2.5. Window Use Cases
2.5.1. COUNTD
2.5.2. MAX
2.5.3. MIN
3. SET Operators
3.1. Basics
3.2. SET Operators vs JOINS
3.3. UNION
3.4. UNION
3.5. EXCEPT
3.6. INTERSECT
4. Case Statement
4.1. Concept
4.2. VIEWS
4.3. TEMP Tables
4.4. CREATE TABLE WITH SELECT
4.5. COMPARISION
5. Null Functions
5.1. Concept
5.2. ISNULL
5.3. NULLIF
5.4. COALESCE
5.5. CASE WHEN
5.6. IS NULL / IS NOT NULL
6. Date & Time Functions
6.1. Concept
6.2. VIEWS
6.3. TEMP Tables
6.4. CREATE TABLE WITH SELECT
6.5. COMPARISION
7. Subqueries
7.1. Subqueries Basics
7.1.1. MAX
7.1.2. MIN
7.2. Subqueries Examples
7.3. Subqueries vs Joins
7.3.1. MAX
7.3.2. MIN