SQL - Window Functions
ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, SUM OVER, PARTITION BY, frames
1What is a window function in SQL?
What is a window function in SQL?
Answer
A window function performs a calculation across a set of rows related to the current row, without collapsing the results into a single output row. Unlike GROUP BY which reduces the number of rows, a window function preserves all rows in the result while adding a computed column. The OVER() clause defines the window over which the calculation is performed.
2Which clause is mandatory to define a window function?
Which clause is mandatory to define a window function?
Answer
The OVER() clause is mandatory for any window function. It tells the SQL engine that the function should be executed as a window function rather than a regular aggregate function. OVER() can contain PARTITION BY, ORDER BY and a frame specification, but it can also remain empty to apply the calculation across the entire result set.
3What is the role of PARTITION BY in a window function?
What is the role of PARTITION BY in a window function?
Answer
PARTITION BY divides the set of rows into independent groups (partitions), and the window function is applied separately to each partition. Unlike GROUP BY, PARTITION BY does not reduce the number of rows in the result. For example, SUM(sales) OVER(PARTITION BY region) calculates total sales per region while preserving each individual row.
What is the main difference between GROUP BY and PARTITION BY?
What does ROW_NUMBER() OVER(ORDER BY salary DESC) return?
+17 interview questions
Other Data Analytics interview topics
Google Sheets - Fundamentals
Google Sheets - Advanced Formulas
SQL - Fundamentals
SQL - Aggregations and Grouping
SQL - Joins
BigQuery - Fundamentals
Data Cleaning
KPIs and Business Metrics
Descriptive Statistics
Zapier and No-Code Automation
Data Visualization Principles
Python & Pandas - Fundamentals
Google Sheets - Automated Dashboards
SQL - Subqueries and CTEs
BigQuery - Advanced Features
Data Modeling
Funnel and Conversion Analysis
Cohort and Retention Analysis
Google Tag Manager and Tracking
APIs and Webhooks
dbt - Fundamentals
AB Testing and Applied Statistics
Looker Studio (Google Data Studio)
Power BI - Fundamentals
SQL - Advanced Analytical Queries
dbt - Advanced Features
Power BI - DAX and Advanced Dashboards
Python Analytics - Advanced Analysis and ML
Master Data Analytics for your next interview
Access all questions, flashcards, technical tests, code review exercises and interview simulators.
Start for free