SQL helps analysts ask questions of data stored in relational tables. Strong SQL work is not just writing a query that runs: it requires understanding table grain, selecting appropriate join keys and validating that the result still represents the intended records.
Begin with tables and row grain
Imagine an order table with one row per order and an order_items table with one row per product line. These tables have different grains. Joining them on order_id repeats order-level fields for each line, which may be correct for item analysis but can inflate order totals if the analyst sums the repeated order amount.
Filter and summarize deliberately
Use WHERE to limit records before aggregation, and GROUP BY to define the level of the summary. For example, a monthly category report might select the relevant date range and status, then group by month and category. Write down what the measure means—such as sum of line value—and how refunds, discounts or cancelled records are treated.
Use joins with checks
Choose an INNER JOIN when only matching records are appropriate, or a LEFT JOIN when unmatched rows from the main table should remain visible. Before and after a join, compare row counts and inspect keys that are null, duplicated or unmatched. If a supposedly unique dimension key has multiple rows, a join may multiply the result unexpectedly.
Window functions add context
Window functions calculate values across a related set of rows without collapsing them to one row per group. They can support rankings, running totals and comparisons with a previous period. A function such as LAG can show a prior month's value within each category, while a windowed SUM can create a cumulative total. Always specify the partition and ordering that match the question, and consider what should happen for ties or missing periods.
Make queries readable and repeatable
Use descriptive aliases, consistent indentation and common table expressions for multi-step logic. Avoid SELECT * in final analytical queries because it can obscure the fields used and make the output fragile when schemas change. Add comments for non-obvious business rules, not for syntax that is already clear.
Practice exercise
- Use fictional orders and order_items tables.
- Write a query for monthly item value by category.
- Document the join key, row grain, date range and included statuses.
- Compare row counts before and after the join and check one hand-calculated order.
- Add a running monthly total or prior-month comparison and state any limitations.
Use a synthetic dataset and clearly label it. A query result does not establish why a metric changed or prove that one factor caused another.
Continue with the Data Analyst Course and the SQL topic guide.