Basic Status Breakdown

basic

HR needs employee status by department: active, inactive, on leave

SELECT

department,

COUNT(*) AS total_employees,

SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,

SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count,

SUM(CASE WHEN status = 'on_leave' THEN 1 ELSE 0 END) AS on_leave_count

FROM employees

GROUP BY department;

department | total_employees | active_count | inactive_count | on_leave_count

Engineering | 45 | 42 | 2 | 1

Sales | 30 | 28 | 1 | 1

Each CASE creates conditional counter for specific status. All conditions mutually exclusive.

All

Revenue by Order Status

intermediate

Finance analyzes revenue impact by order status per customer

SELECT

customer_id,

COUNT(*) AS total_orders,

SUM(CASE WHEN status = 'completed' THEN order_total ELSE 0 END) AS completed_revenue,

SUM(CASE WHEN status = 'pending' THEN order_total ELSE 0 END) AS pending_revenue,

SUM(CASE WHEN status = 'cancelled' THEN order_total ELSE 0 END) AS lost_revenue

FROM orders

WHERE order_date >= '2024-01-01'

GROUP BY customer_id

HAVING SUM(CASE WHEN status = 'completed' THEN order_total ELSE 0 END) > 1000

ORDER BY completed_revenue DESC;

customer_id | total_orders | completed_revenue | pending_revenue | lost_revenue

102 | 5 | 2400.00 | 300.00 | 0.00

101 | 8 | 1500.00 | 500.00 | 200.00

SUM with CASE sums amounts only for matching statuses. HAVING filters to high-value customers.

All

Marketing Funnel Analysis

intermediate

Track campaign performance: impressions → clicks → signups → purchases

SELECT

campaign_id,