SQL for Data Science: Essential Queries and Portfolio Exercises

SQL for data science is not about memorizing every database command. It is about turning a business or research question into a reproducible dataset at the correct level of detail. A beginner should learn to select and filter rows, summarize groups, join tables safely, handle nulls, structure multi-step logic, calculate time-based metrics, use window functions, validate results, and prepare analysis or modeling tables. Those skills cover a large share of the SQL work that happens before visualization, statistics, and machine learning.

The broader Data Science & AI career guide places SQL beside Python, statistics, visualization, model evaluation, and communication. This article goes deeper into the SQL layer. It uses one consistent commerce schema, explains the essential query patterns, and gives portfolio exercises that prove more than syntax. The goal is to help you show that you understand data grain, business logic, quality checks, and analytical reasoning.

The examples use PostgreSQL-style SQL because it is readable and well documented. Core ideas such as SELECT, WHERE, GROUP BY, joins, common table expressions, and window functions transfer across major relational databases and cloud warehouses. Date functions, type casts, null behavior, and a few convenience clauses vary by platform, so always check the documentation for the database you actually use.

Key Takeaways

Why SQL Matters in Data Science

Data science begins long before a model is trained. The U.S. Bureau of Labor Statistics description of data scientists includes identifying useful data, collecting and analyzing it, creating and validating models, presenting findings, and making recommendations. In an organization, much of the relevant information is stored in relational databases or analytical warehouses. SQL is the language that lets you ask those systems for the exact rows, columns, groups, and relationships needed for the next decision.

SQL is declarative. You describe the result you want, and the database planner decides how to produce it. That is different from writing a loop that tells a program how to process every row. The declarative model is powerful because the database can optimize scans, filters, joins, and aggregations using information about storage, indexes, partitions, and data distribution.

For a data scientist, the practical value is speed and control. SQL can reduce stored records to an analysis-ready result before anything reaches a notebook, calculate reusable metrics, expose data-quality problems, and reveal how business entities connect. It also forces questions that a flat CSV can hide: What is one row? Which key is unique? Can an order have several items? Which timestamp defines the event?

The official PostgreSQL tutorial introduces relational concepts, querying, joins, aggregates, views, foreign keys, transactions, and window functions. That sequence is a useful reminder that SQL is not merely a list of keywords. It is a way to reason about sets of related records.

SQL and Python solve different parts of the workflow

The pandas function read_sql_query is one common bridge: it executes a SQL query through a supported connection and returns the result as a DataFrame. The important design decision is not whether SQL or Python is "better." It is where each transformation should happen so the workflow remains correct, efficient, and understandable.

Start With Grain, Keys, and a Small Relational Model

Most serious SQL mistakes are not syntax errors. They are meaning errors. The query runs, but it counts the wrong thing, duplicates money, drops customers, mixes time periods, or creates features using information that was unavailable at prediction time. The best defense is to define grain before writing code.

If the intended output is one row per customer, every join and grouping choice should be judged against that promise. A join from customers to orders creates one row per customer-order combination, not one row per customer. A second join to order items creates one row per customer-order-item combination. That may be correct for product analysis, but it will duplicate order-level fields such as total order value unless you aggregate carefully.

The exercises in this article use a fictional commerce database called Northstar Commerce. You can recreate it in PostgreSQL, DuckDB, SQLite, BigQuery, or another system, adjusting syntax where necessary. The model is intentionally small enough to understand and rich enough to support realistic analysis.

Write a data contract before a complex query

For each source table, record the expected key, row grain, timestamp meaning, timezone, valid statuses, null conventions, and update frequency. For the output, record the grain, date range, inclusion rules, and metric definitions. This short contract makes review easier because another person can compare the SQL with an explicit expectation instead of guessing what the author intended.

• Grain: one row per customer, order, product-day, session, or cohort-month.

• Population: which customers, markets, products, and time periods are included or excluded.

