Review and Practice

JOIN Practice

Group and Filter Totals

A practical SQL review should connect rows from related tables, group them, and filter aggregate totals.

Program

Play the script to choose the minimum logged hours and review the grouped project totals.

min_hours
join_aggregate_practice.sql
Replay: real traced execution (multi-file project)
CREATE TABLE projects (id INTEGER, name TEXT);
INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
WITH params(min_hours) AS (VALUES (4)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;

CREATE TABLE projects (id INTEGER, name TEXT);
INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
WITH params(min_hours) AS (VALUES (3)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;

CREATE TABLE projects (id INTEGER, name TEXT);
INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
WITH params(min_hours) AS (VALUES (7)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;

  1. tables ← 1 row

    1CREATE TABLE projects (id INTEGER, name TEXT);2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
    values this step1 rowtables

  2. projects ← 3 rows

    1CREATE TABLE projects (id INTEGER, name TEXT);2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
    values this step3 rowsprojects

  3. tables ← 2 rows

    2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
    values this step2 rowstables

  4. task_logs ← 5 rows

    3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);5WITH params(min_hours) AS (VALUES (4)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;
    values this step5 rowstask_logs

  5. result ← 1 row

    4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);5WITH params(min_hours) AS (VALUES (4)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE projects (id INTEGER, name TEXT);2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
    values this step1 rowtables

  2. projects ← 3 rows

    1CREATE TABLE projects (id INTEGER, name TEXT);2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
    values this step3 rowsprojects

  3. tables ← 2 rows

    2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
    values this step2 rowstables

  4. task_logs ← 5 rows

    3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);5WITH params(min_hours) AS (VALUES (3)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;
    values this step5 rowstask_logs

  5. result ← 2 rows

    4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);5WITH params(min_hours) AS (VALUES (3)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE projects (id INTEGER, name TEXT);2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
    values this step1 rowtables

  2. projects ← 3 rows

    1CREATE TABLE projects (id INTEGER, name TEXT);2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
    values this step3 rowsprojects

  3. tables ← 2 rows

    2INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
    values this step2 rowstables

  4. task_logs ← 5 rows

    3CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);5WITH params(min_hours) AS (VALUES (7)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;
    values this step5 rowstask_logs

  5. result ← 1 row

    4INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);5WITH params(min_hours) AS (VALUES (7)), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;
    values this step1 rowresult
join The JOIN connects task log rows to their project names.
group aggregate SUM plus GROUP BY collapses many logs into one total per project.
aggregate filter Filtering after grouping keeps only projects whose total meets the selected threshold.