Counting Subsets Without Extra Queries

Concepts covered: sqlConditionalAgg

COUNT has a quirk that makes the conditional pattern slightly different from SUM. COUNT(*) counts every row in the group. COUNT(column) counts rows where column is not NULL. COUNT(CASE WHEN condition THEN 1 END) counts rows where the condition is TRUE (because non-matching rows return NULL, which COUNT skips). The 1 in the THEN is a sentinel; any non-NULL value works (COUNT does not care about the value, only that it is non-NULL). The canonical COUNT pattern Why no ELSE in the COUNT version In COUNT(CASE WHEN ... THEN 1 END), the absence of an ELSE means non-matching rows return NULL. COUNT skips NULL, so non-matching rows do not contribute. Adding ELSE 0 would make COUNT count those rows too (because 0 is not NULL), which would make every conditional COUNT equal to COUNT(*). The conventio

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.