SQL Joins and Date Aggregation | Dataford Interview Questions

Your question is SQL Joins and Date Aggregation. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

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