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.
SELECT
priority,
ROUND(
AVG(JULIANDAY(resolved_date) - JULIANDAY(created_date)),
1
) AS avg_resolution_days
FROM issues
WHERE status = 'Resolved'
GROUP BY priority
ORDER BY avg_resolution_daysJULIANDAY() converts date strings to a floating-point day count since a fixed epoch, so subtracting two JULIANDAY values gives the exact number of days between them as a decimal. The WHERE status = 'Resolved' filter ensures unresolved issues (with NULL resolved_date) are excluded before aggregation. ROUND(..., 1) gives a clean one-decimal output. This is the canonical SQLite approach — straightforward, accurate, and matches the problem's explicit requirement to use JULIANDAY(). Ideal when the data is stored as ISO 8601 date strings.
SELECT
priority,
ROUND(
AVG(
CASE
WHEN status = 'Resolved' AND resolved_date IS NOT NULL
THEN CAST(JULIANDAY(resolved_date) - JULIANDAY(created_date) AS REAL)
ELSE NULL
END
),
1
) AS avg_resolution_days
FROM issues
GROUP BY priority
HAVING AVG(
CASE
WHEN status = 'Resolved' AND resolved_date IS NOT NULL
THEN 1 ELSE NULL
END
) IS NOT NULL
ORDER BY avg_resolution_daysThis variant filters inside AVG() using a CASE expression rather than a WHERE clause, allowing all priorities to appear in the GROUP BY even if none are resolved yet (they return NULL avg). The explicit NULL check on resolved_date prevents erroneous 0-day resolutions from corrupting the average. The HAVING clause removes priorities with zero resolved issues. This pattern is useful when you want to see all priorities in output and distinguish 'no resolved issues' from a genuine fast resolution time. More defensive but verbose.
The primary cost is a full scan of the issues table filtered by status = 'Resolved'. An index on (status, priority) dramatically speeds this up by allowing an index scan that skips all non-Resolved rows and pre-groups by priority. If the table has millions of issues across many projects, partitioning by status or project_key and filtering early is critical. JULIANDAY() is a scalar computation applied per row — it is lightweight but not indexable, so date filtering should be done on the raw date columns where possible. ROUND() and AVG() are negligible. For Jira-scale datasets (tens of millions of issues), consider a pre-aggregated materialized view by priority updated nightly. The ORDER BY avg_resolution_days adds a sort step that is cheap after grouping, since the number of distinct priorities is small (typically 3-5).
The most critical pitfall is forgetting the WHERE status = 'Resolved' filter — including open issues with NULL resolved_date causes JULIANDAY(NULL) to return NULL, which AVG() ignores, but it also means results may be computed on a mix of resolved and in-progress issues depending on the implementation. Another mistake is using DATE() subtraction which does not work in SQLite — JULIANDAY() is required. Candidates sometimes order by priority alphabetically instead of avg_resolution_days, missing the problem requirement. Rounding to 0 instead of 1 decimal is a common oversight. Forgetting to handle NULL resolved_date explicitly (beyond relying on WHERE) can cause surprises if the WHERE clause is later removed.
In SQLite, dates are stored as text or numbers with no native DATE type. Subtracting two text date strings produces NULL or nonsensical results. JULIANDAY() converts a date string to a floating-point number representing days since November 24, 4714 BC, making arithmetic subtraction valid and precise. It correctly handles month and year boundaries, leap years, and fractional days. It is the standard SQLite function for date difference calculations.
Add COUNT(*) to the SELECT clause — since the WHERE clause already filters to Resolved issues, COUNT(*) gives the count of resolved issues per priority. If using the CASE-based approach without a WHERE filter, use COUNT(CASE WHEN status = 'Resolved' THEN 1 END) instead. Showing sample size alongside the average is good analytical practice and often a follow-up expectation in interviews.
JULIANDAY(NULL) returns NULL, and AVG() skips NULLs, so those rows are silently excluded from the average — which may produce a misleadingly low resolution time. The safe approach is to add AND resolved_date IS NOT NULL to the WHERE clause explicitly, and optionally add a separate COUNT to flag how many Resolved issues lack a resolved_date for data quality monitoring.