SQL for analysis

SQL for analysis is a narrower set of habits than SQL for building applications. The analyst reads far more than writes, works in tables somebody else designed and is judged on whether a number is right, not on whether a transaction commits quickly. Microsoft's DP-900 places the language inside its relational concepts area, beside normalisation and common database objects, and DMBOK files the stores themselves under Data Storage and Operations. Neither framework is about analysis as such, which is why the analyst's craft is learned mostly in practice. The questions arrive from colleagues in plain language, and translating them into the right tables is half the work.

Five moves that cover most questions

• Filter and select. Choosing columns and rows, with care over how NULL behaves in comparisons.
• Join. Combining tables on keys, and knowing before running the query how many rows each side should contribute.
• Aggregate. GROUP BY with counts, sums and averages, and HAVING to filter the groups themselves.
• Window. Rankings, running totals and comparisons with the previous row, computed without collapsing rows the way grouping does.
• Structure. Common table expressions that break a long query into named steps a colleague can read from top to bottom.

Behind the moves sits the idea that decides most errors: grain. Every table has one, a row per order, per order line or per customer per day. Join an orders table to its lines and then sum the order totals, and each order is counted once for every line it contains. The query runs, the figure looks plausible and it is wrong. Distinct counts carry the same trap in reverse: counting customers after joining them to their orders works only with a distinct count of the key, and an average of averages misleads whenever the groups differ in size. Comparing row counts before and after each join is the cheapest habit in analytics.

Where the queries run

Analytical SQL now mostly runs in cloud warehouses such as Snowflake, which charge for compute, so a careless scan of a large table costs money as well as time. Pipelines built through data engineering fill those warehouses, and dbt lets analysts turn their queries into versioned, tested models inside the same pipeline. For practice away from work, freeCodeCamp's Relational Databases certification has learners build and query real databases at no cost, and the SQL tool page covers dialects and engines. Designing the tables belongs to databases and SQL under programming. Turning results into charts continues in data visualisation and BI, and the framing of a question before any query is written sits under data analysis fundamentals.