SQL Tutorial • • 2 min read

SQL Functions – Aggregate, Scalar & Window Functions Explained (With Examples & Practice)

Functions are one of the most powerful parts of SQL. Once I understood them properly, my queries went from basic to professional-level overnight.

Today, we're covering the three main types of SQL functions:

  • Aggregate Functions (GROUP BY heroes)
  • Scalar Functions (row-by-row transformations)
  • Window Functions (the game-changer for analytics)

I'll explain each with simple examples using our familiar e-commerce database (users, products, orders). Everything is ready to practice live at sqlpractice.in.

1. Aggregate Functions – Summarize Groups of Rows

These functions take multiple rows as input and return one value (usually with GROUP BY).

Practice Question 1 (Easy) Find the total number of orders per customer. Practice here →

Practice Question 2 (Medium) Calculate average order value per month. Practice here →

2. Scalar Functions – Transform Individual Values

These functions work on one row at a time and return one value per row.

Common Scalar Functions

Practice Question 3 (Easy) Show customer names in uppercase and email in lowercase. Practice here →

Practice Question 4 (Medium) Show order month name (January, February, etc.) instead of number. Practice here →

3. Window Functions – The Superpower (Analytics Magic)

Window functions perform calculations across a set of rows related to the current row — without collapsing them like GROUP BY.

They use OVER (PARTITION BY ... ORDER BY ...).

Most Important Window Functions

Practice Question 5 (Medium) Rank customers by total spending (use RANK and DENSE_RANK). Practice here →

Practice Question 6–8 (Hard)

  • Running total of daily sales
  • Top 3 products per category (if we add category)
  • Month-over-month growth in orders

Full window functions pack →

Quick Comparison

 

Common Interview Questions

  1. "Difference between WHERE and HAVING?" (with aggregate)
  2. "Explain window functions with example."
  3. "When to use RANK vs DENSE_RANK vs ROW_NUMBER?"
  4. "How to calculate running total?"

Final Thoughts

Functions are what make SQL powerful:

  • Aggregate → summaries
  • Scalar → clean data
  • Window → advanced analytics

 

Frequently Asked Questions

Is SQL Practice completely free to use?

Yes — SQL Practice is 100% free with no sign-up required. Our free online SQL compiler lets you write, run, and test SQL queries instantly in your browser. No installation needed.

What SQL dialect does the online compiler support?

Our online SQL compiler runs SQLite for most practice sets, supporting SELECT, JOINs, GROUP BY, subqueries, CTEs, and window functions. The Hospital schema also supports live PostgreSQL query execution.

Are the SQL questions suitable for TCS, Infosys and Deloitte interviews?

Yes — our SQL interview questions 2026 are curated from real hiring rounds at TCS, Infosys, Deloitte, and more. Questions range from Easy (basic SELECT) to Hard (window functions, CTEs, correlated subqueries).

Can I practice SQL on different databases and schemas?

Yes! The SQL Playground lets you switch between Hospital, E-Commerce, Employees, and more schemas to practice SQL queries online against real-world data models.

How does the auto-grader check my SQL query?

The built-in SQL auto-grader runs your query and compares the output against the expected result set. You instantly see a correct or incorrect verdict — plus the reference solution to learn the correct SQL syntax.

Read Next

2026-03-28

SQL Window Functions: The Most Underrated Yet Powerful Feature Many Developers Miss

You know `GROUP BY`, `JOINs`, `CTEs`, and indexes. You can write decent queries and even optimize slow ones using execution...

2026-03-28

Deloitte Data Engineer Interview Questions for 2-5 Years Experience: Complete Preparation Guide (2026)

Deloitte Data Engineer Interview Questions for 2-5 Years Experience: Complete Preparation Guide (2026) This guide focuses on...

2026-03-28

Deloitte SQL Interview Questions for 2-4 Years Experience: Complete Preparation Guide (2026)

Deloitte SQL Interview Questions for 2-4 Years Experience: Complete Preparation Guide (2026) This article compiles the most...