SQL for Cross-System Operational Metrics | Dataford Interview Questions

Write a SQL query to analyze operational performance across multiple systems by joining tables for users, activities, and outcomes, then aggregating results by team and month.

Use the supplied tables and include activities without outcomes. Treat a missing user team as Unassigned and calculate success rate among activities with recorded outcomes.

Output

One row per team and calendar month, with team, activity_month, distinct_systems, total_activities, activities_with_outcomes, successful_outcomes, success_rate_pct, avg_resolution_hours, and success_rate_change_pct_points.
Include months containing activities even when no outcome exists. Sort by team, then month.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
full_name	VARCHAR(100)	User's display name
team	VARCHAR(50)	Operational team assigned to the user

activities

Column	Type	Description
activity_idPK	INT	Unique activity identifier
user_id	INT	User who performed the activity
system_name	VARCHAR(50)	System that recorded the activity
activity_at	TIMESTAMP	Timestamp when the activity occurred

outcomes

Column	Type	Description
outcome_idPK	INT	Unique outcome identifier
activity_id	INT	Activity associated with the outcome
status	VARCHAR(30)	Outcome status
outcome_at	TIMESTAMP	Timestamp when the outcome was recorded

Your question is SQL for Cross-System Operational Metrics. Start with the requirements and the three tables in the Question tab.

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

Problem