Picture a typical first week as a junior analyst. A manager asks which region grew fastest last quarter. The data is not in a spreadsheet; it sits in the company's MySQL database, written there by the billing app every time a customer pays. You can wait for someone to send you an export, or you can write five lines of SQL and answer the question yourself. That difference is why analysts learn SQL.
What SQL gives an analyst that a spreadsheet does not
- Direct access to the source. No stale exports, no "which file is the latest" confusion.
- Scale. MySQL filters and groups millions of rows on the server and sends back only the answer.
- Repeatability. A query is a written record of how a number was produced. Rerun it next month and you get the same logic on fresh data.
- Joins. Combining orders, customers and products is one query, not a chain of lookups.
None of this replaces Excel, Power BI or Python. SQL is usually the first step: get the right data out in the right shape, then chart or model it elsewhere.
A small dataset to work with
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
sale_date DATE,
region VARCHAR(10),
channel VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO sales VALUES
(1,'2026-07-04','North','Online',12000),(2,'2026-07-18','South','Store',8500),
(3,'2026-07-25','North','Store',4000),(4,'2026-08-02','South','Online',15500),
(5,'2026-08-16','North','Online',9800),(6,'2026-08-29','West','Store',7200),
(7,'2026-09-05','South','Online',11000),(8,'2026-09-12','West','Online',13400),
(9,'2026-09-21','North','Store',6100),(10,'2026-09-28','South','Store',5900);
Question 1: is the data what I think it is?
Before any analysis, profile the table. This catches duplicates, gaps and surprise NULLs early:
SELECT COUNT(*) AS total_rows,
COUNT(DISTINCT sale_id) AS distinct_ids,
SUM(amount IS NULL) AS null_amounts,
MIN(sale_date), MAX(sale_date)
FROM sales;
| total_rows | distinct_ids | null_amounts | MIN(sale_date) | MAX(sale_date) |
|---|---|---|---|---|
| 10 | 10 | 0 | 2026-07-04 | 2026-09-28 |
SUM(amount IS NULL) works because a comparison returns 1 or 0 in MySQL. If total_rows and distinct_ids differed, you would have duplicates to explain before reporting anything. The guide to NULL values in MySQL covers what NULLs do to averages and counts.
Question 2: which region brings in the most?
SELECT region,
COUNT(*) AS orders,
SUM(amount) AS revenue,
ROUND(AVG(amount), 0) AS avg_order
FROM sales
GROUP BY region
ORDER BY revenue DESC;
| region | orders | revenue | avg_order |
|---|---|---|---|
| South | 4 | 40900.00 | 10225 |
| North | 4 | 31900.00 | 7975 |
| West | 2 | 20600.00 | 10300 |
South leads on revenue, but West has the highest average order on only two orders. Showing the count next to the average stops anyone from over-reading a small sample. This is the kind of context SQL makes cheap to add.
Question 3: how did the month change versus last month?
WITH m AS (
SELECT DATE_FORMAT(sale_date, '%Y-%m') AS mon, SUM(amount) AS revenue
FROM sales
GROUP BY mon
)
SELECT mon, revenue,
LAG(revenue) OVER (ORDER BY mon) AS prev_month,
ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY mon))
/ LAG(revenue) OVER (ORDER BY mon), 1) AS pct_change
FROM m
ORDER BY mon;
| mon | revenue | prev_month | pct_change |
|---|---|---|---|
| 2026-07 | 24500.00 | NULL | NULL |
| 2026-08 | 32500.00 | 24500.00 | 32.7 |
| 2026-09 | 36400.00 | 32500.00 | 12.0 |
The CTE builds monthly totals; LAG() reads the previous row so each month can be compared with the one before. Window functions like this need MySQL 8.0 or later. They are covered properly in the MySQL window functions tutorial.
Question 4: what share comes from each channel?
SELECT channel,
SUM(amount) AS revenue,
ROUND(100 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS share_pct
FROM sales
GROUP BY channel;
| channel | revenue | share_pct |
|---|---|---|
| Online | 61700.00 | 66.1 |
| Store | 31700.00 | 33.9 |
SUM(SUM(amount)) OVER () is the grand total across all groups, so the share is computed in the same query. You do not need a second query for the total.
Pitfalls analysts run into
- Join fan-out. Joining orders to order items and then summing order totals counts each order once per item. Aggregate first, then join.
- Date filters with functions.
WHERE YEAR(sale_date) = 2026cannot use an index;WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01'can. - Integer division surprises belong to other databases, not MySQL, where
/always returns a decimal. Round explicitly so reports look consistent. - Querying production at peak hours. Ask for a read replica or an analytics copy before running heavy queries.
If you are starting from zero, follow a step-by-step MySQL learning path, then practise with SQL interview questions for data analysts.