# SQL - Window Functions (Data Analytics)

> ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, SUM OVER, PARTITION BY, frames

- 20 interview questions
- Mid-Level
- [Interview Questions: Data Analytics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions.md)

## 1. 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.

## 2. 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.

## 3. 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.

## 17 more questions available

- What is the main difference between GROUP BY and PARTITION BY?
- What does ROW_NUMBER() OVER(ORDER BY salary DESC) return?

Sign up for free: https://sharpskill.dev/en/login

## Other Data Analytics interview topics

- [Google Sheets - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/google-sheets-basics.md): 20 questions, Junior
- [Google Sheets - Advanced Formulas](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/google-sheets-advanced.md): 20 questions, Junior
- [SQL - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-basics.md): 25 questions, Junior
- [SQL - Aggregations and Grouping](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-aggregations.md): 20 questions, Junior
- [SQL - Joins](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-joins.md): 20 questions, Junior
- [BigQuery - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/bigquery-fundamentals.md): 20 questions, Junior
- [Data Cleaning](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/data-cleaning.md): 20 questions, Junior
- [KPIs and Business Metrics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/kpis-metrics.md): 20 questions, Junior
- [Descriptive Statistics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/descriptive-statistics.md): 20 questions, Junior
- [Zapier and No-Code Automation](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/zapier-automation.md): 20 questions, Junior
- [Data Visualization Principles](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/data-viz-principles.md): 20 questions, Junior
- [Python & Pandas - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/python-pandas-basics.md): 20 questions, Junior
- [Google Sheets - Automated Dashboards](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/google-sheets-dashboards.md): 20 questions, Mid-Level
- [SQL - Subqueries and CTEs](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-subqueries-ctes.md): 20 questions, Mid-Level
- [BigQuery - Advanced Features](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/bigquery-advanced.md): 20 questions, Mid-Level
- [Data Modeling](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/data-modeling.md): 20 questions, Mid-Level
- [Funnel and Conversion Analysis](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/funnel-analysis.md): 20 questions, Mid-Level
- [Cohort and Retention Analysis](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/cohort-retention.md): 20 questions, Mid-Level
- [Google Tag Manager and Tracking](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/gtm-tracking.md): 20 questions, Mid-Level
- [APIs and Webhooks](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/apis-webhooks.md): 20 questions, Mid-Level
- [dbt - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/dbt-fundamentals.md): 20 questions, Mid-Level
- [AB Testing and Applied Statistics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/ab-testing.md): 20 questions, Mid-Level
- [Looker Studio (Google Data Studio)](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/looker-studio.md): 20 questions, Mid-Level
- [Power BI - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/power-bi-fundamentals.md): 20 questions, Mid-Level
- [SQL - Advanced Analytical Queries](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-advanced-analytics.md): 20 questions, Senior
- [dbt - Advanced Features](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/dbt-advanced.md): 20 questions, Senior
- [Power BI - DAX and Advanced Dashboards](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/power-bi-dax.md): 20 questions, Senior
- [Python Analytics - Advanced Analysis and ML](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/python-analytics-advanced.md): 20 questions, Senior

---

Source: SharpSkill (https://sharpskill.dev), tech interview preparation for your real stack.
HTML version of this page: https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-window-functions