Calculate the average time (in days) to resolve issues for each priority level. Only include resolved issues. Return priority and avg_resolution_days, rounded to 1 decimal place.
This SLA metrics problem reflects Atlassian's Jira issue tracking system. Understanding resolution times by priority helps teams meet service level agreements and identify process bottlenecks. This question tests your ability to work with dates, calculate time differences, perform aggregations by category, and filter data appropriately—fundamental skills for product analytics.
Concepts tested: JULIANDAY() for date arithmetic in SQLite, date difference calculations, AVG() aggregation, GROUP BY for priority-level metrics, WHERE clause for filtering resolved issues, and ROUND() for presentation. Understanding how to work with date/time data types in your SQL dialect is crucial.
Atlassian customers use similar queries to: generate SLA compliance reports for support teams, identify priority levels requiring process improvement, calculate team performance metrics, trigger escalations when resolution times exceed targets, power project management dashboards, and analyze the impact of process changes on resolution speed.
When tackling this Atlassian problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.