• More
• How to write SQL?
• ROLLUP
• CUBE
• Window Functions
• row_number(), rank(), dense_rank(), ntile(n)
• percent_rank()
• cume_dist()
• first_value(), last_value()
• lag(), lead()

More

• Order of execution: https://sqlbolt.com/lesson/select_queries_order_of_execution
• SELECT EXTRACT(MONTH FROM '2018-08-01')
• EXISTS = IN, SOME, ANY

‣

Set theory

• EXCEPT = NOT IN

• SEMI-JOIN(in), ANTI-JOIN(not in) (subqueries)

How to write SQL?

SELECT <columns>
FROM <table>
JOIN <other tables>
WHERE <filter condition>
GROUP BY <grouping>
HAVING <aggregate filter> # similar to where but works after aggregation done
ORDER BY <column list>
LIMIT <number of rows>
"""
Order of execution:
FROM + JOIN
WHERE
GROUP BY
HAVING 
SELECT 
DISTINCT
ORDER BY
LIMIT / OFFSET
"""

ROLLUP

• Allows grouping the data
• https://www.sqltutorial.org/sql-rollup/

CUBE

Window Functions

• What are Window Functions in SQL? Window functions perform calculations on a set of rows that are related together. But, unlike the aggregate functions, windowing functions do not collapse the result of the rows into a single value
• https://www.sqltutorial.org/sql-window-functions/
• https://www.geeksforgeeks.org/window-functions-in-sql/
• MORE: https://www.linkedin.com/pulse/sql-window-functions-rows-range-unbounded-preceding-octavian-zarzu/?articleId=6630093981688705025 unbounded ...

row_number(), rank(), dense_rank(), ntile(n)

• https://www.sqlshack.com/overview-of-sql-rank-functions/ row_number(), rank(), dense_rank(), ntile()
• https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-row_number-function/ row_number() = numbering, if PARTITIONED BY is used then grouping happens, then again +1 (1,2,3,4,5,6)
• https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-rank-function/ rank() = happens ranking, if without PARTITIONED BY then 1,1,3 we skip 2 here.

If with PARTITIONED BY, then it happens over the partitions.

• dense_rank() = here we don't skip the numbers 1,1,1,2 but in rank() we do 1,1,1,4
• NTILE(N) = function to distribute the number of rows in the specified (N) number of groups.

If with PARTITIONED BY then if NTILE(2) then the same partitions are grouped till N, then move to another number.

percent_rank()

• https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-percent_rank-function/
Copy

SELECT 
    year,
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    FORMAT(
        PERCENT_RANK() OVER (
            PARTITION BY year
            ORDER BY net_sales DESC
        ) ,
    'P') percent_rank

FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    YEAR IN (2016,2017);

cume_dist()

• 3 / 11 = 0.27
• 5 / 11 = 0.45 because function will find other departments which have the number of headcounts less than or equal to 2. The result is 5.

first_value(), last_value()

• The FIRST_VALUE() function is a window function that returns the first value in an ordered partition of a result set.
• https://www.sqltutorial.org/sql-window-functions/sql-first_value/
• https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-first_value-function/

lag(), lead()

• LAG() and LEAD() just taking the others row's value