SQL Interview Questions for Data Analyst (2026 Guide)

In technical hiring rounds for data analysts, SQL screening filters out over 65% of applicants before they ever speak with a hiring manager. Engineering leads and analytics directors look beyond basic SELECT * FROM table queries. They want to verify that you understand data grain, edge cases, metric reconciliation, and execution efficiency on real production schemas.

Whether you are preparing for product tech companies, financial institutions, or high-growth startups, this practical guide details the core problem archetypes tested in live coding screens. You can review current salary benchmarks in our Data Analyst Salary Guide 2026 or follow the complete curriculum in our 12-Week Data Analyst Career Track.

The 5 Core SQL Archetypes Tested in Analyst Interviews

Technical interviewers structure SQL assessments around 5 recurring patterns that reflect daily data pipeline responsibilities:

1. Relational Grain & Multi-Table JOINs: Combining transactional ledgers with dimensional lookups while preventing accidental record duplication.
2. Window Calculations: Evaluating ranking (ROW_NUMBER, DENSE_RANK), offsets (LAG, LEAD), and cumulative aggregates (SUM() OVER) without collapsing row detail.
3. Grouped Aggregations & Conditional Logic: Filtering aggregated buckets using HAVING alongside conditional metrics with CASE WHEN.
4. Cohort Analysis & Churn Modeling: Calculating month-over-month growth, rolling 7-day averages, and multi-touch user retention via Common Table Expressions (WITH).
5. Three-Valued Boolean Logic & NULL Traps: Guarding against unexpected row drops during NOT IN subqueries and join comparisons.

Let us dissect each archetype with concrete tables, executable queries, and common pitfalls.

Scenario 1: Multi-Table JOINs & The Metric Fan-Out Trap

Interview Prompt

You have two tables: orders containing 1,200 unique customer transactions and order_items containing 3,400 line-item product details. Write a query to compute the total revenue and the total discount amount applied per customer.

The Common Pitfall: Metric Inflation

Many candidates immediately join both tables on customer_id and compute SUM(o.discount_amount) alongside SUM(oi.item_price * oi.quantity). Because one order contains an average of 2.8 line items, joining on customer_id causes order-level records to duplicate. The discount total inflates by 280%, corrupting financial ledgers. For deeper coverage on this phenomenon, explore our detailed SQL JOIN Fan-Out Guide.

Schema Context

Table: orders

Table: order_items

Production Solution: Pre-Aggregation in a CTE

To avoid fan-out multiplication, aggregate the line items to the order grain before joining to the orders table:

WITH order_item_totals AS (
    SELECT order_id,
           SUM(quantity * unit_price) AS gross_item_revenue
    FROM order_items
    GROUP BY order_id
),
customer_order_summary AS (
    SELECT o.customer_id,
           SUM(o.discount_amount)            AS total_discounts,
           SUM(oit.gross_item_revenue)       AS total_gross_revenue
    FROM orders o
    LEFT JOIN order_item_totals oit
           ON o.order_id = oit.order_id
    GROUP BY o.customer_id
)
SELECT customer_id,
       ROUND(total_gross_revenue, 2) AS gross_revenue,
       ROUND(total_discounts, 2)     AS total_discounts,
       ROUND(total_gross_revenue - total_discounts, 2) AS net_revenue
FROM customer_order_summary
ORDER BY customer_id;

Expected Output

Scenario 2: Ranking & Deduplication with Window Functions

Interview Prompt

Given an employees table with employee IDs, department names, and annual salaries, retrieve the employees who earn the second-highest salary in each department. If two employees share the second-highest salary, include both.

Schema Context

Table: employees

Choosing the Correct Ranking Function

In ranking questions, candidate failure usually stems from confusing ROW_NUMBER(), RANK(), and DENSE_RANK():

• ROW_NUMBER() assigns sequential numbers (1, 2, 3, 4), which would arbitrarily pick between Aarav and Bhavna at rank 1 and push Chirag to rank 3.
• RANK() assigns tied rows the same number but skips subsequent ranks (1, 1, 3). In Analytics, Chirag would receive rank 3, meaning a WHERE rank = 2 filter would return zero rows.
• DENSE_RANK() assigns tied rows the same number without skipping (1, 1, 2). Chirag receives rank 2, correctly reflecting the second distinct salary tier. Learn more about these nuances in our guide on ROW_NUMBER vs RANK vs DENSE_RANK in SQL.

Production Solution

WITH ranked_salaries AS (
    SELECT employee_id,
           employee_name,
           department,
           salary,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS salary_rank
    FROM employees
)
SELECT employee_id,
       employee_name,
       department,
       salary
FROM ranked_salaries
WHERE salary_rank = 2
ORDER BY department, employee_name;

