Advanced SQL for Data Scientists – Window Functions, Query Optimisation and Database Design

SQL is the single most universal skill in data science — used by analysts, data engineers, ML engineers, and data scientists alike. While basic SELECT, JOIN, and GROUP BY are table stakes, advanced SQL skills — window functions, query optimisation, recursive CTEs, and database design — separate candidates who pass SQL rounds from those who excel. This guide covers the advanced SQL concepts that appear most frequently in data science interviews and day-to-day production work.

This guide builds directly on our SQL Interview Questions and Answers which covers the foundational 50 questions. For data engineering context (how SQL fits into ETL pipelines and data warehouses), see our Data Engineering Interview Q&A and ETL Pipelines with Airflow and dbt guides. Python data manipulation equivalents are covered in our Python Interview Q&A.

Window Functions — The Most Powerful SQL Feature for Data Scientists

Window functions perform calculations across a set of table rows related to the current row — without collapsing the rows into groups like GROUP BY. They are the single highest-leverage SQL feature for analytical work: ranking, running totals, moving averages, lag/lead comparisons, and percentile calculations all use window functions.

Anatomy of a window function:

function_name(expression)
    OVER (
        [PARTITION BY column1, column2]   -- divide into groups (optional)
        [ORDER BY column3 ASC/DESC]        -- order within each partition
        [ROWS/RANGE BETWEEN ... AND ...]   -- frame specification (optional)
    )

Window frame specification: By default (when ORDER BY is present without a frame clause), the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — all rows from the partition start up to the current row. Common frames:

Ranking functions — the most common in interviews:

SELECT
    user_id,
    revenue,
    -- ROW_NUMBER: unique rank, no ties (arbitrary tie-breaking)
    ROW_NUMBER()  OVER (ORDER BY revenue DESC) AS row_num,
    -- RANK: tied rows get same rank, next rank skips (1,2,2,4)
    RANK()        OVER (ORDER BY revenue DESC) AS rank_num,
    -- DENSE_RANK: tied rows same rank, no skipping (1,2,2,3)
    DENSE_RANK()  OVER (ORDER BY revenue DESC) AS dense_rank,
    -- NTILE: divide into N buckets (quartiles, deciles)
    NTILE(4)      OVER (ORDER BY revenue DESC) AS quartile,
    -- PERCENT_RANK: relative rank 0-1
    PERCENT_RANK()OVER (ORDER BY revenue DESC) AS pct_rank
FROM orders;

Running totals and moving averages:

