Introduction

This article covers four SQL concepts: window functions, joins, common table expressions (CTEs) and subqueries. Window functions let you rank rows, calculate running totals and compare a row to the ones around it without losing any data. Joins bring information from different tables together. CTEs break a long query into small named steps, and subqueries let one query use the result of another.

This article will use a hospital dataset which contains various tables. For each concept I explain what it does, show a query on that data and give the expected result.

1. Window Functions

A window function calculates across a set of rows related to the current row without collapsing them. Window function keeps every row and attaches the result to each one. Thus, for each row, SQL opens a "window" that shows some other rows. It looks through that window, calculates something, and writes the answer next to the row.

a. Ranking Functions

Ranking functions give each row a position inside its group.…