• Metric definition: for example, whether revenue includes taxes, refunds, shipping, or canceled orders.

• Time logic: event time versus ingestion time, timezone, cutoff boundary, and late-arriving data.

• Uniqueness: which columns should uniquely identify a row in both source and output.

• Null behavior: whether null means unknown, not applicable, not yet observed, or a genuine absence.

The Essential SQL Query Roadmap for Data Science

Learn the following patterns in sequence. Each one adds a new reasoning skill. Do not rush to advanced window functions while basic filters, joins, and grouping still feel uncertain. A complex query is usually a composition of simple operations whose intermediate results you understand.

1. Select only the columns and rows you need

Begin with explicit columns. SELECT * is useful for a quick inspection, but it is weak in reusable work because schemas change, unnecessary columns increase transfer, and duplicate column names become confusing after joins. Add a deterministic sort when you need a meaningful top or latest sample.

SELECT
    order_id,
    customer_id,
    order_ts,
    total_amount
FROM orders
WHERE status = 'completed'
  AND order_ts >= TIMESTAMP '2026-01-01 00:00:00'
ORDER BY order_ts DESC, order_id DESC
LIMIT 100;

This query answers a narrow question: show the 100 most recent completed orders in the analysis period. The secondary sort by order ID makes the output stable when several orders share the same timestamp. In portfolio work, explain why the selected status and date boundary match the business definition.

2. Filter carefully and make null handling explicit

SQL uses three-valued logic: a comparison can be true, false, or unknown. A null value is not equal to anything, including another null, so filters such as country <> 'US' will not include rows where country is null. Use IS NULL or IS NOT NULL deliberately. Use CASE to express business categories and COALESCE only when substituting a value is logically valid. PostgreSQL documents these patterns in its conditional expressions guide.

SELECT
    order_id,
    customer_id,
    COALESCE(total_amount, 0) AS reported_amount,
    CASE
        WHEN total_amount IS NULL THEN 'missing'
        WHEN total_amount < 50 THEN 'small'
        WHEN total_amount < 200 THEN 'medium'
        ELSE 'large'
    END AS order_value_band
FROM orders
WHERE status IN ('completed', 'refunded');

The category handles missing amounts before numerical comparisons. That does not mean replacing every missing amount with zero is analytically safe. The alias reported_amount signals that the substitution is a presentation choice. For financial analysis, you would normally investigate why a completed or refunded order has a missing amount before using it in totals.

3. Aggregate at the level of the question

Aggregation collapses rows into groups. Before grouping, say what one output row should represent. The next query produces one row per order month and acquisition channel. It counts distinct orders and customers because one customer can place several orders.

SELECT
    CAST(DATE_TRUNC('month', o.order_ts) AS date) AS order_month,
    c.acquisition_channel,
    COUNT(DISTINCT o.order_id) AS completed_orders,
    COUNT(DISTINCT o.customer_id) AS purchasing_customers,
    SUM(o.total_amount) AS revenue,
    AVG(o.total_amount) AS average_order_value
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY
    CAST(DATE_TRUNC('month', o.order_ts) AS date),
    c.acquisition_channel
ORDER BY order_month, c.acquisition_channel;

A common mistake is to divide revenue by row count and call it average order value after joining to an item-level table. The denominator would then be items, not orders. Aggregates are only meaningful when the input grain and denominator match the metric definition.

4. Use HAVING for group-level conditions

The WHERE clause filters source rows before grouping. HAVING filters groups after aggregation. Use it when the condition depends on an aggregate such as total revenue, customer count, or number of orders.

SELECT
    p.category,
    COUNT(DISTINCT o.order_id) AS orders,
    SUM(oi.quantity * oi.unit_price) AS item_revenue
FROM order_items AS oi
JOIN orders AS o
  ON o.order_id = oi.order_id
JOIN products AS p
  ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY p.category
