Tech News, Tech & Coding · Free Online Tools

IT Question Answer
Tools
Back to Data Science & Big Data
SQL for Data Science: Essential Queries Every Analyst Should Know

SQL for Data Science: Essential Queries Every Analyst Should Know

Data ScienceBy Muhammad Fareed
sqldata-sciencedata-analysisqueries

Short answer: the SQL you need most for data work is SELECT with WHERE, GROUP BY with aggregates, JOINs, subqueries or CTEs, and window functions. These let you filter, summarize, combine and rank data directly in the database before you move it to Python or a dashboard.

Select and filter

SELECT customer_id, order_date, total
FROM orders
WHERE order_date >= '2026-01-01'
  AND total > 100
ORDER BY total DESC
LIMIT 20;

Filter early so the database processes fewer rows, and select only the columns you need.

Aggregate with GROUP BY

SELECT country,
       COUNT(*) AS orders,
       SUM(total) AS revenue,
       AVG(total) AS avg_order
FROM orders
GROUP BY country
HAVING SUM(total) > 10000
ORDER BY revenue DESC;

WHERE filters rows before grouping, and HAVING filters groups after aggregation.

Join tables

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

An INNER JOIN keeps only matching rows. A LEFT JOIN keeps all rows from the left table, which is useful for finding customers with zero orders.

Use CTEs to keep queries readable

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

A common table expression names an intermediate result, so a complex query reads as a series of steps. Function names such as DATE_TRUNC vary between databases.

Window functions for rankings and running totals

SELECT customer_id,
       order_date,
       total,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_in_customer
FROM orders;

Window functions calculate across related rows without collapsing them, which makes them ideal for rankings, moving averages and year-over-year comparisons.

Habits that prevent mistakes

  • Check row counts before and after joins to catch accidental duplication.
  • Watch out for NULL, which is not equal to anything, even another NULL.
  • Test queries on a small sample first.
  • Format and comment your SQL so others can review it.

Practice ideas

  • Find the top five customers by revenue in each country.
  • Calculate month-over-month growth with a window function.
  • List customers who signed up but never ordered, using a LEFT JOIN.
  • Remove duplicate rows by keeping the latest record per ID.

Use a free sample database such as Chinook or a CSV imported into SQLite to try them.

Key takeaways

  • Master SELECT, WHERE, GROUP BY and JOIN first, because they cover most everyday questions.
  • Use CTEs to break complicated logic into readable steps.
  • Use window functions for rankings, running totals and comparisons between rows.
  • Always sanity-check row counts after joins and filters.

Analysis patterns you will use constantly

Month-over-month growth with a window function:

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly
ORDER BY month;

Conditional aggregation to pivot categories into columns:

SELECT country,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunds,
       ROUND(AVG(CASE WHEN channel = 'mobile' THEN amount END), 2) AS avg_mobile_order
FROM orders
GROUP BY country;

Date functions differ between databases: DATE_TRUNC works in PostgreSQL, DuckDB and Snowflake, while MySQL uses functions such as DATE_FORMAT. Check your database's documentation when copying examples.

Cohort and funnel thinking

Many business questions are about groups of users over time. A cohort query groups customers by the month they signed up and tracks how many are still active in later months. A funnel query counts how many users reach each step, such as visit, sign-up, first purchase. Both rely on the same building blocks shown above: CTEs to define each step, joins to connect events to users, and window functions or conditional aggregation to compare steps. Once you can write these, you can answer most product analytics questions without exporting data to a spreadsheet.

Common mistakes

  • Join fan-out. Joining orders to order items and then summing order totals counts each order several times. Aggregate before joining, or sum at the right level.
  • Integer division. In some databases 1 / 4 returns 0. Multiply by 1.0 or cast to a decimal type.
  • Filtering a LEFT JOIN in WHERE, which quietly turns it into an inner join. Put conditions on the right-hand table in the ON clause.
  • Ignoring NULLs in averages and comparisons.
  • Using SELECT * on large tables, which is slow and expensive in cloud warehouses.

Keep building with the SQL commands cheat sheet, practise with SQL interview questions, and learn how joins work in MySQL joins explained.

Frequently asked questions

Should I learn SQL or Python first for data science?

Learn the basics of both, but SQL is often the faster start because most company data lives in databases, and many analyst jobs rely on it daily.

Which SQL database should I practice with?

PostgreSQL, MySQL and SQLite are good free options. The core syntax is similar, with small differences in functions and date handling.

Is SQL still relevant with modern tools?

Yes. Cloud warehouses, BI tools and even many data science libraries use SQL or SQL-like syntax, so it remains one of the most valuable data skills.

How do I check that a query is correct?

Test it on a small subset where you can count results by hand, compare totals against a known report, and check row counts before and after each join to spot unexpected duplication.