Mission 03 / DATA / QUERY

PostgreSQL and Databases

Inspect relational tables, join synthetic records, and aggregate results in a real browser-side PostgreSQL engine.

Starter lesson24 min

Briefing

Join and aggregate synthetic events

A relational database stores related facts in tables and combines them through keys. This SQL lab starts an isolated, in-memory PostgreSQL-compatible database in your browser and seeds it with synthetic rows.

Read the schema

`auth_events` stores event identifiers, device IDs, source addresses, and outcomes. `devices` maps an ID to a hostname and team. A primary key uniquely identifies a row; a foreign key records a relationship between tables.

Filter, join, aggregate

`WHERE` selects relevant rows before grouping. `JOIN ... ON` connects matching keys. `COUNT(*)` computes a group total and `GROUP BY` defines each group. Aggregate queries should explicitly name their result columns so downstream reporting is clear.

Keep the practice database local

The editor runs SQL against a fresh seeded PGlite database in a browser worker. It does not connect to MissionStack's application database. Changes are discarded when the query worker ends; use only synthetic training data.

Practical exercise

Run the mission

Join events to devices, keep failed events, group by hostname, and find the host with two failed attempts.

Progressive hint

Join `auth_events.device_id` to `devices.id`, filter `outcome = 'failed'`, then count each hostname.

Need another hint?

Group on `hostname`; use `COUNT(*)` for the failed rows and sort the count descending.

Training schema

devices(id, hostname, owner_team)

auth_events(id, device_id, source_ip, outcome)

Three invented events · two devices · in-memory PostgreSQL-compatible practice database

SELECT d.hostname, COUNT(*) AS failed_attempts
FROM auth_events AS e
JOIN devices AS d ON d.id = e.device_id
WHERE e.outcome = 'failed'
GROUP BY d.hostname
ORDER BY failed_attempts DESC;

SELECT d.hostname, COUNT(*) AS failed_attempts
FROM auth_events AS e
JOIN devices AS d ON d.id = e.device_id
WHERE e.outcome = 'failed'
GROUP BY d.hostname
ORDER BY failed_attempts DESC;

Output

Run the starter to see observable output. Code is saved in this browser.

PGlite · PostgreSQL in WebAssembly · fresh local training database for each run

Passing work is graded by the academy and saved to your private portfolio when you submit it.

Knowledge check

Which SQL operation combines matching rows from two related tables?

Pass both checks to save progress and earn 175 XP.