SQL for Data Analysts: The Complete Guide to Getting Hired (2026)

Your First SQL Query: Simple but Powerful

SELECT
    product_category,
    COUNT(*) as total_orders,
    SUM(revenue) as total_revenue,
    AVG(revenue) as avg_order_value
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY product_category
ORDER BY total_revenue DESC;

This single query answers "What are our top product categories this year?" Something that would take multiple pivot tables and VLOOKUP formulas in Excel.

Execution Order in Action:

-- You WRITE it in this order:
SELECT
    region,                          -- Step 5: Pick columns
    COUNT(*) as total_orders,        -- Step 5: Compute aggregates
    SUM(revenue) as total_revenue    -- Step 5: Compute aggregates
FROM orders                          -- Step 1: Start here
WHERE order_date >= '2025-01-01'     -- Step 2: Filter rows
GROUP BY region                      -- Step 3: Create groups
HAVING SUM(revenue) > 10000          -- Step 4: Filter groups
ORDER BY total_revenue DESC          -- Step 6: Sort
LIMIT 10;                            -- Step 7: Cap results

The database starts at FROM, not SELECT. This is why "total_revenue" works in ORDER BY (step 6, after SELECT) but not in WHERE (step 2, before SELECT).

SELECT customer_name, email, total_purchases
FROM customers
WHERE signup_date >= '2024-01-01'
  AND total_purchases > 500
  AND country = 'USA';

Find high-value US customers who signed up this year. Filters combine with AND, OR, IN, BETWEEN, and LIKE for pattern matching.

SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(revenue) as monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Monthly revenue and customer count trend. A classic analyst report you will write weekly.

SELECT
    c.customer_name,
    COUNT(o.order_id) as total_orders,
    SUM(o.amount) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.amount) > 1000;

Customer lifetime value analysis. LEFT JOIN keeps all customers, even those with zero orders.

SELECT customer_name, total_spending
FROM customers
WHERE total_spending > (
    SELECT AVG(total_spending)
    FROM customers
);

The inner query calculates the average; the outer query filters by it. One statement, two logical steps.

SELECT
    salesperson,
    month,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY salesperson ORDER BY month
    ) as running_total,
    RANK() OVER (
        PARTITION BY month ORDER BY revenue DESC
    ) as monthly_rank
FROM sales;

Running totals and rankings per salesperson. Two window functions, one query, zero row collapse.

SELECT
    customer_id,
    total_spent,
    CASE
        WHEN total_spent >= 5000 THEN 'VIP'
        WHEN total_spent >= 1000 THEN 'Regular'
        ELSE 'Occasional'
    END as customer_tier,
    CASE
        WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days'
            THEN 'Active'
        ELSE 'Inactive'
    END as status
FROM customers;

Two CASE expressions in one query: segment by spending tier and flag activity status. Your marketing team will request this weekly.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(revenue) as revenue
    FROM orders
    GROUP BY 1
),
revenue_with_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) as prev_month
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    prev_month,
    ROUND(100.0 * (revenue - prev_month) / prev_month, 1) as growth_pct
FROM revenue_with_growth
WHERE prev_month IS NOT NULL
ORDER BY month;

Month-over-month revenue growth in three clean steps. Each CTE builds on the last. Compare this readability to nested subqueries.

SELECT
    customer_id,
    COUNT(*) as purchase_count,
    SUM(amount) as total_spent,
    CASE
        WHEN COUNT(*) >= 10 AND SUM(amount) >= 5000 THEN 'VIP'
        WHEN COUNT(*) >= 5 THEN 'Regular'
        ELSE 'Occasional'
    END as customer_segment
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY customer_id;

Combines aggregation (COUNT, SUM), filtering (WHERE with dates), and conditional logic (CASE WHEN) in one query. The output feeds directly into a marketing dashboard.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(revenue) as revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month), 1
    ) as growth_pct
FROM monthly_revenue
ORDER BY month;

Uses a CTE for the first aggregation step, then LAG() to compare each month against the previous one. Your CFO will ask for this report monthly.

WITH cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', signup_date) as cohort_month
    FROM customers
)
SELECT
    co.cohort_month,
    DATE_PART('month', AGE(o.order_date, co.cohort_month))
        as months_since_signup,
    COUNT(DISTINCT co.customer_id) as active_customers
FROM cohorts co
JOIN orders o ON co.customer_id = o.customer_id
GROUP BY 1, 2
ORDER BY 1, 2;

The foundation of a cohort retention table. Product and marketing teams use this to measure whether customers stick around after their first purchase.

What Interviewers Actually Assess:

• Problem decomposition: Can you break a complex question into steps?
• Business intuition: Do you understand what the question is really asking?
• SQL fluency: Can you write clean, correct queries without constant Googling?
• Edge case awareness: Do you handle NULLs, duplicates, and data quality issues?