One Pass Replaces Many WHERE Queries

Concepts covered: sqlConditionalAgg

Modern SQL has a cleaner syntax for conditional aggregation: the FILTER clause. SUM(amount) FILTER (WHERE tier = 'premium') is equivalent to SUM(CASE WHEN tier = 'premium' THEN amount END), but it reads as English and separates the aggregate from the filter visually. The FILTER clause is in the SQL standard since 2003 and supported on Postgres, SQLite, and DuckDB. It is not supported on MySQL, SQL Server, BigQuery, Snowflake, or Redshift. State the engine when you reach for it: 'on Postgres I would use FILTER; on Snowflake I'd fall back to CASE.' The FILTER clause syntax Why the FILTER form is preferred where supported Three reasons. First: readability. SUM(amount) FILTER (WHERE tier = 'premium') reads as 'sum the amount column, filtered to premium-tier rows.' SUM(CASE WHEN tier = 'premium

About This Interactive Section

This section is part of the Conditional Aggregation: Beginner lesson on DataDriven, a free data engineering interview prep platform. Each section includes explanations, worked examples, and hands-on code challenges that execute in real time. SQL queries run against a live database. Python runs in a sandboxed Docker container. Data modeling problems validate against interactive schema canvases. All content is framed around what data engineering interviewers actually test at companies like Meta, Google, Amazon, Netflix, Stripe, and Databricks.

How DataDriven Lessons Work

DataDriven combines four interview rounds (SQL, Python, Data Modeling, Pipeline Architecture) with adaptive difficulty and spaced repetition. Easy problems get harder as you improve. Weak concepts resurface until you master them. Your readiness score tracks progress across every topic interviewers test. Every lesson section ends with problems you solve by writing and running real code, not by picking multiple-choice answers.