Data Science and Machine Learning 101: Programming and Data Engineering SQL for Analytics Joins Window Functions Aggregations Subqueries

What This Is

SQL for analytics is the toolbox that lets you pull, reshape, and summarize data stored in relational databases so you can feed clean, feature‑rich tables into your machine‑learning pipelines. Whether you’re building a churn‑prediction model for a telecom provider or creating the input matrix for a recommendation engine, you first need to join the right tables, aggregate metrics, and compute window‑based features (e.g., “average spend over the past 30 days”) directly in SQL—saving time, memory, and downstream preprocessing.

Key Terms & Formulas

• JOIN – Combines rows from two tables based on a logical relationship.
• INNER JOIN: keep only matching rows.
• LEFT/RIGHT OUTER JOIN: keep all rows from one side, filling missing values with NULL.
• ON clause – Predicate that defines the join condition, e.g., ON a.customer_id = b.customer_id.
• Window Function – Computes a value across a set of rows that are related to the current row without collapsing the result set.
• General syntax: FUNC() OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN X PRECEDING AND Y FOLLOWING).
• ROW_NUMBER() – Assigns a unique sequential integer to rows within a partition; useful for deduplication.
• LAG / LEAD – Accesses a previous/next row’s column value; e.g., LAG(revenue, 1) OVER (PARTITION BY cust_id ORDER BY date).
• Aggregation – Summarizes groups of rows into a single value.
• SUM(col), AVG(col), COUNT(*), MAX(col), MIN(col).
• GROUP BY – Groups rows so that aggregate functions are applied per group.
• Formula: SELECT key, AGG(col) FROM tbl GROUP BY key.
• HAVING – Filters groups after aggregation (similar to WHERE but works on aggregated columns).
• Subquery – A query nested inside another query; can appear in SELECT, FROM, WHERE, or HAVING.
• Correlated subquery: references columns from the outer query, evaluated per outer row.
• CTE (Common Table Expression) – Temporary named result set defined with WITH; improves readability for multi‑step logic.
• DISTINCT – Removes duplicate rows from the result set.
• UNION / UNION ALL – Stacks result sets vertically; UNION removes duplicates, UNION ALL keeps them.

Step‑by‑Step / Process Flow

1. Identify the analytical grain – Decide the level of observation you need (e.g., “customer‑day”, “product‑month”).
2. Pull raw tables – Write a base query that selects the primary fact table and any dimension tables via appropriate JOINs.
   python
sql = """
SELECT c.customer_id, c.signup_date, t.transaction_id, t.amount, t.date
FROM customers c
LEFT JOIN transactions t ON c.customer_id = t.customer_id
"""
3. Create window‑derived features – Use LAG, AVG OVER, or ROW_NUMBER to add temporal or ranking features.
   sql
SELECT *,
AVG(amount) OVER (PARTITION BY customer_id ORDER BY date
ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS amt_30d_avg,
LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY date) AS amt_prev_day
FROM base
4. Aggregate to the target grain – Group by the key you decided in step 1 and compute final metrics.
   sql
SELECT customer_id,
COUNT(*) AS txn_cnt,
SUM(amount) AS total_spent,
MAX(amount) AS max_txn,
AVG(amount) AS avg_txn
FROM enriched
GROUP BY customer_id
5. Filter / clean – Apply WHERE (pre‑aggregation) and HAVING (post‑aggregation) to drop outliers, e.g., HAVING total_spent > 0.
6. Export – Pull the result into pandas for modeling:
   python
df = pd.read_sql(final_sql, con=engine) # engine = SQLAlchemy connection

Common Mistakes

• Mistake: Using INNER JOIN when you need all customers, causing churn‑label rows to disappear.
  Correction: Switch to LEFT JOIN on the fact table so every customer appears, and fill missing metrics with 0 (e.g., COALESCE(SUM(amount),0)).

• Mistake: Applying WHERE after a window function, unintentionally discarding rows needed for the window calculation.
  Correction: Filter before the window or wrap the windowed query in a CTE/subquery, then apply WHERE on the outer layer.

• Mistake: Forgetting to GROUP BY all non‑aggregated columns, leading to SQL errors or unintended Cartesian products.
  Correction: List every column that isn’t an aggregate in the GROUP BY clause, or move those columns into a subquery/CTE.

• Mistake: Using UNION when you actually need UNION ALL, causing duplicate rows to be removed silently.
  Correction: Choose UNION ALL for stacking logs (e.g., daily transaction tables) unless you truly need deduplication.

• Mistake: Relying on DISTINCT to “dedupe” after a JOIN, which masks underlying data‑quality problems.
  Correction: Diagnose why duplicates appear (e.g., many‑to‑many joins) and resolve the join logic rather than masking it.

Data Science Interview / Practical Insights

1. Explain the difference between a correlated subquery and a regular subquery.

2. Correlated subqueries reference outer‑query columns and are re‑evaluated per row; regular subqueries are computed once.

3. When would you prefer a window function over a GROUP BY aggregation?

4. When you need row‑level results and aggregated context (e.g., “running total” or “rolling average”) without collapsing rows.

5. How do you avoid “double counting” when joining a fact table to multiple dimension tables?

6. Use a “star schema” pattern: join each dimension separately in subqueries/CTEs, or aggregate the fact table first before joining.

7. What is the performance impact of LEFT JOIN vs. INNER JOIN and how can you mitigate it?

8. LEFT JOIN forces the engine to keep all rows from the left side, often preventing early filter push‑down. Mitigate by filtering the left table first, adding appropriate indexes, and materializing intermediate results with CTEs.

Quick Check Questions

1. Scenario: You need the 7‑day rolling average spend per customer, but you also need to keep rows where a customer had no transactions in the last 7 days.
   Answer: Use a LEFT JOIN to the transactions table and compute AVG(amount) OVER (PARTITION BY customer_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), then COALESCE the result to 0.

2. Scenario: After joining orders to order_items you notice each order appears three times. What likely went wrong?
   Answer: You performed a many‑to‑many join (order ↔ items) without aggregating; fix by aggregating order_items first (e.g., SUM(quantity) GROUP BY order_id) then join.

3. Scenario: A model built on aggregated customer features shows a sudden drop in performance after adding a new data source.
   Answer: The new source probably introduced duplicate rows via an INNER JOIN; verify join keys and use DISTINCT or proper aggregation to eliminate duplicates.

Last‑Minute Cram Sheet (10 one‑liners)

1. INNER vs. LEFT JOIN: INNER keeps only matching rows; LEFT keeps all rows from the left table (fill missing with NULL).
2. Window vs. GROUP BY: Window functions keep row granularity; GROUP BY collapses rows.
3. ROW_NUMBER() + CTE → quick de‑duplication: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY key ORDER BY dt DESC) rn FROM tbl) SELECT * FROM ranked WHERE rn = 1.
4. LAG(col, n) OVER (PARTITION BY … ORDER BY …) → fetch the value n rows back (useful for “previous day” features).
5. HAVING filters after aggregation; WHERE filters before.
6. UNION ALL = stack without deduplication; UNION = stack + deduplication (costly).
7. COALESCE(expr, 0) → replace NULL (common after outer joins) with a neutral value for ML features.
8. CTE (WITH) improves readability and lets you reuse a sub‑query result multiple times in the same statement.
9. Index on join keys (e.g., customer_id) dramatically speeds up large joins; always check execution plan (EXPLAIN).
10. ⚠️ Never use SELECT * in production pipelines – explicit column lists avoid hidden schema changes and reduce data transfer.