SQL for Application Developers: From Questions to Queries You Can Trust

Start with the question and its grain

Every query answers a question, and every result has a grain: what one row represents. 'How many learners enrolled in each course last month?' has a grain of one row per course. Writing the grain down before writing SQL prevents most join mistakes, because you can immediately see when a query produces more rows than the question allows.

Know the tables you are querying

Sketch the relevant tables with their keys: courses(id, title, category), learners(id, email), enrolments(id, course_id, learner_id, enrolled_at, status). Note the relationships: one course has many enrolments, one learner has many enrolments. Also note the awkward parts, such as cancelled enrolments that remain in the table with status = 'cancelled'. These details change answers, so confirm them with whoever owns the data.

Run SELECT * FROM enrolments LIMIT 20 and look at real rows before writing anything clever.

Build the query in small steps

Start from the table that matches the grain, add filters, then joins, then aggregation. Run the query after each step and check the row count.

SELECT c.id, c.title, COUNT(e.id) AS enrolments
FROM courses AS c
LEFT JOIN enrolments AS e
  ON e.course_id = c.id
 AND e.status = 'active'
 AND e.enrolled_at >= DATE '2026-09-01'
 AND e.enrolled_at <  DATE '2026-10-01'
GROUP BY c.id, c.title
ORDER BY enrolments DESC;

Two details matter here. The date and status conditions are in the ON clause, not WHERE, so courses with no matching enrolments still appear with a count of zero. And COUNT(e.id) counts only matched rows, whereas COUNT(*) would count the unmatched course row as one.

Use half-open date ranges

Filter dates with >= start and < next period instead of BETWEEN with a last day. Timestamps on the last day after midnight are otherwise excluded, and the same pattern works for any period length. Be explicit about time zones: if enrolled_at is stored in UTC and the business reports in India Standard Time, convert before comparing, or the first and last hours of each month land in the wrong period.

Pre-aggregate before joining many-sided tables

When a question combines two one-to-many relationships, such as enrolments and reviews per course, joining both directly multiplies rows. Aggregate each in a common table expression first, then join the results to courses. This keeps the grain correct and makes each part independently testable.

Check results like a sceptic

• Compare totals with an independent count, such as the overall enrolment count for the month.
• Look for duplicated keys in the output with GROUP BY id HAVING COUNT(*) > 1.
• Spot-check a few rows against the application's own screens.
• Test edge cases: a course with no enrolments, a cancelled enrolment and a timestamp at the period boundary.

Make queries safe and maintainable in code

Always pass values as parameters through your driver or query builder, never by concatenating strings, which prevents SQL injection. Keep important reporting queries in version control with a comment stating the question and grain. When a query becomes slow, read its execution plan with EXPLAIN before adding indexes, so changes are based on evidence.

Checklist

• The question and grain are written down
• Real rows were inspected before writing the query
• Join conditions keep unmatched rows when the question needs them
• Date ranges are half-open and time-zone aware
• Results are checked against independent totals
• Values are passed as parameters