SQL is a foundational language for querying relational databases and many analytical platforms. Data scientists use it to inspect schemas, filter records, combine related tables, aggregate measures, and create reproducible analytical datasets close to stored data.

SQL is one part of a broader toolkit. Statistics, experimental design, domain knowledge, Python or R, governance, and communication may be equally important depending on the role.

Begin with grain and keys

Before writing a join, state what one row represents in each table and verify primary- and foreign-key assumptions. A one-to-many join can multiply rows and inflate totals. Reconcile important counts and sums to a trusted baseline.

Use explicit time boundaries

“Last 30 days” can mean 30 reporting dates or the preceding 30×24 hours. Define the time zone and pass half-open boundaries from the application:

SELECT DISTINCT user_id
FROM transactions
WHERE purchase_timestamp >= :window_start
  AND purchase_timestamp < :window_end
  AND user_id IS NOT NULL;

A fixed condition such as purchase_date >= '2024-09-01' is not a rolling 30-day window and lacks an upper bound.

Choose the join from the population

To retain every recently registered user, including users who never logged in, aggregate logins and use a left join:

SELECT u.user_id,
       u.registration_timestamp,
       MAX(l.login_timestamp) AS last_login_timestamp
FROM users AS u
LEFT JOIN logins AS l ON l.user_id = u.user_id
WHERE u.registration_timestamp >= :registered_start
  AND u.registration_timestamp < :registered_end
GROUP BY u.user_id, u.registration_timestamp;

An inner join would exclude users without a login. Validate that user_id identifies one user and document duplicate behavior.

Rank deliberately

To return exactly five rows, use ROW_NUMBER() with a deterministic tie-breaker. To include ties at fifth place, use RANK() or DENSE_RANK() and accept that more than five rows may be returned.

WITH product_sales AS (
  SELECT product_id, SUM(net_sales) AS total_sales
  FROM sales
  WHERE sale_timestamp >= :period_start
    AND sale_timestamp < :period_end
  GROUP BY product_id
), ranked AS (
  SELECT product_id, total_sales,
         ROW_NUMBER() OVER (
           ORDER BY total_sales DESC, product_id
         ) AS row_num
  FROM product_sales
)
SELECT product_id, total_sales
FROM ranked
WHERE row_num <= 5
ORDER BY row_num;

Understand window offsets

LAG(value) OVER (PARTITION BY ... ORDER BY timestamp) returns a value from a previous observed row in that ordering, not necessarily the previous calendar day. Construct or join a complete calendar when calendar continuity matters, and define same-timestamp ordering.

Measure database performance

Pushing filters, joins, and aggregates into the database can reduce transfer and local memory use, but it is not automatically faster. Performance depends on volume, indexes, partitions, statistics, layout, predicates, joins, concurrency, engine, and the alternative computation. Inspect query plans with the engine's tools and test representative workloads. Use EXPLAIN ANALYZE cautiously because it executes the statement.

Use bound parameters from Python

from sqlalchemy import create_engine, text
import pandas as pd

engine = create_engine(database_url)
query = text("""
    SELECT customer_id, SUM(net_sales) AS total_sales
    FROM sales
    WHERE sale_timestamp >= :period_start
      AND sale_timestamp < :period_end
    GROUP BY customer_id
""")

with engine.connect() as connection:
    result_df = pd.read_sql_query(
        query,
        connection,
        params={"period_start": period_start, "period_end": period_end},
    )

Do not concatenate untrusted values into SQL. Use an approved secret store, TLS, a least-privileged read-only role, timeouts, and resource limits. Use chunking or governed extracts for results that may not fit memory. Validate row count, grain, types, nulls, precision, and time zones after loading.

Production checklist

  • Use explicit columns, aliases, typed parameters, and a documented time zone.
  • Define null, duplicate, late-arriving, deleted, test, and corrected-record behavior.
  • Check join cardinality and reconcile totals.
  • Make ordering deterministic for limits, ranks, and window offsets.
  • Version SQL, schemas, parameters, tests, and data-cut timestamps.
  • Use least privilege, encryption, audit logging, and governed sensitive-data access.

Push work into SQL when it produces a correct, maintainable plan and reduces data movement. Use pandas when the dataset fits memory and its libraries or iterative workflow better suit the task. For downstream preparation, see data cleaning in Python and feature engineering for machine learning. Statistical modeling choices, including the bias-variance trade-off, remain separate from query correctness.

Originally published August 13, 2025; technically reviewed and substantially updated September 4, 2026.