Exercise 2: The FILTER Clause vs. MySQL's CASE WHEN Trick — Possible Solution
====================================================================

WHAT THE FILTER CLAUSE DOES
------------------------------
Per this chapter, "aggregate FILTER (WHERE condition) is a genuinely
cleaner way to do conditional aggregation than MySQL's CASE WHEN
trick." FILTER lets an aggregate function (like COUNT, SUM, AVG) be
applied only to the rows matching a specific condition, directly and
declaratively, without needing to reshape the values being aggregated
first.

THIS CHAPTER'S OWN EXAMPLE
------------------------------
Per this chapter, the Postgres version:

  SELECT COUNT(*) FILTER (WHERE status = 'completed') AS completed_count
  FROM orders;

directly states the intent: count the rows, but only those where
status = 'completed'. The MySQL equivalent the chapter gives:

  SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count
  FROM orders;

achieves the same result indirectly — it converts each row into
either a 1 (if it matches the condition) or a 0 (if it doesn't), and
then sums those converted values, which mathematically ends up
counting only the matching rows, but does so through an extra layer
of transformation (CASE WHEN ... THEN 1 ELSE 0 END) that has nothing
directly to do with the actual intent ("count only completed
orders").

WHY FILTER IS CLEANER
------------------------------
The FILTER version reads as exactly what it means: "count rows,
filtered to this condition." The CASE WHEN version requires the
reader to mentally simulate the 1/0 conversion and then realize that
summing 1s and 0s is equivalent to counting — an extra layer of
indirection for the same result. FILTER also generalizes more
naturally to aggregates where the CASE WHEN trick is more awkward to
construct (e.g. AVG FILTER (WHERE ...) is a direct, natural
expression, where an equivalent CASE WHEN-based average requires more
careful handling to avoid skewing the average with the substituted
0/NULL values).

WHY THIS WORKS AS AN ANSWER
------------------------------
It reproduces both queries from the chapter's own example exactly,
explains what each one actually computes and how, and identifies the
specific reason FILTER is considered cleaner — directness of intent
versus an indirect mathematical workaround.