HAVING COUNT(DISTINCT o.order_id) >= 50
ORDER BY item_revenue DESC;

The result excludes categories with fewer than 50 completed orders. This may reduce noise in a comparative report, but it also changes the population. A strong analysis states the threshold and explains why smaller groups were excluded.

5. Join tables without multiplying metrics

Joins are where SQL becomes powerful and dangerous. An inner join keeps matching rows from both sides. A left join keeps every row from the left side and adds matches from the right. The join type is not merely technical; it defines who remains in the analysis.

Suppose you need one row per completed order with revenue reconstructed from item lines. Aggregate the one-to-many table first, then join the order-level result. This prevents order-level columns from repeating once for every item.

WITH item_totals AS (
    SELECT
        order_id,
        SUM(quantity * unit_price) AS item_revenue,
        SUM(quantity) AS units
    FROM order_items
    GROUP BY order_id
)
SELECT
    o.order_id,
    o.customer_id,
    o.order_ts,
    it.units,
    it.item_revenue
FROM orders AS o
JOIN item_totals AS it
  ON it.order_id = o.order_id
WHERE o.status = 'completed';

Validate the join by comparing the number of unique order IDs before and after it. If the output promises one row per order, COUNT(*) and COUNT(DISTINCT order_id) should match. If they do not, the query has either duplicated orders or allowed duplicate keys into the aggregated subquery.

6. Find missing relationships with EXISTS and NOT EXISTS

Data science questions often involve absence: customers who have never purchased, products with no recent orders, or sessions without a conversion event. NOT EXISTS expresses this directly and avoids the surprising null behavior that can make NOT IN return no rows when its subquery contains a null.

SELECT
    c.customer_id,
    c.signup_date,
    c.acquisition_channel
FROM customers AS c
WHERE c.signup_date < DATE '2026-04-01'
  AND NOT EXISTS (
      SELECT 1
      FROM orders AS o
      WHERE o.customer_id = c.customer_id
        AND o.status = 'completed'
  );

This returns customers who signed up before April 2026 and have no completed order. It does not say why they failed to convert. That requires additional evidence from sessions, campaigns, product exposure, or qualitative research.

7. Use CTEs to expose intermediate logic

A common table expression, or CTE, gives a name to an intermediate result within one query. The main benefit is readability and testability. The PostgreSQL CTE documentation describes CTEs as auxiliary statements that can break a complicated query into simpler parts. They are not an automatic performance optimization, so use them because they clarify logic, then inspect the plan when performance matters.

WITH completed_orders AS (
    SELECT
        order_id,
        customer_id,
        order_ts,
        total_amount
    FROM orders
    WHERE status = 'completed'
),
customer_summary AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS lifetime_revenue,
        MAX(order_ts) AS last_order_ts
    FROM completed_orders
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.country,
    s.order_count,
    s.lifetime_revenue,
    s.last_order_ts
FROM customer_summary AS s
JOIN customers AS c
  ON c.customer_id = s.customer_id
ORDER BY s.lifetime_revenue DESC;

During development, run each CTE as a standalone query. Check its row count, uniqueness, date range, and null profile. This turns one intimidating statement into a sequence of smaller contracts.

8. Use window functions when rows must keep their identity

A normal aggregate reduces several rows to one row per group. A window function calculates across related rows while preserving one output row for each input row. That distinction makes windows ideal for rankings, running totals, prior values, moving averages, deduplication, and top-N-per-group questions. The PostgreSQL window-function tutorial explains how OVER, PARTITION BY, and window ordering define the related rows.

The following query finds the three highest-revenue products inside each category. Aggregation creates one row per product first. Ranking then compares products within their category.

