SQL
SQL is a standard language for accessing and manipulating databases.

What is SQL?

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

SQL Query structure
Offen when people decide to creat query, they design query in the following order:

1. FROM/JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET

FROM - The FROM command is used to specify which table to select or delete data from.
WHERE - The WHERE clause is used to filter records.
GROUP BY - The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
HAVING - The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
SELECT - The SELECT statement is used to select data from a database.
DISTINCT - The SELECT DISTINCT statement is used to return only distinct (different) values.
ORDER BY - The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
LIMIT - The SQL LIMIT statement is used to limit the number of records returned based on a limit value.

SQL Query examples:

SELECT user_name, company_name,  SUM(orser_cost)
FROM users
INNER JOIN company ON users.cmp_id = company.cmp_id
INNER JOIN orders ON orders.user_id = user_id
GROUP BY user_name, company_name
HAVING SUM(orser_cost) > 1000
ORDER BY company_name
LIMIT 10

SELECT count(distinct user_id)
FROM orders
WHERE order_date > '2021-01-01'

SQL Window Functions
A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
SQL Query examples with window function:
SUM, COUNT, and AVG
When using window functions, you can apply the same aggregates that you would under normal circumstances—SUM, COUNT, and AVG.

SELECT start_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_total,
       COUNT(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_count,
       AVG(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_avg
  FROM tutorial
 WHERE start_time < '2012-01-08'

ROW_NUMBER()
ROW_NUMBER() does just what it sounds like—displays the number of a given row. It starts are 1 and numbers the rows according to the ORDER BY part of the window statement. Using the PARTITION BY clause will allow you to begin counting 1 again in each partition.

SELECT start_terminal,
       start_time,
       duration_seconds,
       ROW_NUMBER() OVER (PARTITION BY start_terminal
                          ORDER BY start_time)
                    AS row_number
  FROM tutorial
 WHERE start_time < '2012-01-08'

RANK() and DENSE_RANK()
The RANK() function is a window function that assigns a rank to each row within a partition of a result set. The rows within a partition that have the same values will receive the same rank. The rank of the first row within a partition is one. The RANK() function adds the number of tied rows to the tied rank to calculate the rank of the next row, therefore, the ranks may not be consecutive.
The DENSE_RANK() is a window function that assigns a rank to each row within a partition of a result set. Unlike the RANK() function, the DENSE_RANK() function returns consecutive rank values. Rows in each partition receive the same ranks if they have the same values.

SELECT Department, Employee, Salary
FROM (
    SELECT Department.name as Department, Employee.name as Employee, Salary, 
    RANK() OVER(PARTITION BY Department.name ORDER BY Salary DESC) as rn
    FROM Employee
    inner join Department on Department.id = Employee.departmentId
) sub_select
WHERE rn = 1

SELECT score, dense_rank() OVER(ORDER BY score DESC) as "rank"
FROM Scores

Lag and Lead
SQL LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row.
SQL LEAD() is a window function that provides access to a row at a specified physical offset which comes after the current row.

with consective as (
    SELECT 
    id, 
    num as num1, 
    LAG(num,1) OVER(ORDER BY id) as num2,
    LAG(num,2) OVER(ORDER BY id) as num3
    FROM Logs
)
SELECT distinct num1 as ConsecutiveNums
FROM consective
WHERE num1 = num2 and num2 = num3