Subqueries in SELECT power analytics dashboards: showing customer order count and total spent, employee salary vs department average, product sales rank within category, account balance with pending transactions, student GPA vs major average. They add context columns without complex JOINs.

E-commerce: Customer Lifetime Value Dashboard

Marketing dashboard needs to show each customer with order statistics: total orders, total spent, average order value, and last order date. Used to identify high-value customers and churn risks.

-- Customer LTV metrics using SELECT subqueries

SELECT

c.id,

c.name,

c.email,

c.join_date,

(SELECT COUNT(*)

FROM orders

WHERE customer_id = c.id) AS lifetime_orders,

COALESCE(

(SELECT SUM(total)

FROM orders

WHERE customer_id = c.id),

0

) AS lifetime_value,

COALESCE(

(SELECT AVG(total)

FROM orders

WHERE customer_id = c.id),

0

) AS avg_order_value,

(SELECT MAX(order_date)

FROM orders

WHERE customer_id = c.id) AS last_order_date,

DATEDIFF(CURRENT_DATE,

(SELECT MAX(order_date) FROM orders WHERE customer_id = c.id)

) AS days_since_last_order

FROM customers c

WHERE c.status = 'active'

ORDER BY lifetime_value DESC;

Identified top 500 customers (>$5000 lifetime value) for VIP program - increased retention by 23%. Flagged 1200 customers with >90 days since last order as churn risks - re-engagement campaign recovered 18%. Query initially took 8500ms for 50,000 customers. Added index on orders(customer_id, total, order_date) - reduced to 950ms. Rewrote with JOIN - further reduced to 180ms.

All

HR Analytics: Compensation Equity Analysis

HR needs to compare each employee salary to department average and company average to identify pay equity issues and outliers.

-- Salary comparison using scalar and correlated subqueries

SELECT

e.employee_id,