Advanced · 18 min

Project: a ranked learning report

Build a complete report with aggregation, inactive members, and ranking.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Build the report in stages

First summarize completed minutes at the member level. Then join that summary to all members and fill missing totals with zero. This establishes one row per member before ranking, so session counts cannot distort the ranking.

WITH totals AS (SELECT member_id, SUM(CASE WHEN completed = 1 THEN minutes ELSE 0 END) AS minutes FROM sessions GROUP BY member_id), report AS (SELECT m.id, m.name, COALESCE(t.minutes, 0) AS minutes FROM members AS m LEFT JOIN totals AS t ON t.member_id = m.id) SELECT name, minutes, DENSE_RANK() OVER (ORDER BY minutes DESC) AS rank FROM report ORDER BY minutes DESC, id;

Output

name | minutes | rank
Bo | 45 | 1
Ada | 20 | 2
Cy | 15 | 3
Dee | 0 | 4

Select a top row per member

Window results can be filtered in an outer query or CTE. This example ranks sessions within each member and then keeps the first. The ID tie-breaker ensures only one predictable row is selected when durations match.

WITH ranked AS (SELECT member_id, id, minutes, ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY minutes DESC, id) AS position FROM sessions) SELECT member_id, id, minutes FROM ranked WHERE position = 1 ORDER BY member_id;

Output

member_id | id | minutes
1 | 1 | 20
2 | 3 | 30
3 | 5 | 25

Put it into practice

Read the sample tables and predict the result before running the query.
Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return every member name, completed minutes, and dense rank by completed minutes. Include inactive members with zero and break display ties by member ID.

Your SQL query

Show solution

WITH totals AS (SELECT member_id, SUM(CASE WHEN completed = 1 THEN minutes ELSE 0 END) AS minutes FROM sessions GROUP BY member_id), report AS (SELECT m.id, m.name, COALESCE(t.minutes, 0) AS minutes FROM members AS m LEFT JOIN totals AS t ON t.member_id = m.id) SELECT name, minutes, DENSE_RANK() OVER (ORDER BY minutes DESC) AS rank FROM report ORDER BY minutes DESC, id;

The first stage computes completed minutes once per member. The left join includes inactivity, the fallback supplies zero, and the window function ranks the finished report without collapsing rows.

Practice