SELECT
    order_date,
    daily_revenue,
    -- Cumulative revenue
    SUM(daily_revenue)
        OVER (ORDER BY order_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS cumulative_revenue,
    -- 7-day rolling average
    AVG(daily_revenue)
        OVER (ORDER BY order_date
              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        AS rolling_7d_avg,
    -- Month-to-date (partition by month)
    SUM(daily_revenue)
        OVER (PARTITION BY DATE_TRUNC('month', order_date)
              ORDER BY order_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS mtd_revenue
FROM daily_sales;

LAG and LEAD — comparing rows to adjacent rows:

SELECT
    user_id,
    event_date,
    revenue,
    -- Revenue from previous month
    LAG(revenue, 1, 0) OVER (PARTITION BY user_id ORDER BY event_date)
        AS prev_month_revenue,
    -- Month-over-month change
    revenue - LAG(revenue, 1, 0) OVER (PARTITION BY user_id ORDER BY event_date)
        AS mom_change,
    -- Days until next purchase (LEAD)
    LEAD(event_date) OVER (PARTITION BY user_id ORDER BY event_date) - event_date
        AS days_to_next_purchase
FROM user_revenue;

CTEs — Common Table Expressions

CTEs (WITH clauses) allow you to define named subqueries that can be referenced multiple times in the main query. They are the primary tool for writing readable, maintainable SQL — breaking a complex query into logical steps rather than nesting subqueries five levels deep.

-- Multi-step analysis using CTEs
WITH
-- Step 1: cohort definition
user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
-- Step 2: monthly activity per user
user_monthly AS (
    SELECT
        o.user_id,
        DATE_TRUNC('month', o.order_date)          AS activity_month,
        uc.cohort_month,
        DATEDIFF('month', uc.cohort_month,
                 DATE_TRUNC('month', o.order_date)) AS months_since_cohort
    FROM orders o
    JOIN user_cohorts uc USING (user_id)
),
-- Step 3: cohort retention matrix
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM user_cohorts
    GROUP BY cohort_month
)
-- Final: retention rate by cohort and period
SELECT
    um.cohort_month,
    um.months_since_cohort,
    COUNT(DISTINCT um.user_id)                     AS retained_users,
    cs.cohort_size,
    ROUND(COUNT(DISTINCT um.user_id) * 100.0
          / cs.cohort_size, 1)                     AS retention_rate_pct
FROM user_monthly um
JOIN cohort_sizes cs USING (cohort_month)
GROUP BY um.cohort_month, um.months_since_cohort, cs.cohort_size
ORDER BY um.cohort_month, um.months_since_cohort;

Recursive CTEs — for hierarchical data: Recursive CTEs query hierarchical structures like org charts, category trees, or bill-of-materials — where a record references another record in the same table.

-- Employee hierarchy traversal
WITH RECURSIVE org_tree AS (
    -- Anchor: top-level managers (no manager)
    SELECT employee_id, name, manager_id, 0 AS level,
           name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: employees whose manager is in the previous result
    SELECT e.employee_id, e.name, e.manager_id,
           ot.level + 1,
           ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY path;

Query Optimisation — Why Queries Are Slow and How to Fix Them

Understanding query optimisation is one of the most differentiating SQL skills in interviews and production work. The first tool is always EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL/BigQuery) — read the query plan before trying to optimise.

The most impactful optimisation techniques ranked by impact:

Index pitfalls — why indexes are not always used: An index on created_at will NOT be used by WHERE YEAR(created_at) = 2026 because the function wraps the indexed column. Rewrite as WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'. Similarly, WHERE LOWER(email) = 'user@example.com' will not use an index on email — use a functional index or store emails pre-lowercased. WHERE status != 'active' typically causes a full table scan even with an index on status — the optimiser estimates it is cheaper to scan than use the index for a large fraction of rows.

Database Design for Analytics

For data science contexts, the two key design patterns are the star schema (used in most data warehouses) and the wide table (used in modern lakehouse analytics). Our Data Engineering Interview Q&A covers star schema, snowflake schema, and slowly changing dimensions (SCD) in depth. Key principles for analytics-oriented design:

Denormalise for read performance: Analytics workloads are read-heavy. Pre-joining dimension tables into the fact table (star schema) eliminates expensive runtime joins. A 10TB fact table joined to a 1GB dimension at query time is much slower than a 10TB denormalised fact table queried directly.

Choose the right data types: Using VARCHAR(255) everywhere is an anti-pattern. Correct types save storage and improve performance: use INT instead of BIGINT when counts are < 2 billion; use DATE instead of TIMESTAMP when time is not needed; use BOOLEAN instead of TINYINT(1) for flags; use ENUM/category type for low-cardinality string columns in analytical databases.

Partitioning strategy: Partition on the column most commonly used in WHERE clauses — typically a date. In Snowflake: CLUSTER BY (event_date). In BigQuery: PARTITION BY event_date. In Redshift: SORTKEY and DISTKEY. Partition too granularly (by hour for a low-volume table) and you suffer small-file overhead; partition too coarsely (by year for a high-volume table) and you scan too much. The right partition granularity depends on query patterns and data volume.

20 Classic SQL Interview Problems

1. Find the second highest salary: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) — or more elegantly with DENSE_RANK: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) WHERE dr = 2. Our SQL Interview Q&A covers ranking-based problems extensively.

2. Retention rate calculation: For each user cohort (grouped by first order month), what percentage returned in month 1, 2, 3? Use the cohort CTE pattern shown above — this is the most common analytics SQL question at product companies.

3. Consecutive login streak: Find users with 3+ consecutive daily logins. Classic gap-and-island problem: subtract ROW_NUMBER from the date, creating an island identifier. Days in the same consecutive streak get the same island value.

4. Duplicate detection: SELECT *, COUNT(*) OVER (PARTITION BY email) AS cnt FROM users HAVING cnt > 1 — using window functions allows seeing all columns of duplicates, not just the count.

5. Running total by category: SUM(amount) OVER (PARTITION BY category ORDER BY date) — cumulative sum that resets for each category.

6-20 (common patterns): Median calculation (PERCENTILE_CONT in modern SQL), first/last value in a group (FIRST_VALUE/LAST_VALUE window functions), finding gaps in sequential IDs (LAG to detect jumps), pivoting rows to columns (CASE WHEN SUM pattern), unpivoting columns to rows (UNION ALL), self-join for finding pairs meeting conditions, recursive hierarchy traversal (org chart depth), top-N per group (ROW_NUMBER in CTE), rolling 30-day active users (COUNT DISTINCT with date range), year-over-year comparison (LAG with period=12 for monthly data).

SQL is tested in every data science interview loop. Beyond our SQL Interview Q&A (50 questions), the best preparation is practising on real datasets in BigQuery or PostgreSQL — write the cohort retention query, the gap-and-island query, and the top-N-per-group query from memory until they feel natural. These three patterns cover 80% of advanced SQL interview scenarios. For the Python equivalents of these SQL operations, see our Python Interview Q&A which covers pandas groupby, merge, and window operations.