SQL for data analysis means using a small set of query patterns to answer business questions from a database: filtering rows, aggregating with GROUP BY, joining tables, structuring logic with CTEs, and ranking or comparing rows with window functions. Master those eight patterns and you can handle most entry-level analyst tasks and SQL interview questions.

Key takeaways

• About eight query patterns cover the large majority of day-to-day analyst SQL.
• JOINs and GROUP BY are where beginners struggle most, so practise them deliberately.
• Window functions are what separate beginner from interview-ready.
• Always sanity-check results: row counts, totals and duplicates.

The examples below use a simple shop database with orders (order_id, customer_id, order_date, amount) and customers (customer_id, country, signup_date).

1. Select and filter

SELECT order_id, amount
FROM orders
WHERE order_date >= '2026-01-01'
  AND amount > 100;

Business question: which large orders came in this year?

2. Aggregate with GROUP BY

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC;

Business question: who are our highest-value customers? Use HAVING to filter after aggregation (HAVING COUNT(*) >= 3).

3. Join tables

SELECT c.country, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.country;

• INNER JOIN: only matching rows.
• LEFT JOIN: every row from the left table, with NULLs where there’s no match. Use it to find customers who never ordered.

Common trap: joining on a non-unique key multiplies rows and inflates totals. Check row counts before and after every join.

4. Conditional logic with CASE

SELECT order_id,
  CASE WHEN amount >= 500 THEN 'large'
       WHEN amount >= 100 THEN 'medium'
       ELSE 'small' END AS order_size
FROM orders;

5. Dates

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

Date functions vary by dialect (DATE_TRUNC in PostgreSQL and BigQuery; FORMAT or DATEFROMPARTS in SQL Server), so check yours.

6. CTEs for readable logic

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue
FROM monthly
WHERE revenue > 10000;

CTEs (WITH ... AS) break complex analysis into named, testable steps. Interviewers like them because they make your thinking visible.

7. Window functions

SELECT month, revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_last_month,
  RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM monthly;

Window functions compute across related rows without collapsing them. Learn ROW_NUMBER, RANK, LAG/LEAD, and running totals with SUM() OVER (ORDER BY ...).

8. Handle NULLs and duplicates

SELECT COALESCE(country, 'Unknown') AS country, COUNT(DISTINCT customer_id)
FROM customers
GROUP BY 1;

COUNT(*) counts rows; COUNT(column) skips NULLs; COUNT(DISTINCT ...) removes duplicates. Mixing these up is a classic reporting error.

An 8-week SQL practice plan

Weeks	Focus	Practice target
1–2	SELECT, WHERE, ORDER BY, GROUP BY	30 single-table questions
3–4	JOINs, CASE, dates	30 multi-table questions on one dataset
5–6	CTEs, subqueries	Re-write earlier answers as CTEs
7–8	Window functions	20 ranking, running-total and period-comparison questions

Then use SQL in a portfolio project (ideas here). Using AI to help write queries is fine, but read every line and check the numbers; see how to use generative AI at work.

SQL is step two in our data analyst roadmap; the full skill stack is in data analyst skills.

Learn SQL with a mentor

Tutopiya is planning a live Data Analytics with Python & SQL cohort, with weekly SQL assignments marked by a practising analyst and a capstone on real data. It isn’t running yet. Join the waitlist for the SQL and data analytics programme. There’s nothing to pay.

Sources

• PostgreSQL documentation: queries
• Mode SQL tutorial
• Google BigQuery sandbox

Frequently asked questions

How long does it take to learn SQL for data analysis? +

The basics (SELECT, WHERE, GROUP BY, JOIN) take a few weeks of regular practice. Becoming comfortable with CTEs and window functions, the level most analyst interviews test, typically takes about two to three months at several hours a week.

Which SQL dialect should I learn? +

Start with any mainstream one, such as PostgreSQL, MySQL, SQL Server or BigQuery. The core syntax is shared, and switching dialects later mostly means learning different date and string functions.

Is SQL enough to get a data analyst job? +

SQL is the most important single skill, but most roles also expect spreadsheets and a BI tool such as Power BI or Tableau. Python is increasingly asked for too.

Where can I practise SQL for free? +

Free options include the Mode SQL tutorial, SQLZoo, the PostgreSQL documentation with a local install, and public datasets in Google BigQuery's sandbox.

Founding cohorts · nothing charged

Want to learn this live, with a mentor?

Tutopiya is planning small, mentor-led online cohorts in English test prep, data & AI, business & finance and digital careers. Tell us which one you want. The programmes with the most interest open first, and the people on the waitlist hear about them first.

See the planned programmes