WITH product_sales AS (
    SELECT
        p.category,
        p.product_id,
        p.product_name,
        SUM(oi.quantity oi.unit_price) AS revenue
    FROM order_items AS oi
    JOIN orders AS o
      ON o.order_id = oi.order_id
    JOIN products AS p
      ON p.product_id = oi.product_id
    WHERE o.status = 'completed'
    GROUP BY p.category, p.product_id, p.product_name
),
ranked_products AS (
    SELECT
        product_sales.,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS revenue_rank
    FROM product_sales
)
SELECT *
FROM ranked_products
WHERE revenue_rank <= 3
ORDER BY category, revenue_rank, product_id;

Use ROW_NUMBER when you need exactly one sequential position and can define a deterministic tie-breaker. Use RANK when ties should share rank and leave gaps. Use DENSE_RANK when ties should share rank without gaps. State the intended behavior instead of selecting a function by habit.

9. Compare time periods with LAG and safe division

Month-over-month change is a common analytical pattern. First create one row per month, then use LAG to bring the previous month onto the current row. Use NULLIF to avoid division by zero, and decide how missing months should be handled before calculating growth.

WITH monthly_revenue AS (
    SELECT
        CAST(DATE_TRUNC('month', order_ts) AS date) AS order_month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY CAST(DATE_TRUNC('month', order_ts) AS date)
),
with_previous AS (
    SELECT
        order_month,
        revenue,
        LAG(revenue) OVER (ORDER BY order_month) AS previous_revenue
    FROM monthly_revenue
)
SELECT
    order_month,
    revenue,
    previous_revenue,
    ROUND(
        100.0 * (revenue - previous_revenue)
        / NULLIF(previous_revenue, 0),
        1
    ) AS month_over_month_pct
FROM with_previous
ORDER BY order_month;

This query assumes every observed month should be compared with the preceding observed month. If a calendar month is missing entirely, the comparison may span two or more months. A production metric often needs a calendar table or date spine so missing periods appear explicitly with zero or null according to the business definition.

10. Deduplicate event data with a documented rule

Event pipelines can deliver the same logical event more than once. Do not hide duplicates with an unexplained DISTINCT. Define the business key and the record to keep. If event_uuid identifies the logical event and later ingestion contains corrected data, retain the latest ingested record.

WITH ranked_events AS (
    SELECT
        events.*,
        ROW_NUMBER() OVER (
            PARTITION BY event_uuid
            ORDER BY ingested_at DESC
        ) AS version_rank
    FROM events
)
SELECT
    event_uuid,
    customer_id,
    session_id,
    event_ts,
    event_name,
    experiment_group
FROM ranked_events
WHERE version_rank = 1;

Explain why the newest ingestion is trusted. Another system may keep the earliest record or quarantine duplicates instead.

11. Build a cohort retention table

Cohort analysis groups customers by a shared starting period and measures activity after that start. It combines date logic, distinct counting, CTEs, and a denominator that must remain fixed. The following PostgreSQL-style query assigns customers to signup-month cohorts and calculates the share active in each later order month.

WITH customer_activity AS (
    SELECT DISTINCT
        c.customer_id,
        CAST(DATE_TRUNC('month', c.signup_date) AS date) AS cohort_month,
        CAST(DATE_TRUNC('month', o.order_ts) AS date) AS activity_month
    FROM customers AS c
    JOIN orders AS o
      ON o.customer_id = c.customer_id
    WHERE o.status = 'completed'
),
cohort_activity AS (
    SELECT
        cohort_month,
        CAST(
            EXTRACT(YEAR FROM AGE(activity_month, cohort_month)) 12
            + EXTRACT(MONTH FROM AGE(activity_month, cohort_month))
            AS integer
        ) AS month_number,
        COUNT(DISTINCT customer_id) AS active_customers
    FROM customer_activity
    GROUP BY cohort_month, month_number
),
cohort_size AS (
    SELECT
        CAST(DATE_TRUNC('month', signup_date) AS date) AS cohort_month,
        COUNT() AS cohort_customers
    FROM customers
    GROUP BY CAST(DATE_TRUNC('month', signup_date) AS date)
)
SELECT
    a.cohort_month,
    a.month_number,
    a.active_customers,
    s.cohort_customers,
    ROUND(100.0 * a.active_customers / s.cohort_customers, 1)
        AS retention_pct
