SQL Joins and Date Aggregation

MediumSQL · PostgreSQL

Problem

Agile Defense's mission support dashboard needs a daily summary of operational events across missions and monitored systems. Write a PostgreSQL query that joins mission events to their mission and system records, then aggregates the results by event date and mission.

Requirements

Restrict results to events from April 1 through April 4, 2025, inclusive.
Return the event date, mission code, mission name, total event count, completed event count, distinct systems involved, and total impact score.
Use a LEFT JOIN for systems so events remain visible when a system record is unavailable. Treat a missing impact score as zero.
Return only date and mission groups with more than one event, ordered by date and mission code.

Schema

missions

Column	Type	Description
mission_idPK	INTEGER	Primary key for a mission
mission_code	VARCHAR(20)	Short mission identifier
mission_name	VARCHAR(100)	Mission display name

mission_events

Column	Type	Description
event_idPK	INTEGER	Primary key for an event
mission_id	INTEGER	References missions.mission_id
system_id	INTEGER	References systems.system_id when available
event_recorded_at	TIMESTAMP	Timestamp when the event was recorded
status	VARCHAR(20)	Current event status
impact_score	INTEGER	Numeric impact score

systems

Column	Type	Description
system_idPK	INTEGER	Primary key for a monitored system
system_name	VARCHAR(100)	System display name
system_category	VARCHAR(50)	System classification

Tablesmissionsmission_eventssystems