DataslopeAnalytics vs. Application Development
The same SQL keywords serve two very different jobs. Understanding the difference between application SQL and analytical SQL is the foundation of everything that follows.
Most people meet SQL inside an application: a website saves a user's
order, then reads it back on the "My Orders" page. That is real, useful
SQL — but it is a different job from what an analyst does, even though
both use the word SELECT. This page makes that difference explicit,
because almost every habit you build in this course flows from it.
Two jobs, one language
Think of a busy online store.
- The application asks tiny, sharply targeted questions, millions of times a day: "What is in cart #84213?", "Save this new order.", "Mark order #91 as shipped." Each touches a handful of rows and must return in milliseconds.
- The analyst asks broad, exploratory questions, a few times a day: "Which product categories grew fastest last quarter?", "Do first-time buyers spend less than returning ones?" Each sweeps across millions of rows to produce a small summary.
Same keywords. Opposite shape. An application query is a needle: find this one row, fast. An analytical query is a net: drag it across the whole ocean and weigh the catch.
What the analyst is really doing
When you write SQL for analysis, you are not maintaining the data — you are interrogating it. A typical analytical query:
- Reads data but rarely changes it. Inserts, updates, and deletes are the application's concern; the analyst mostly observes.
- Touches many rows to produce few. A thousand orders become one average; a year of traffic becomes twelve monthly numbers.
- Cares about columns, not rows. "Average
amount" reads one column across every row. Applications usually want a whole row at once; analysts usually want one column across the whole table. - Is exploratory. You rarely know the final query in advance. You run one, look at the result, and let it suggest the next question.
That last point matters most. Application SQL is written once and run forever. Analytical SQL is written, read, rewritten, and thrown away — a conversation with the data, not a fixed feature.
Why this changes how you write SQL
Because the goals differ, the good habits differ too. Throughout this course you will see analytical instincts that would look unusual in application code:
| Application instinct | Analytical instinct |
|---|---|
| Fetch a specific row by its key | Scan and summarize a whole column |
| Return full rows to display | Return computed numbers and groups |
| Optimize for many fast writes | Optimize for one big read |
| Query is fixed in the codebase | Query is rewritten every few minutes |
| "Did it save correctly?" | "What does this tell me?" |
Neither column is "better" SQL — they are tuned for different questions. This course lives entirely in the right-hand column.
A quick taste: this query does something deeply analytical — it reads every row but returns just one summarizing line per category. No single order is the answer; the pattern across orders is.
Notice there is no "find order #5" here. We deliberately forgot about individual orders to see the shape of the whole.
Check your understanding
Which question is analytical rather than application-oriented?
Save a new order to the database.
Show the details of order #4821 on a confirmation page.
Compare average order value between first-time and returning customers across the last year.
Update the shipping status of order #91 to "shipped".
An analyst's query usually...
Returns one full row matched by its primary key.
Reads many rows but returns only a small summary.
Inserts or updates rows as its main purpose.
Must complete in single-digit milliseconds to keep a web page responsive.
Why does analytical SQL tend to focus on columns more than whole rows?
Because SQL cannot return more than one column at a time.
Because analytical questions usually summarize one attribute across many rows (e.g. the average of one column).
Because applications are not allowed to read columns.
Because rows do not exist in analytical databases.
Next, we will see how this read-heavy, summarize-everything pattern has a name — OLAP — and how it differs from the OLTP work applications do.
Welcome
An analytical-SQL course for people who already know basic SQL but want to learn how analysts actually use it — to explore, summarize, and reason about large datasets with DuckDB.
Analytical vs. Transactional Workloads
OLTP keeps a business running; OLAP helps it understand itself. Why analytical workloads scan columns while transactional ones touch rows — and why that distinction shapes DuckDB.