FROM cohort_activity AS a
JOIN cohort_size AS s
  ON s.cohort_month = a.cohort_month
ORDER BY a.cohort_month, a.month_number;

The query defines activity as at least one completed order in a month. That is one valid retention definition, not a universal one. Subscription products, marketplaces, media platforms, and business-to-business products often need different activity windows and eligibility rules. Make the definition visible in the project.

12. Create a leakage-aware feature table for machine learning

SQL often creates the table that a model will consume. The central discipline is time. Every feature must use only information available before the prediction cutoff. The next query creates one row per customer using completed orders before June 1, 2026. It includes lifetime and recent behavior while preserving customers who have no qualifying order.

WITH eligible_orders AS (
    SELECT
        order_id,
        customer_id,
        order_ts,
        total_amount
    FROM orders
    WHERE status = 'completed'
      AND order_ts < TIMESTAMP '2026-06-01 00:00:00'
)
SELECT
    c.customer_id,
    COUNT(DISTINCT o.order_id) AS orders_before_cutoff,
    COALESCE(SUM(o.total_amount), 0) AS revenue_before_cutoff,
    AVG(o.total_amount) AS average_order_value,
    MAX(o.order_ts) AS last_order_before_cutoff,
    SUM(
        CASE
            WHEN o.order_ts >= TIMESTAMP '2026-05-01 00:00:00'
            THEN 1 ELSE 0
        END
    ) AS orders_last_30_days
FROM customers AS c
LEFT JOIN eligible_orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

The label must be created in a later observation window, not inside the feature window. For example, a churn label might ask whether the customer places no order from June through August. Keep the cutoff, feature window, label window, and eligibility rules in configuration or documentation so the dataset can be reproduced for another date.

Validate Correctness Before Optimizing Performance

A query can be fast and wrong. Validate before tuning, especially after joins, deduplication, cohort calculations, and feature construction. Small checks are enough when they target the ways the result could fail.

Read the execution plan after the result is trustworthy

When a query becomes slow, first reduce unnecessary work: select fewer columns, filter early when semantics allow it, avoid repeated calculations, and confirm that join keys use compatible data types. Then inspect the plan. PostgreSQL's EXPLAIN documentation describes how the command shows scans, join strategies, estimated costs, and, with ANALYZE, actual execution statistics. Remember that EXPLAIN ANALYZE executes the statement.

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    customer_id,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
  AND order_ts >= TIMESTAMP '2026-01-01 00:00:00'
GROUP BY customer_id;

Do not add indexes or rewrite queries from intuition alone. Measure the actual workload, data size, filter selectivity, and execution plan. For a more specialized follow-up, Refonte Learning's guide to query optimization with AI-powered assistants discusses how automated suggestions should be benchmarked and reviewed rather than trusted blindly.

Choose a practical environment for exercises

PostgreSQL is a strong choice when you want a full relational database and production-like behavior. SQLite is convenient for a small local file. DuckDB is useful for analytical practice because it can query local data files, including Parquet, directly; its official guide explains reading and querying Parquet files. Cloud warehouses are valuable later when you need platform-specific skills such as partitioning, clustered storage, cost controls, or semi-structured data. Begin with one environment and learn the relational ideas well before collecting dialects.

Five Progressive SQL Portfolio Exercises

A strong SQL portfolio does not need a private company database. It needs a coherent question, realistic table relationships, readable logic, and evidence that you tested the result. Use public, synthetic, or carefully anonymized data. Keep raw data separate from transformed outputs, and include a small data dictionary so reviewers can understand the schema without opening every file.

Exercise 1: Revenue and margin diagnostic

