What is conditional aggregation and why use it instead of separate queries?

Conditional aggregation applies different conditions within aggregate functions to create multiple metrics in one query. More efficient (single pass), ensures consistency (same snapshot), and creates pivot-style reports.

Explain difference between CASE and FILTER syntax.

CASE uses SUM(CASE WHEN condition THEN 1 ELSE 0 END) and works everywhere but verbose. FILTER uses COUNT(*) FILTER (WHERE condition) and is cleaner but PostgreSQL/SQL Server 2022+ only.

Show active vs inactive vs trial users by plan with revenue

SELECT

plan_type,

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

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

SUM(CASE WHEN status = 'trial' THEN 1 ELSE 0 END) AS trial_users,

SUM(CASE WHEN status = 'active' THEN monthly_revenue ELSE 0 END) AS active_revenue

FROM subscriptions

GROUP BY plan_type;

Comprehensive status breakdown per plan with conditional counters and revenue metrics.