Scalar subqueries power everyday business analytics: finding employees earning above company average, identifying products priced higher than category median, calculating sales performance vs targets, showing customer spending compared to segment average, and computing percentage differences from benchmarks. They turn complex comparisons into simple, readable queries.

E-commerce: Dynamic Pricing Tiers

Online store needs to categorize products into pricing tiers (Budget, Standard, Premium) based on category averages. Each product should show its tier and how it compares to category average.

-- Categorize products by price tier within category

SELECT

product_name,

category,

price,

(SELECT AVG(price)

FROM products p2

WHERE p2.category = p1.category) AS category_avg,

CASE

WHEN price >= (SELECT AVG(price) * 1.5 FROM products p2 WHERE p2.category = p1.category)

THEN 'Premium'

WHEN price >= (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category)

THEN 'Standard'

ELSE 'Budget'

END AS price_tier

FROM products p1

WHERE category IN ('Electronics', 'Clothing', 'Home')

ORDER BY category, price DESC;

Correlated scalar subquery calculates category-specific average for each product. Products automatically categorized into tiers. Premium products (>150% of category avg) highlighted for marketing. Index on (category, price) reduced query time from 3200ms to 180ms for 50,000 products.

All

HR Analytics: Compensation Benchmarking

HR dashboard needs to show each employee salary compared to multiple benchmarks: company average, department average, and role average. Identify employees significantly above or below market.

-- Multi-level salary comparison

WITH company_avg AS (

SELECT AVG(salary) AS avg_salary FROM employees WHERE active = true

)

SELECT

e.name,

e.department,

e.salary,

c.avg_salary AS company_avg,

(SELECT AVG(salary) FROM employees WHERE department = e.department) AS dept_avg,

(SELECT AVG(salary) FROM employees WHERE role = e.role) AS role_avg

FROM employees e, company_avg c

WHERE e.active = true;

Identified 23 employees >30% above role average (retention risk) and 47 employees <20% below role average (flight risk). HR used data to adjust compensation. Query runs in 250ms for 5,000 employees with proper indexes.

All