SQL Topic
Aggregation
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one summary value, usually combined with GROUP BY. Mastery means knowing what each one does with NULLs and duplicates.
When is Aggregation used?
Counting distinct users per day, averaging process times, summing sales per region, finding max/min per group.
Core syntax
COUNT(*), COUNT(col), COUNT(DISTINCT col), SUM(col), AVG(col), MIN(col), MAX(col) — usually alongside GROUP BY.Common mistakes
- Assuming COUNT(col) counts NULLs (it does not; COUNT(*) does).
- Forgetting DISTINCT inside COUNT when the question says 'distinct users'.
- Using AVG over rows that include NULLs and expecting them to be ignored (AVG does ignore them).
Practice Aggregation questions
Work through them in order — each question builds on the last.
- easyActive Users Per Day
- easyAverage Process Time Per Machine
- easySecond Highest Salary
- easySubjects Taught By Each Teacher
- mediumFirst and Last Order Per Customer
- mediumGame Play Analysis IV
- mediumImmediate Food Delivery
- mediumPercentage of Total Sales
- mediumRunning Total
- mediumSeven-Day Rolling Average
- mediumUsers With the Most Friends