Readable SQL and Dialect Differences

Yara, the senior analyst on your team, reviews every query before it reaches a stakeholder. Her first comment on new analysts’ work is rarely about the logic. It’s “I can’t check this quickly.” Her second, this quarter, is practical: Cartwheel is moving its reporting to a PostgreSQL warehouse, and every query you’ve written needs to survive the move. This lesson covers both: SQL that reviewers can read, and SQL that travels.

A query someone else has to check

Here’s a real query from the support team’s old dashboard. It runs and returns plausible numbers:

select t.channel,count(*),avg(satisfaction) from support_tickets t,customers c where t.customer_id=c.customer_id and closed_at is not null and opened_at>='2025-01-01' group by 1 order by 3 desc;

A reviewer has to work to answer basic questions. Why is customers here? (It isn’t used; it’s leftover from an earlier version, and it’s a join someone must now reason about.) Is that a join condition or a filter? Which table does satisfaction belong to? What is column 3? Is the 2025 filter missing an upper bound or is that deliberate? Here’s the same analysis, written to be checked:

-- Support satisfaction by contact channel, tickets opened in 2025.
-- grain: one row per channel. Only closed tickets: open ones can't be rated yet.
SELECT
  t.channel,
  COUNT(*)                      AS closed_tickets,
  COUNT(t.satisfaction)         AS rated,
  ROUND(AVG(t.satisfaction), 2) AS avg_satisfaction
FROM support_tickets AS t
WHERE t.closed_at IS NOT NULL
  AND t.opened_at >= '2025-01-01'
  AND t.opened_at <  '2026-01-01'
GROUP BY t.channel
ORDER BY avg_satisfaction DESC;

The changes, in order of importance:

1. Remove what isn’t used. The unused join is gone. Every table in FROM is a potential fan-out or a filter in disguise, so it must earn its place.
2. Explicit joins. When you do join, write JOIN ... ON, never a comma plus a WHERE condition. The join condition then sits next to the table it belongs to.
3. Names over positions. Every output column has a name, and ORDER BY uses it. GROUP BY 1 is a contested convenience; names survive edits to the SELECT list.
4. One column per line, qualified with a role-based alias. Diffs in code review become one line per change.
5. Comments that say why. The header states the question and the grain; “only closed tickets” explains a decision. Don’t comment what the SQL plainly says.
6. Show the denominator. rated sits next to the average, the response-rate lesson from Section 1.

Uppercase keywords, leading or trailing commas, and indentation width are team conventions. Pick one, apply it everywhere, and let a formatter enforce it. Consistency matters far more than which style wins.

From SQLite to PostgreSQL

SQL is a standard, but every engine has its own functions and its own leniency. Almost everything in this course runs unchanged in PostgreSQL: joins, CTEs, recursive CTEs, window functions, FILTER, NULLIF, COALESCE, IS DISTINCT FROM. The differences cluster in a few places:

The biggest change is underneath the table. PostgreSQL has real date and timestamp types, so date columns are compared as dates, and date arithmetic returns intervals rather than numbers. Booleans are a real type too: SUM(status = 'cancelled') fails there because you can’t sum a boolean, which is why FILTER is the portable choice.

Two smaller differences catch people regularly. Integer division truncates in both engines (7 / 2 is 3), so keep writing 100.0 * for percentages. And PostgreSQL sorts NULLs last in ascending order where SQLite sorts them first, so add NULLS FIRST or NULLS LAST whenever NULL placement matters.

The exercise is a real porting job: a warehouse query that fails in SQLite. After that, the capstone puts the whole course together.