SQL for Data Science: Queries, Joins & Analysis

Start with the table grain before writing analytical SQL

Suppose an ecommerce team stores customers, orders and products in separate tables. SQL can build one clean customer-level dataset containing total spend, last order date and order frequency before Python or a BI tool is used.

• SQL lets analysts filter, aggregate and join data close to where it is stored, reducing unnecessary movement into Python.
• A strong SQL workflow starts by confirming table grain and key uniqueness before writing joins.
• CTEs and window functions often make analytical queries easier to audit than deeply nested subqueries.
• Query correctness matters more than clever syntax: validate row counts and totals against a simpler baseline.

Work through a query with row-count checks

For analytical SQL, predict one visible result before each snippet runs—a row count, shape, statistic, ranking or error pattern—then compare it with the printed checkpoint.

-- analytical SQL · example 1
SELECT customer_id,
       COUNT(*) AS orders,
       SUM(order_total) AS revenue,
       MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC;

analytical SQL checkpoint 1: Run the cell and inspect the printed values before changing anything.

-- analytical SQL · example 2
SELECT COUNT(*) AS rows, COUNT(DISTINCT customer_id) AS customers
FROM practice_orders;

analytical SQL checkpoint 2: rows | customers
30   | 12

-- analytical SQL · example 3
SELECT customer_id, COUNT(*) AS n
FROM practice_orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n DESC;

analytical SQL checkpoint 3: Customers with more than one row are listed before any join or aggregation.

-- analytical SQL · example 4
SELECT region, COUNT(*) AS orders, ROUND(SUM(amount),2) AS revenue
FROM practice_orders
GROUP BY region
ORDER BY revenue DESC;

analytical SQL checkpoint 4: region | orders | revenue
West   | ...    | ...

-- analytical SQL · example 5
WITH x AS (
  SELECT amount FROM practice_orders WHERE amount IS NOT NULL
)
SELECT ROUND(SUM(amount),2) AS detail_total,
       ROUND((SELECT SUM(amount) FROM x),2) AS check_total
FROM x;

analytical SQL checkpoint 5: detail_total and check_total should match exactly.

Test duplicates, NULLs and unmatched keys

Self-check with answers

Practise with a reproducible SQLite-style dataset