SQL

Window functions are one of the most powerful and underused features in SQL. Unlike GROUP BY, they let you perform calculations across a set of rows related to the current row — without collapsing the result set. The Core Syntax function_name() OVER ( PARTITION BY column1 ORDER BY column2 ROWS/RANGE BETWEEN ... AND ... ) Clause Purpose Required? PARTITION BY Divides rows into groups (like GROUP BY, but rows are kept) No ORDER BY Defines the order within each partition Depends on function Frame (ROWS BETWEEN) Defines which rows to include relative to the current row No 1. Ranking Functions ROW_NUMBER() Assigns a unique sequential integer to each row within a partition. No ties. ...

In our Invisible City, as the population grows and the subway map expands, Conductor Mickey sometimes finds that a simple Star Hub isn’t enough. When the “Station” information becomes a giant, messy pile of repeating words, it’s time to move to the Snowflake Schema. If the Star Schema is the “Express Line,” the Snowflake is the “Highly Detailed District.” What is a Snowflake Schema? In the previous post, we saw how the Star Schema keeps things simple. But what happens when your data starts repeating itself? What if you have 10 million rows, and you’ve written “United States” and “North America” 10 million times? ...