Expected Output

Notice that Engineering returned both Fatima and Gopal because both earn ₹28,00,000, which represents the second-highest salary tier.

Scenario 3: Time-Series Deltas & Month-over-Month Growth

Interview Prompt

You have a table of monthly sales figures. Write a query to compute the month-over-month (MoM) revenue growth percentage for each month in 2025.

Schema Context

Table: monthly_revenue

Production Solution Using LAG()

The LAG() window function allows you to inspect previous rows within an ordered partition without performing an expensive self-join:

WITH revenue_with_lag AS (
    SELECT sale_month,
           total_revenue,
           LAG(total_revenue, 1) OVER (ORDER BY sale_month ASC) AS prev_month_revenue
    FROM monthly_revenue
)
SELECT sale_month,
       total_revenue,
       prev_month_revenue,
       ROUND(
           ((total_revenue - prev_month_revenue) / NULLIF(prev_month_revenue, 0)) * 100.0,
           2
       ) AS mom_growth_pct
FROM revenue_with_lag
ORDER BY sale_month ASC;

Expected Output

Scenario 4: User Retention & 30-Day Cohort Activity

Interview Prompt

You have an activity_log table tracking user login events. Write a query to determine the percentage of users who returned to the application between 1 and 30 days after their initial signup date.

Schema Context

Table: activity_log

Production Solution: Cohort Modeling with CTEs

WITH user_signups AS (
    SELECT user_id,
           MIN(event_timestamp)::date AS signup_date
    FROM activity_log
    WHERE event_type = signup
    GROUP BY user_id
),
subsequent_logins AS (
    SELECT DISTINCT
           a.user_id,
           a.event_timestamp::date AS login_date
    FROM activity_log a
    WHERE a.event_type = login
),
retained_users AS (
    SELECT s.user_id,
           s.signup_date,
           CASE
               WHEN COUNT(l.login_date) > 0 THEN 1
               ELSE 0
           END AS is_retained_30d
    FROM user_signups s
    LEFT JOIN subsequent_logins l
           ON s.user_id = l.user_id
          AND l.login_date > s.signup_date
          AND l.login_date <= s.signup_date + INTERVAL '30 days'
    GROUP BY s.user_id, s.signup_date
)
SELECT COUNT(*)                                         AS total_signups,
       SUM(is_retained_30d)                             AS retained_users_30d,
       ROUND((SUM(is_retained_30d)::numeric / COUNT(*)) * 100.0, 2) AS retention_rate_30d_pct
FROM retained_users;

Key Technical Details

1. Inequality JOIN in ON Clause: The condition l.login_date > s.signup_date AND l.login_date <= s.signup_date + INTERVAL '30 days' belongs inside the ON clause. Moving this filter to WHERE would eliminate users with zero logins, making true retention calculations impossible.
2. Double Counting Prevention: The DISTINCT keyword inside subsequent_logins ensures that multiple logins on the same day do not inflate cohort volumes.

Scenario 5: Three-Valued Logic & The NOT IN (NULL) Trap

Interview Prompt

Identify all department IDs in the departments table that currently have no employees assigned to them in the employees table.

Schema Context

Table: departments

Table: employees

The Trap Query: NOT IN

Many candidates write:

-- DANGEROUS: Returns 0 rows if employees contains even a single NULL
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (
    SELECT department_id FROM employees
);

Why NOT IN Fails with NULL Values

In ANSI SQL, comparison with NULL yields UNKNOWN. The expression department_id NOT IN (10, 20, NULL) expands to: department_id != 10 AND department_id != 20 AND department_id != NULL Since department_id != NULL evaluates to UNKNOWN, the entire compound AND condition evaluates to UNKNOWN or FALSE for every single record. As a result, the query returns 0 rows.

The Robust Production Fix: NOT EXISTS

SELECT d.department_id,
       d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

NOT EXISTS relies on two-valued existential checks (whether a row exists matching the predicate or not), rendering it completely immune to NULL values in subqueries.

The 4-Step Communication Framework for Live Technical Rounds

Senior interviewers score candidates not just on final syntax, but on problem decomposition and defensive reasoning:

Practice Checklist for Data Analyst Interviews

Before attending your next technical screening, ensure you can execute these operations from memory without consulting documentation:

• Deduplicate tables deterministically using ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
• Distinguish between WHERE (pre-aggregation filter) and HAVING (post-aggregation filter)
• Write multi-period window offsets using LAG() and LEAD() with explicit NULLIF zero guards
• Convert dates and extract intervals using standard ANSI syntax (DATE_TRUNC, INTERVAL)
• Combine multiple datasets safely using UNION ALL instead of slower UNION when records are known to be distinct