JSTAcademy
0 XP
Dashboard
Crash Courses
Aggregates & Window Functions
11 min
Basic+150 XP
Crash Courses · Basic

Aggregates & Window Functions

Summarize data with GROUP BY aggregates and rank rows with window functions.
11 min read+150 XP on completionCert: PostgreSQL Crash Course
Tap any word in the text below to start reading from there.

Aggregates & Window Functions

Aggregates summarize data. Window functions compute across rows while keeping individual row context.

Aggregates with GROUP BY

-- Count completions per track
SELECT
  LEFT(course_id, 8) AS crash_id,  -- e.g. cc-js
  COUNT(*) AS completions,
  AVG(xp_earned) AS avg_xp,
  MAX(completed_at) AS last_completion
FROM progress
GROUP BY LEFT(course_id, 8)
ORDER BY completions DESC;

-- Filter groups with HAVING
SELECT
  user_id,
  COUNT(*) AS completed_count,
  SUM(xp_earned) AS total_xp
FROM progress
GROUP BY user_id
HAVING SUM(xp_earned) > 500  -- only users with 500+ total XP
ORDER BY total_xp DESC;

Window Functions

-- Rank users by total XP (dense rank — no gaps)
SELECT
  user_id,
  SUM(xp_earned) AS total_xp,
  DENSE_RANK() OVER (ORDER BY SUM(xp_earned) DESC) AS rank
FROM progress
GROUP BY user_id;

-- Row number within each track
SELECT
  course_id,
  user_id,
  xp_earned,
  ROW_NUMBER() OVER (
    PARTITION BY LEFT(course_id, 8)  -- reset per crash course
    ORDER BY xp_earned DESC
  ) AS rank_in_course
FROM progress;

Running Total

SELECT
  completed_at::date AS day,
  COUNT(*) AS daily_completions,
  SUM(COUNT(*)) OVER (ORDER BY completed_at::date) AS cumulative
FROM progress
GROUP BY completed_at::date
ORDER BY day;
0%