365 Days of SQL: Day 172

Yo peeps! Hope you are having a good start of the day. I continued on to another lesson of the course. The course covers Windows function in detail, like too DETAIL! which is a good thing. I only covered half of the lesson as it was quite lengthy. One of the things I learnt was the advantage of using the windows function over GROUP BY.

When it comes to using GROUP BY for aggregating, you always need to specify the columns included in the SELECT clause otherwise it returns an error. This is not the case for windows functions.

Another key learning I want to highlight is the order of execution that also includes the windows function and having function which goes as follows:

In a nutshell - all SQL queries run in the following order:
	1. FROM
		a. WHERE filters
		b. ON table join conditions
	2. GROUP BY
	3. SELECT statements
		a. Derived column expressions
		b. Aggregate functions
	4. HAVING
	5. Window functions
	6. ORDER BY
        7. LIMIT

Windows calculation only take place after WHERE, JOINS, GROUP BY and HAVING.

Also, another key thing to note is that we can only use the outputs from the GROUP BY clause in the HAVING clause.