Start with completed orders, order items, products, and customers. Reconstruct item revenue, estimate gross margin as revenue minus quantity multiplied by unit cost, and summarize results by month, product category, and acquisition channel. Include order count, purchasing customers, units, revenue, margin, average order value, and margin rate. Then test whether totals reconcile with a simpler order-level query.

Portfolio value comes from interpretation. Identify one area of concentration, one declining segment, and one caveat. For example, a category may appear profitable because refunds are excluded or because acquisition cost is unavailable. State what the query can support and what additional data would be needed before making a commercial recommendation.

Exercise 2: Customer cohort retention

Assign customers to a signup-month cohort and calculate the percentage with at least one completed order in month zero, month one, month two, and later periods. Compare cohorts by acquisition channel only after checking that group sizes are large enough to interpret. Produce both a long table for analysis and a pivot-ready output for a heatmap.

Document the denominator carefully. Does the cohort include everyone who signed up, only verified accounts, or only customers eligible to purchase? Is month zero considered activation rather than retention? Do late orders update historical cohorts? These decisions matter more than the color scale in the final visualization.

Exercise 3: Product funnel analysis

Use event data to create one row per session with flags for product view, add to cart, checkout start, and purchase. Deduplicate events first. Decide whether the funnel is strictly ordered or whether a session can count at a later stage without an earlier event. Calculate stage-to-stage conversion and overall conversion, then segment by device, acquisition channel, or experiment group.

Check for sessions with multiple customers, purchases without checkout events, late events, and sudden tracking-volume changes. Funnel analysis depends on instrumentation quality, not only conditional sums.

Exercise 4: Experiment analysis data mart

Create an analysis table with one row per randomized unit, usually a user or session. Include the assigned group, first exposure time, eligibility status, conversion outcome, order value, and guardrail measures such as refunds or support contacts. Exclude post-exposure features from baseline columns. Check for users assigned to multiple groups and for outcome events that occurred before exposure.

Stop at a trustworthy analytical dataset and descriptive checks; statistical inference can happen in Python. The project should show how database logic affects experimental validity.

Exercise 5: Churn feature table

Choose a cutoff date and generate one row per eligible customer. Build recency, frequency, and monetary features, plus category diversity, refund rate, recent session count, and change from an earlier window. Build the label from a future period in a separate query. Repeat the process for at least two cutoff dates to show that the pipeline is reusable rather than hard-coded to one snapshot.

Include a feature dictionary with each source, calculation, time window, null policy, and expected range. A reviewer should be able to tell what was known at prediction time.

Turn SQL Exercises Into Credible Portfolio Evidence

A folder full of query files is not yet a portfolio. A reviewer needs context, reproducibility, and a clear path from question to result. Refonte Learning's guide on how to build a data science portfolio that gets you hired emphasizes complete, well-explained projects rather than a large quantity of disconnected exercises. Apply that principle directly to SQL.

Recommended repository structure

sql-portfolio-project/
|-- README.md
|-- data_dictionary.md
|-- sql/
|   |-- 01_source_checks.sql
|   |-- 02_cleaning.sql
|   |-- 03_analysis.sql
|   |-- 04_validation.sql
|-- outputs/
|   |-- monthly_metrics.csv
|   |-- cohort_retention.csv
|-- notebooks/
|   |-- visualization_and_interpretation.ipynb
|-- docs/
    |-- metric_definitions.md
    |-- limitations.md

Keep SQL readable with meaningful CTE names, consistent alignment, and comments that explain unusual logic. Publish only small, non-sensitive outputs. Never expose credentials, private connection strings, proprietary schemas, or identifiable customer data.

Explain your reasoning in the README

1.     State the business or research question and why it matters.

2.     Describe the tables, grain, keys, date range, and important exclusions.

3.     Define each metric in words before showing the final query.

4.     Explain the hardest SQL decision, such as avoiding a one-to-many join error or fixing a cohort denominator.

