SQL Practice Platform
Article •

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