SQL practice: members and study sessions

Two independent assignments in the context of MentorHub. Can be performed separately from the main project: the first - after becoming familiar with the constraints and connections, the second - after JOIN and GROUP BY. Use PostgreSQL and regular SQL, without ORM.

The guideline is 25–40 minutes for each task. Formulate the solution yourself first, then open the tooltip and check the edge cases.

1. Who participates in the learning goal

Skill: design many-to-many relationships and protect data with database restrictions.

MentorHub has users and learning goals. Multiple users can work on the same goal; one user can participate in multiple purposes.

Requirements

• The user has a numeric identifier and a mandatory unique email. For this exercise, emails are compared in a case-sensitive manner; no normalization required.
• The goal has a numeric identifier and a required name. The name must contain at least one character other than a regular space.
• Participation links an existing user to an existing goal. The moment of entry and role are stored for it: owner or member.
• One user cannot enter the same goal twice, even with a different role.
• The target may not have any participants yet. There can be several owners: the “exactly one owner” restriction is not included in this task.
• When you delete a target, its participation records are automatically deleted. You cannot delete a user while he is participating in at least one goal.

What to do

1. Draw the connections and write CREATE TABLE for users, goals, goal_members. Identifiers can be set manually.
2. Add two users and two goals. One user participates in both goals, the second only in the first. Assign roles yourself.
3. Write a request: for the passed user ID, display the names of the goals, role and moment of entry. Sort by Target ID.
4. Check the restrictions with separate queries. Run each erroneous query separately or use savepoint: after an error, PostgreSQL does not continue normal commands within the same transaction until it is rolled back.

Self-test

What to submit: SQL table creation, initial data, target list query and check results. The constraints should work when executed directly in SQL, without Python checks.

Hint - after trying it yourself

A many-to-many relationship requires a separate table. Consider which pair of columns should be unique and why adding a role to that pair would break the condition. Foreign keys control the existence of related rows and deletion behavior. CHECK does not replace NOT NULL: check both constraints.

Discuss after decision: which indexes are created by the primary and unique keys, and which secondary index can help find members by target?

2. Who completed the weekly plan

Skill: connect tables, filter source rows and select groups by aggregate.

MentorHub records user training sessions. We need to find those who completed at least two sessions in a week with a total duration no less than 120 minutes.

Report rules

• Week: from 2026-09-14 00:00:00+00 up to and including 2026-09-21 00:00:00+00 exclusively.
• The session refers to the week from the start started_at. Its duration is taken into account in its entirety.
• Only sessions with status are taken into account completed.
• Output user_id, display_name, number of eligible sessions session_count and the amount of minutes total_minutes.
• Sorting: first the sum of minutes in descending order, if equal, the user ID in ascending order.
• The same names belong to different users. They should not be combined in the report.

Startup data

This set is independent from the first task. Temporary tables exist only in the current connection; complete your preparation and your solution in one session. To restart preparation, open a new connection.

CREATE TEMP TABLE practice_users (
    id integer PRIMARY KEY,
    display_name text NOT NULL
);

CREATE TEMP TABLE practice_sessions (
    id integer PRIMARY KEY,
    user_id integer NOT NULL REFERENCES practice_users(id),
    started_at timestamptz NOT NULL,
    duration_minutes integer NOT NULL CHECK (duration_minutes > 0),
    status text NOT NULL CHECK (status IN ('completed', 'cancelled'))
);

INSERT INTO practice_users (id, display_name) VALUES
    (1, 'Алекс'), (2, 'Мира'), (3, 'Алекс'),
    (4, 'Лев'), (5, 'Ника'), (6, 'Олег');

INSERT INTO practice_sessions
    (id, user_id, started_at, duration_minutes, status)
VALUES
    (1,  1, '2026-09-14 00:00:00+00', 60,  'completed'),
    (2,  1, '2026-09-16 18:00:00+00', 60,  'completed'),
    (3,  1, '2026-09-17 18:00:00+00', 90,  'cancelled'),
    (4,  1, '2026-09-21 00:00:00+00', 200, 'completed'),
    (5,  2, '2026-09-15 10:00:00+00', 80,  'completed'),
    (6,  2, '2026-09-20 23:59:59+00', 70,  'completed'),
    (7,  3, '2026-09-14 12:00:00+00', 30,  'completed'),
    (8,  3, '2026-09-18 12:00:00+00', 45,  'completed'),
    (9,  4, '2026-09-19 12:00:00+00', 180, 'completed'),
    (10, 5, '2026-09-13 23:59:59+00', 100, 'completed'),
    (11, 5, '2026-09-15 12:00:00+00', 60,  'completed'),
    (12, 5, '2026-09-19 12:00:00+00', 60,  'completed');

Expected result

What to submit: one SELECT query and a short explanation of why users 3, 4 and 6 are not in the report.

Self-test

• The beginning of the week is included in the interval, the beginning of the next week is not.
• A canceled session does not increase either the quantity or the amount.
• 120 minutes is enough, but one long session is not enough.
• Users with the same names remain separate groups.
• The result does not depend on the current date and time zone of the SQL client.

Hint - after trying it yourself

First, select rows based on time and status. Then group by user, count the quantity and amount. WHERE checks individual rows, HAVING checks the resulting groups. For boundaries, use explicit timestamptz values ​​with a UTC offset rather than converting the session time to date.

Complication: return all users, including those who have no completed sessions in a week. For these, show the quantity and amount as zero; remove selection according to the weekly plan. Explain how the arrangement of conditions across sessions affects LEFT JOIN.

After execution

Explain the solution to another student without reading the SQL out loud line by line: what data is selected, what the constraints are guaranteed, where the boundaries are checked. After a few days, repeat the request with a different period and threshold.