5.     List validation checks and whether they passed.

6.     Summarize the result, limitation, and next action in language a nontechnical reader can understand.

A Four-Week SQL Learning Plan

This plan assumes five to seven focused hours per week. Stretch it if databases are completely new to you. The sequence is more important than the calendar. For a broader learning path that also includes Python, statistics, projects, and job-readiness milestones, use the data science roadmap from beginner to job-ready as the surrounding plan.

Predict the result before running the query. When it fails, reduce it to the smallest working piece. When it succeeds, test an edge case such as a null key, tied rank, zero denominator, or duplicate event. This is how syntax becomes judgment.

Common SQL Mistakes Data Science Beginners Should Avoid

Using DISTINCT to hide a join problem

DISTINCT can be correct when the question genuinely asks for unique combinations. It is not a repair tool for a query whose joins created unexpected duplicates. Find the source of the multiplicity, define the intended grain, and aggregate or deduplicate according to a documented rule.

Turning a LEFT JOIN into an INNER JOIN accidentally

A condition on the right-hand table in the WHERE clause removes rows where that table did not match, because its columns are null. When the goal is to keep all left-side entities while restricting the matched rows, place the condition in the ON clause or pre-filter the right table in a CTE.

Counting rows when the metric needs entities

After joining orders to items, COUNT(*) counts item rows. It does not count orders. Use COUNT(DISTINCT order_id) when that matches the question, or aggregate items to the order grain first. Distinct counting can be expensive at scale, but correctness comes before optimization.

Using NOT IN without considering nulls

If the subquery used by NOT IN returns a null, the comparison can become unknown for every candidate row. Prefer NOT EXISTS for anti-join logic unless you have deliberately excluded nulls and understand the semantics.

Ignoring integer division and zero denominators

Some systems divide integers as integers. Multiply by 1.0 or cast to a decimal type when calculating a rate. Use NULLIF(denominator, 0) to prevent division-by-zero errors, then decide whether the result should remain null or receive a documented fallback.

Assuming timestamps mean the same thing

Event time, transaction time, ingestion time, and update time answer different questions. Timezones and daylight-saving changes can shift daily boundaries. Store and document the timezone, use half-open intervals such as start-inclusive and end-exclusive, and test records around the cutoff.

Forgetting the default window frame

Adding ORDER BY inside a window changes the frame used by many aggregate windows. This can produce a running value when you expected the full partition. Specify the frame explicitly when the default is not the intended behavior, especially for moving calculations and functions such as LAST_VALUE.

Building model features after the outcome

A feature table can be perfectly valid SQL and still be invalid machine learning. If a field was created after the event you are predicting, it leaks the answer. Use explicit cutoffs, separate feature and label windows, and verify the maximum timestamp used by every feature.

Optimizing before defining correctness

A shorter or faster query is not better if nobody can verify it. Start with readable logic and checks. Measure performance on realistic data, inspect the execution plan, and then improve the expensive parts without changing the result.

Accepting AI-generated SQL without review

Generative tools can draft queries and explain errors, but they do not know your real grain, metric definition, privacy obligations, or data quality history unless you provide that context. They may invent columns, choose the wrong join, or produce plausible calculations with a false denominator. Treat generated SQL as a draft. Test it against known cases and never paste sensitive schemas or data into an unapproved service.

How Refonte Learning Fits Into This SQL Roadmap

As of July 2026, the live page for Refonte Learning's Data Science & AI program lists Python data science, statistical modelling, exploratory data analysis and visualization, machine learning, predictive modelling, deep learning, generative AI, and application to industry projects among its competencies. Those areas depend on reliable data preparation, and SQL is a natural companion skill for learners who expect to work with relational business data.

However, SQL is not named in the publicly accessible competency or FAQ tool lists on that course page. Prospective learners should request the current syllabus and ask whether SQL, relational modeling, joins, analytical window functions, and database-based projects are included. If they are not, use the exercises in this guide as a parallel practice track rather than assuming the broader program covers them.

