SQL Quest

Lessons, hints, explained solutions, the in-browser PostgreSQL editor and practice answer checks in your browser are available. Graded assessments, recorded progress from answer checks and new certificates aren't available yet.

Aggregation and Business Reporting

This track moves you from reading rows to summarising them. You will learn what COUNT, SUM, AVG, MIN and MAX really do (including with missing values), how GROUP BY turns rows into groups, how WHERE and HAVING filter at different stages, and how conditional and distinct aggregates build pivot-style reports. Then you apply it the way analysts do at work: revenue reports from order lines with the right statuses, monthly summaries, customer segments, and precisely defined KPIs such as average order value, repeat rate, cancellation rate, hotel occupancy and ADR, and clinic no-show rate. The track ends with the classic grouping mistakes (wrong grain, double counting, averaging averages) so you can spot them in any report. Datasets: Wadi Market (online retail), Nakheel Hotels and Shifa Clinics.

Start the track

Lessons

  1. Counting rowsCollapse a table into one answer with COUNT, and learn exactly what COUNT(*), COUNT(column) and COUNT(DISTINCT column) each count.14 min · 4 exercisesFree
  2. SUM, AVG, MIN and MAXTotal, average and find the extremes of a column, aggregate calculated values, and round results without falling into the integer-division trap.16 min · 4 exercises Members
  3. GROUP BY: one row per groupSplit rows into groups and aggregate each group separately, and understand why every selected column must be grouped or aggregated.18 min · 5 exercises Members
  4. Grouping by several columnsBreak results down by combinations of values (channel and status, year and channel) and by calculated keys such as the year of a date.16 min · 4 exercises Members
  5. HAVING: filtering groupsKeep or discard whole groups based on their aggregates, and know exactly when to use WHERE and when to use HAVING.16 min · 5 exercises Members
  6. Conditional aggregationCount and sum only the rows that meet a condition, side by side in one grouped query, with FILTER (WHERE …) or the portable CASE pattern, and turn the results into percentages.20 min · 4 exercises Members
  7. Distinct aggregatesCount different things inside groups with COUNT(DISTINCT …), see why distinct counts do not add up across groups, and avoid the SUM(DISTINCT …) trap.16 min · 5 exercises Members
  8. Aggregates and NULLKnow exactly how SUM, AVG, MIN, MAX and COUNT treat missing values, when to replace NULL with 0 and when replacing it would falsify the answer.18 min · 5 exercises Members
  9. Revenue reportsBuild revenue figures the way finance defines them: from order lines, for the right order statuses, separating booked sales, cash received, refunds and shipping.24 min · 4 exercises Members
  10. Monthly summariesGroup timestamps into calendar months with date_trunc, label them with to_char, filter whole periods with half-open ranges, and keep months in chronological order.20 min · 4 exercises Members
  11. Customer segmentationClassify customers into tiers with CASE applied to their aggregates (orders, spend, last purchase), then summarise the tiers in a two-step query.22 min · 4 exercises Members
  12. Developing KPIsTurn business KPIs (average order value, repeat rate, cancellation rate, no-show rate, occupancy and ADR) into precise definitions, and implement each one as a ratio of the right aggregates.26 min · 5 exercises Members
  13. Classic grouping mistakesRecognise and fix the errors that make grouped reports wrong while still running: the wrong grain, double counting after a join, averaging averages and adding up distinct counts.22 min · 4 exercises Members