Summarize Operations Issues by Source | Dataford Interview Questions

Your question is Summarize Operations Issues by Source. 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

Acme Manufacturing tracks operational issues by source system. Write a PostgreSQL query to summarize issue volume and resolution speed for sources that currently have at least one open issue.

Requirements

Return one row per source_name.
Show the count of open issues as open_issue_count.
Show the count of open high-severity issues as high_open_issue_count.
Show the average number of days to resolve closed issues as avg_resolution_days, rounded to 1 decimal place.
Include only sources with at least one open issue.
Order results by open_issue_count descending, then source_name ascending.

Schema

issues

Column	Type	Description
issue_idPK	INT	Primary key for each issue
source_id	INT	Foreign key to the issue source
severity	VARCHAR(20)	Severity label such as low, medium, or high
status	VARCHAR(20)	Issue status such as open or closed
created_at	DATE	Date the issue was created
resolved_at	DATE	Date the issue was resolved, if closed

issue_sources

Column	Type	Description
source_idPK	INT	Primary key for the issue source
source_name	VARCHAR(50)	Human-readable source name

daily_activity

Column	Type	Description
activity_idPK	INT	Primary key for the activity row
issue_id	INT	Foreign key to the issue
activity_date	DATE	Date of the activity
minutes_spent	INT	Minutes spent on the issue that day