A structured program is most valuable when it sequences concepts, provides feedback, and requires practical output. Self-study can also work when you build projects, document assumptions, and seek review. In either route, the target is not a collection of solved syntax drills. It is the ability to create trustworthy analytical datasets and explain how you know they are trustworthy.

Frequently Asked Questions

How much SQL should a data scientist know?

At minimum, be comfortable with filtering, grouping, joins, null handling, CASE, CTEs, subqueries, dates, and window functions. You should also know how to validate row grain and reconcile totals. Deeper optimization, schema design, transactions, and database administration become more important in data engineering or production-heavy roles.

Which SQL dialect should a beginner learn?

PostgreSQL is a practical starting point because it supports a broad analytical feature set and has strong documentation. DuckDB or SQLite can reduce setup for local practice. BigQuery, Snowflake, Redshift, SQL Server, and MySQL become relevant when a target role or employer uses them. Learn core relational reasoning first, then study dialect differences in dates, casts, arrays, semi-structured data, and filtering of window results.

Should I learn SQL before pandas?

Learn them together. SQL teaches set-based thinking and how related tables are assembled. pandas helps you continue analysis in Python. A useful exercise is to solve a small aggregation or join in both tools, compare the outputs, and explain which environment is more appropriate for the data size and workflow.

Do data scientists need database design?

You do not need to become a database administrator to begin, but you should understand primary keys, foreign keys, one-to-many relationships, normalization at a basic level, fact and dimension tables, and why indexes or partitions can affect access. These concepts help you interpret schemas and avoid incorrect joins.

Are window functions essential for data science?

Yes at a working level. Rankings, running totals, previous values, moving metrics, deduplication, and top-N-per-group questions appear frequently in analytics and feature engineering. Learn the difference between grouping and windowing, then practice ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM over a window, and explicit frames.

How can I practice SQL without access to a company database?

Use public or synthetic datasets and load them into a local database. An e-commerce, subscription, transport, public-health, or media schema can support realistic questions about cohorts, funnels, trends, and quality. The key is to create several related tables rather than working only with one polished CSV.

What should a SQL portfolio project include?

Include a business question, data dictionary, schema or relationship diagram, ordered query files, validation checks, small outputs, and a README that explains assumptions and limitations. One complete project with a clear analytical story is usually more persuasive than dozens of isolated interview solutions.

Can generative AI write my SQL portfolio project?

It can help draft syntax, suggest edge cases, or explain an execution plan, but you must own the logic. Review every table and column, verify joins, test expected counts, inspect time boundaries, and explain the result without relying on the generated response. A portfolio is evidence of your reasoning, not evidence that a tool can produce code.

How long does it take to learn enough SQL for a first project?

A focused beginner can learn enough for a basic multi-table analysis in several weeks. Reliable analytical SQL takes longer because it depends on repeated exposure to messy schemas, ambiguous metrics, and edge cases. Use the four-week plan as a first cycle, then deepen the same skills through projects rather than restarting with another syntax course.

Conclusion

SQL for data science is the skill of turning stored records into a defensible analytical dataset. The essential path is straightforward: learn to retrieve and filter, summarize at the right grain, join without multiplying metrics, handle absence and nulls, structure logic with CTEs, analyze sequences with window functions, work carefully with time, and validate every important result.

Portfolio work should prove the same habits. Build a revenue diagnostic, cohort table, funnel, experiment data mart, or leakage-aware feature table. Include metric definitions, tests, and limitations. A reviewer should be able to understand not only what the query returns, but why the result deserves trust.

Whether you study independently or through Refonte Learning's training and internship platform, keep the target practical. SQL fluency is not the ability to recall the most keywords. It is the ability to ask a precise question, preserve the meaning of the data, and produce an answer that another person can reproduce and challenge.