Find all queries that took longer than the average execution time. Return query_id, execution_time, and how much longer than average (in seconds).
This performance monitoring problem reflects Databricks' focus on query optimization and observability. Identifying slow queries is the first step in performance tuning, helping data engineers prioritize optimization efforts. This question tests your understanding of subqueries, aggregate functions, and calculated columns—basic but essential skills for database performance analysis.
Concepts tested: scalar subquery for calculating average, comparison operators in WHERE clause, calculated columns for difference, and understanding when subqueries are evaluated (once for scalar subqueries). This is a straightforward application of 'compare to aggregate' pattern that appears frequently in analytics.
Databricks customers use similar queries to: identify queries requiring optimization in their data pipelines, generate performance reports for data engineering teams, trigger alerts when query times exceed thresholds, calculate SLA compliance for data freshness, prioritize cluster scaling decisions, and feed automated query optimization recommendations.
When tackling this Databricks 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
query_id,
execution_time,
execution_time - (SELECT AVG(execution_time) FROM query_logs) AS time_above_avg
FROM query_logs
WHERE execution_time > (SELECT AVG(execution_time) FROM query_logs)
ORDER BY execution_time DESCThis approach uses two scalar subqueries — one in the SELECT list and one in the WHERE clause — both computing the global average. While readable and compatible with all SQL dialects, the table is scanned at least twice (or more, depending on optimizer caching). It works well on small datasets and in environments where correlated subquery optimization is limited. The main downside is redundant computation: the average is calculated twice unless the query optimizer is smart enough to cache the result.
WITH avg_time AS (
SELECT AVG(execution_time) AS avg_exec
FROM query_logs
)
SELECT
q.query_id,
q.execution_time,
q.execution_time - a.avg_exec AS time_above_avg
FROM query_logs q
CROSS JOIN avg_time a
WHERE q.execution_time > a.avg_exec
ORDER BY q.execution_time DESCA CTE computes the average once and materializes it, then a CROSS JOIN attaches it to every qualifying row. This is more efficient because the aggregation runs a single time. It is also more readable and maintainable — you can inspect the CTE in isolation. Prefer this approach in production or when the table is large, as it avoids redundant full-table scans. Most modern optimizers (Spark SQL included) will treat the CTE as a single aggregation pass.
The core challenge here is computing a global average efficiently. The naive double-subquery approach may trigger two full sequential scans of query_logs on databases that do not cache scalar subquery results. In Databricks / Apache Spark SQL, CTEs are typically materialized once, making the CTE + CROSS JOIN approach significantly faster at scale. Adding an index on execution_time (in traditional RDBMS) enables the WHERE filter to use an index range scan rather than a full scan, reducing I/O substantially. For very large query_logs tables, consider pre-aggregating the average via a summary table or persisted view updated on a schedule. The ORDER BY execution_time DESC benefits from a descending index. At Petabyte scale in Spark, partition pruning on a date or job column would reduce shuffle cost before the aggregation step. Always EXPLAIN/ANALYZE the query to confirm the optimizer is not executing the average subquery once per row in a nested loop.
A frequent mistake is forgetting that NULL execution_time values are silently excluded by AVG() — if slow queries are unlogged and appear as NULL, they will skew results. Candidates sometimes write the subquery in only the WHERE clause and forget to include it in the SELECT list (or vice versa), resulting in incorrect time_above_avg values. Another pitfall is using >= instead of > for the filter, which may include queries exactly at the average — whether that is desired depends on the problem statement. Ordering DESC is often omitted, failing the requirement to surface the slowest queries first. Finally, candidates may compute average on a filtered subset rather than the full table, producing a misleading baseline.
AVG() ignores NULLs by default, so the average baseline could be misleading if NULLs represent failed/timed-out queries that should count as very long. You would use COALESCE(execution_time, 0) or a sentinel value depending on business logic, or add a WHERE execution_time IS NOT NULL clause explicitly to document the assumption and keep the intent clear.
In Databricks (Spark SQL), CTEs are evaluated once and reused, avoiding a second full scan. The double-subquery approach may trigger two separate Spark jobs or two aggregation stages depending on the optimizer. For large tables with billions of rows, this doubles the shuffle and I/O cost. The CTE approach is therefore strongly preferred in distributed environments where aggregation is expensive.
Yes — AVG(execution_time) OVER () computes the global average as a window function without grouping, making the result available per row in one pass. You then filter in an outer query: SELECT * FROM (SELECT *, execution_time - AVG(execution_time) OVER () AS time_above_avg FROM query_logs) WHERE time_above_avg > 0. This is a single scan and is often the most elegant approach.