WINDOW FUNCTION
What is a window Function
It is a function that performs calculations across a set of rows related to the current one and returns the result of its computation for each row. Unlike GROUP BY, Window functions in PostgreSQL do not aggregate values and therefore do not reduce the number of rows in the output dataset. All rows from the input table are present in the output, and computations are attached to the corresponding rows.
In other words, you can think of a window function as a computation that for each row requests a value that is the result of some calculation applied to a certain set of rows (a window).
The commonly used Window Functions include,
ROW_NUMBER()
This function is used to retrieve single records out of a repeating table. It also gives each row a unique number in sequential order within a group.
Attached assigns a unique number to each doctor's visit in date order, starting from 1 for each doctor.
RANK()
The RANK() function assigns a rank to each row based on the order specified within the window. Rows with the same values receive the same rank, and the next rank is skipped.
Calculates the total bills associated with each doctor and ranks doctors from highest to lowest.
DENSE_RANK()
The DENSE_RANK() function operates similarly to the RANK() function except it does not skip any ranks even if rows have the same values.
** NTILE()**
NTILE() divides sorted partitions into n-number of equal groups. Each row in a partition is assigned a group number.
LEAD()
It retrieves a value from a following row. It is the counterpart of the LAG() function.
SUM
Returns the total sum of a numeric column.
AVG
Returns the average which is the mean value of a numeric column.
MIN
Returns the minimum or the smallest value from a column.
MAX
Returns the maximum or largest value from a column.
COUNT
Returns the number of rows that match a specified condition.
-The advantages on these functions are such that it is efficient for SQL to perform operations on entire sets inside the database engine.
It also handles large data, as SQL is designed for big tables and can process millions of rows reliably.
SQL JOINS
The JOIN clause is used to combine rows from two or more tables, based on a related column between them, i.e., a primary key and foreign key relationship between the two tables.
SQL JOINS TYPES
Inner join
Combines rows from two or more tables based on a related column between them. It returns only the records that have matching values in both tables; if a row in one table does not have a matching row in the other, it is excluded from the final result.
Left Join
In SQL, a LEFT JOIN (also known as a LEFT OUTER JOIN) returns all rows from the left table (the first table mentioned), along with the matching rows from the right table. If there is no match for a row from the left table, the columns from the right table will simply display NULL.
Right Join
The SQL RIGHT JOIN (or RIGHT OUTER JOIN) returns all records from the right table (table2) and the matched records from the left table (table1). If there is no match for a row from the right table, the resulting columns from the left table will contain NULL values.
Full Join
A SQL FULL JOIN (also known as a FULL OUTER JOIN) returns all records from both tables whenever there is a match in either the left or right table. If a row from the left table matches a row from the right table, the columns are combined. If there is no match, the missing sides are populated with NULL values. Essentially, a FULL JOIN acts as a combination of a LEFT JOIN and a RIGHT JOIN
Conclusion
The concept that took the most practice was realizing that a join isn't really "combining tables" in general — it's about deciding what happens to unmatched rows on each side. Once I started asking "what do I want to happen to rows without a match" for every join I wrote, choosing between INNER, LEFT, RIGHT, and FULL OUTER became a matter of answering that one question rather than memorizing four separate syntaxes.
Subqueries
A subquery is a query within a query. Subquery (also known as an inner query or nested query) is simply a SQL query embedded inside another SQL statement. It is used to fetch an intermediate dataset that the main (outer) query can use to filter, calculate, or manipulate data.
Common locations of Subqueries
- In the WHERE Clause (Dynamic Filtering)
This filters rows based on a calculation or a list generated by the inner query.
- In the SELECT Clause
Calculates a single value to display as a standalone column alongside individual row data
CTEs
A CTE does the same job — precomputing an intermediate result — but is defined upfront using WITH, giving it a name that can be reused.
It produces the exact same result as the nested subquery.
I learned that subqueries are queries placed inside another SQL query to help retrieve or filter data before the main query runs. They are useful when I need to use the result of one query to answer another question.
I also learned that CTEs (Common Table Expressions) allow me to create a temporary named result using the "WITH" keyword and then use it in another query. CTEs queries easier to read, understand and organize.
CTEs helps break down complex SQL problems into smaller and more manageable steps.
Top comments (0)