SQL is the single most important skill for any data scientist working with real data. Before you build a machine learning model, you need to get your data — and in most organisations, that data lives in a relational database. SQL is how you talk to that database. This guide covers everything from basic queries to advanced analytical functions used in real data science work.
Why SQL Matters More Than Most Data Scientists Realise
Many data science bootcamps spend weeks on Python and machine learning, then cover SQL in a single afternoon. That’s backwards. In practice, 60-80% of a data scientist’s time is spent querying, cleaning, and transforming data — all done in SQL. Companies like Airbnb, Uber, and Netflix run their entire analytics on SQL-based data warehouses. If you can’t write efficient SQL, you’ll be dependent on data engineers to fetch your data for you, which slows everything down.
SQL is also one of the most tested skills in data science interviews. Nearly every technical interview includes at least one SQL question, and senior roles often require you to write complex multi-table queries on the spot.
Core SQL Concepts Every Data Scientist Must Know
Start with the SELECT statement — the foundation of all data retrieval. The basic structure is SELECT (what you want), FROM (which table), WHERE (filter conditions), GROUP BY (aggregation groups), HAVING (filter on aggregates), and ORDER BY (sort). Here’s a practical example using a sales dataset:
-- Total revenue by product category for Q1 2026
SELECT
category,
COUNT(order_id) AS total_orders,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
MAX(amount) AS largest_order
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31'
AND status = 'completed'
GROUP BY category
HAVING SUM(amount) > 10000
ORDER BY total_revenue DESC;
JOINs are where most beginners struggle. An INNER JOIN returns rows that match in both tables. A LEFT JOIN returns all rows from the left table plus matching rows from the right — unmatched rows get NULL values. Understanding this difference is critical when dealing with optional relationships in your data:
-- Customers with their total spend (include customers with zero purchases)
SELECT
c.customer_id,
c.name,
c.email,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email
ORDER BY total_spent DESC;
Window Functions: The SQL Feature Data Scientists Love
Window functions perform calculations across a set of rows related to the current row without collapsing them into groups. They’re invaluable for running totals, rankings, moving averages, and comparing each row to an aggregate. The OVER() clause defines the window:
-- Rank customers by revenue within each region
SELECT
region,
customer_name,
revenue,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS regional_rank,
SUM(revenue) OVER (PARTITION BY region) AS region_total,
revenue / SUM(revenue) OVER (PARTITION BY region) * 100 AS pct_of_region,
AVG(revenue) OVER (
PARTITION BY region
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_avg
FROM customer_sales;
The LEAD() and LAG() functions let you compare a row to the next or previous row — perfect for churn analysis, trend detection, and time-series work:
-- Month-over-month revenue growth
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month)) /
LAG(revenue) OVER (ORDER BY month) * 100, 2
) AS mom_growth_pct
FROM monthly_revenue;
Subqueries and CTEs for Complex Analysis
Common Table Expressions (CTEs) make complex queries readable by breaking them into named steps. They’re far more readable than nested subqueries and perform identically in most databases. Use them whenever you need to reference an intermediate result more than once:
-- Find high-value customers who haven't purchased in 90 days (churn risk)
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS lifetime_value,
MAX(order_date) AS last_purchase_date
FROM orders
GROUP BY customer_id
),
high_value AS (
SELECT *
FROM customer_totals
WHERE lifetime_value > 500
)
SELECT
c.name,
c.email,
hv.lifetime_value,
hv.last_purchase_date,
DATEDIFF(CURRENT_DATE, hv.last_purchase_date) AS days_since_purchase
FROM high_value hv
JOIN customers c ON hv.customer_id = c.customer_id
WHERE DATEDIFF(CURRENT_DATE, hv.last_purchase_date) > 90
ORDER BY hv.lifetime_value DESC;
For data science work, you’ll also frequently use subqueries in WHERE clauses for filtering based on aggregate conditions, and correlated subqueries for row-by-row calculations — though CTEs are almost always cleaner for the latter.
SQL Performance Tips for Large Datasets
As data grows into millions of rows, query performance matters. Index the columns you filter on most — typically foreign keys, date columns, and high-cardinality status columns. Avoid using functions on indexed columns in WHERE clauses, as this prevents index usage. Write WHERE order_date >= '2026-01-01' rather than WHERE YEAR(order_date) = 2026. Select only the columns you need rather than SELECT * — this reduces I/O significantly on column-store databases like BigQuery, Redshift, and Snowflake.
Frequently Asked Questions
Do I need SQL if I use Python for data science?
Yes. Python and SQL are complementary — SQL retrieves and transforms data at the database level (where it’s efficient at scale), and Python handles modelling, visualisation, and everything SQL can’t. Most production data science workflows use both.
Which SQL dialect should I learn?
Standard SQL covers 90% of what you’ll use across PostgreSQL, MySQL, SQLite, BigQuery, Redshift, and Snowflake. Start with PostgreSQL or MySQL — both are free and widely used. The syntax differences between dialects are minor once you know the fundamentals.
How long does it take to get job-ready at SQL?
With focused practice — solving real query problems daily — most people reach a competent intermediate level (JOINs, aggregations, subqueries) within 4-6 weeks. Window functions and query optimisation take another 2-4 weeks of practice on real datasets.
