How this post came about
This post highlights the biggest hurdle I hit during the US Consumer Finance Complaints project: conditional aggregation. I wrote about the project overview here, but this post is specifically about the SQL problem that consumed most of my time.
The setup
I had a large SQL cheatsheet in my notes but little real experience beyond simple SELECT and WHERE conditions.
The weekend struggle
I spent an entire weekend trying different iterations of this SQL query — tweaking, rewriting, testing — trying to understand how conditional aggregation made sense. The query execution order kept tripping me up.
The breakthrough moment
This sounds obvious, but the framing that finally clicked was: I'm working with a table. It has rows and columns. It's not Python logic — it's a relational structure.
This helped me get past my confusion with COUNT. I kept thinking it was counting values in a specific column, but COUNT(*) counts rows. Those rows happen to have multiple columns, and SUM(CASE WHEN ...) lets me conditionally count a subset of those same rows.
A concrete example
This was the Query in question, which took me far too long to understand;
SELECT submitted_via, disputed, (disputed * 100.0 / total ) AS pct
FROM (
SELECT submitted_via,
COUNT(*) AS total,
SUM (
CASE WHEN "consumer_disputed?" = 'Yes' THEN 1 ELSE 0 END) as disputed
FROM consumer_complaints
GROUP BY submitted_via
)
ORDER BY pct ASC
There is an order of precedence on how this query is executed, where the subquery runs first. Inside the subquery, CASE WHEN returns a '1' or '0' for each row depending on whether consumer_disputed? is 'Yes', aliased as disputed. It's grouped by submitted_via, so COUNT(*) and SUM(CASE WHEN ...) both relate to each submitted_via group. The outer query then calculates the rate by dividing disputed by total.
I'd be lying if I said I fully understand this, or could replicate it on the fly. COUNT still trips me up — but I have to remind myself this is a table of rows. COUNT(*) counts every row in each submitted_via group, regardless of what the other columns say. Those rows also have values in consumer_disputed?, product, date, etc. The difference is that SUM(CASE WHEN ...) only adds to the tally if the consumer_disputed? column says 'Yes'. Both look at the same rows, just with different criteria.
Final thoughts
I've realised everything has a learning curve. It's always painful to learn new things, but with practice these concepts get internalised — until the next difficult thing comes along.
Our brains constantly like to remind us that we aren't able to learn complicated things. Our job is to prove them wrong.