Description

Login
Back

Intermediate · 18 min

Project: a member activity report

Combine joins, aggregates, and fallbacks into a complete report.

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');

Include inactive members

A useful activity report often needs every member, including those with no sessions. Combine LEFT JOIN with a count of matching keys and a fallback for an empty sum.

SELECT m.name, COUNT(s.id) AS sessions, COALESCE(SUM(s.minutes), 0) AS minutes FROM members AS m LEFT JOIN sessions AS s ON s.member_id = m.id GROUP BY m.id, m.name ORDER BY m.id;

Output

name | sessions | minutes
Ada | 2 | 30
Bo | 2 | 45
Cy | 2 | 40
Dee | 0 | 0

Make a focused report

A report can filter its final groups while preserving the underlying join semantics. This example keeps members with at least 40 minutes and sorts by the total, then by member ID to resolve ties.

SELECT m.name, SUM(s.minutes) AS minutes FROM members AS m JOIN sessions AS s ON s.member_id = m.id GROUP BY m.id, m.name HAVING SUM(s.minutes) >= 40 ORDER BY minutes DESC, m.id;

Output

name | minutes
Bo | 45
Cy | 40

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. 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, session count, and total minutes, using zero for missing totals. Order by member ID.

Show solution
SELECT m.name, COUNT(s.id) AS sessions, COALESCE(SUM(s.minutes), 0) AS minutes FROM members AS m LEFT JOIN sessions AS s ON s.member_id = m.id GROUP BY m.id, m.name ORDER BY m.id;

The left join retains Dee, COUNT ignores the missing session ID, and COALESCE turns the missing total into zero.

Practice