SQL for Data Analysts — Complete Guide

SELECT — Reading Data from a Table

SELECT *
FROM orders;
-- Returns every column and every row — useful for exploring a table

SELECT order_id, customer_id, order_date, amount_inr
FROM orders;

SELECT
    order_id,
    amount_inr,
    amount_inr * 0.18              AS gst_amount,
    amount_inr + amount_inr * 0.18 AS total_with_gst
FROM orders;

SELECT
    order_id       AS "Order Number",
    amount_inr     AS "Order Value (₹)",
    status         AS "Order Status"
FROM orders;

WHERE — Filtering Rows

SELECT order_id, customer_id, amount_inr
FROM orders
WHERE city = 'Noida';

SELECT order_id, amount_inr, status
FROM orders
WHERE city IN ('Noida', 'Delhi', 'Gurgaon')
  AND status = 'delivered'
  AND amount_inr > 500;

SELECT order_id, order_date, amount_inr
FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31';

SELECT customer_id, name, email
FROM customers
WHERE email LIKE '%@gmail.com'   -- ends with @gmail.com
   OR name LIKE 'Priya%';        -- starts with Priya

SELECT order_id, delivery_date
FROM orders
WHERE delivery_date IS NULL;     -- Orders not yet delivered
-- NEVER write: WHERE delivery_date = NULL (this always returns nothing)

GROUP BY & Aggregations — Summarising Data

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

SELECT
    city,
    COUNT(order_id)        AS total_orders,
    SUM(amount_inr)        AS total_revenue_inr,
    ROUND(AVG(amount_inr), 0) AS avg_order_value_inr
FROM orders
WHERE status != 'cancelled'
GROUP BY city
ORDER BY total_revenue_inr DESC;

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(order_id)                   AS orders,
    ROUND(SUM(amount_inr), 0)         AS revenue_inr
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

-- Cities with at least 100 orders AND avg order value above ₹1,000
SELECT
    city,
    COUNT(order_id)           AS orders,
    ROUND(AVG(amount_inr), 0) AS aov_inr
FROM orders
GROUP BY city
HAVING COUNT(order_id) >= 100
   AND AVG(amount_inr)   > 1000
ORDER BY aov_inr DESC;

JOINs — Combining Multiple Tables

-- Orders with customer names (only orders that have a matching customer)
SELECT
    o.order_id,
    c.name          AS customer_name,
    c.city,
    o.amount_inr,
    o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

-- All customers, whether or not they have placed an order
SELECT
    c.customer_id,
    c.name,
    c.city,
    COUNT(o.order_id)  AS total_orders,
    COALESCE(SUM(o.amount_inr), 0) AS total_spent_inr
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.city
ORDER BY total_spent_inr DESC;
-- Customers with 0 orders appear with total_orders = 0

SELECT
    o.order_id,
    c.name            AS customer_name,
    p.product_name,
    p.category,
    o.quantity,
    o.amount_inr
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products  p ON o.product_id  = p.product_id
WHERE o.order_date >= '2026-08-01'
ORDER BY o.amount_inr DESC
LIMIT 20;

-- LEFT JOIN with NULL check = rows in left table with no match in right
SELECT c.customer_id, c.name, c.city, c.signup_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL   -- no matching order found
ORDER BY c.signup_date DESC;

CASE WHEN — Conditional Logic

SELECT
    order_id,
    amount_inr,
    CASE
        WHEN amount_inr < 500               THEN 'Low Value'
        WHEN amount_inr BETWEEN 500 AND 1999 THEN 'Mid Value'
        WHEN amount_inr >= 2000             THEN 'High Value'
        ELSE 'Unknown'
    END AS value_tier
FROM orders;

SELECT
    CASE
        WHEN amount_inr < 500               THEN 'Low Value'
        WHEN amount_inr BETWEEN 500 AND 1999 THEN 'Mid Value'
        ELSE 'High Value'
    END AS value_tier,
    COUNT(*)            AS order_count,
    SUM(amount_inr)     AS tier_revenue
FROM orders
GROUP BY value_tier
ORDER BY tier_revenue DESC;

SELECT
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
    COUNT(CASE WHEN status = 'returned'  THEN 1 END) AS returned,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
    COUNT(*) AS total
FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31';

Subqueries — Queries Within Queries

SELECT order_id, customer_id, amount_inr
FROM orders
WHERE amount_inr > (
    SELECT AVG(amount_inr)
    FROM orders
    WHERE status = 'delivered'
)
ORDER BY amount_inr DESC;

-- Monthly revenue summary and its rank
SELECT
    month,
    revenue_inr,
    RANK() OVER (ORDER BY revenue_inr DESC) AS revenue_rank
FROM (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount_inr)                  AS revenue_inr
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) monthly_revenue
ORDER BY month;

CTEs — Common Table Expressions

WITH customer_revenue AS (
    SELECT
        c.customer_id,
        c.name,
        c.city,
        SUM(o.amount_inr) AS total_spent_inr
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    WHERE o.status = 'delivered'
    GROUP BY c.customer_id, c.name, c.city
),
ranked_customers AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY city ORDER BY total_spent_inr DESC) AS city_rank
    FROM customer_revenue
)
SELECT customer_id, name, city, total_spent_inr, city_rank
FROM ranked_customers
WHERE city_rank <= 3
ORDER BY city, city_rank;

WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount_inr)                  AS revenue_inr
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
mom_change AS (
    SELECT
        month,
        revenue_inr,
        LAG(revenue_inr) OVER (ORDER BY month) AS prev_revenue_inr
    FROM monthly_revenue
)
SELECT
    month,
    revenue_inr,
    prev_revenue_inr,
    ROUND(100.0 * (revenue_inr - prev_revenue_inr) / prev_revenue_inr, 1) AS mom_growth_pct
FROM mom_change
ORDER BY month;

Window Functions — Advanced Analytics

SELECT
    c.name,
    c.city,
    SUM(o.amount_inr) AS total_spent,
    RANK() OVER (
        PARTITION BY c.city
        ORDER BY SUM(o.amount_inr) DESC
    ) AS rank_in_city
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.city;

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY signup_date ASC
        ) AS row_num
    FROM customers
) deduped
WHERE row_num > 1;   -- All rows that are duplicates

SELECT
    DATE_FORMAT(order_date, '%Y-%m')                              AS month,
    SUM(amount_inr)                                               AS revenue,
    LAG(SUM(amount_inr)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m'))
                                                                  AS prev_revenue,
    ROUND(100.0 * (SUM(amount_inr) - LAG(SUM(amount_inr)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')))
          / LAG(SUM(amount_inr)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')), 1) AS mom_pct
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

SELECT
    month,
    monthly_revenue,
    SUM(monthly_revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount_inr)                  AS monthly_revenue
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) m
ORDER BY month;

How long does it take to learn SQL for a data analyst job?

With consistent daily practice (1–2 hours), most beginners reach a job-ready level of SQL in 6–10 weeks. Week 1–2: SELECT, WHERE, ORDER BY, basic aggregations. Week 3–4: GROUP BY, HAVING, multiple table JOINs. Week 5–6: Subqueries, CASE WHEN, date functions. Week 7–8: Window functions (ROW_NUMBER, RANK, LAG, LEAD, running totals). Week 9–10: CTEs, query optimisation basics, writing real analyst queries from scratch. The key is practice on real datasets — not just memorising syntax. Many candidates in India learn SQL theory but cannot write a query from a blank screen, which is what interviews test.

Which SQL should I learn — MySQL, PostgreSQL, or SQL Server?

Learn MySQL or PostgreSQL first — both use standard SQL and are widely used in India. The core syntax (SELECT, JOIN, GROUP BY, WHERE, HAVING, window functions, CTEs) is 95% identical across all SQL dialects. The 5% that differs is mainly in date functions, string functions, and some edge cases. In Indian companies: startups and e-commerce typically use MySQL or PostgreSQL; large enterprises use Oracle or SQL Server; analytics on cloud data warehouses uses BigQuery (Google) or Redshift (AWS) which are both SQL-based. Mastering one SQL dialect transfers immediately to others. Do not wait until you know which dialect your target company uses — start with MySQL or PostgreSQL today.

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. WHERE runs first — it removes rows from the dataset. Then GROUP BY groups what remains. Then HAVING removes groups that do not meet the condition. Example: to find cities where average order value is above ₹1,000, you cannot use WHERE avg(order_amount) > 1000 — WHERE does not know about aggregated values yet. You must use GROUP BY city HAVING AVG(order_amount) > 1000. A practical rule: if you are filtering on an aggregate function (SUM, AVG, COUNT, MAX, MIN), use HAVING. If you are filtering on a raw column value, use WHERE.

What SQL questions are asked in data analyst interviews in India?

Common SQL interview questions in India for data analyst roles: (1) Write a query to find the top 3 customers by revenue in each city — requires window function RANK() OVER (PARTITION BY city ORDER BY revenue DESC). (2) Find month-over-month revenue change — requires LAG() window function or self-join. (3) Find customers who placed an order in January but not in February — requires LEFT JOIN with NULL check or NOT EXISTS. (4) Find duplicate records in a table — requires GROUP BY + HAVING COUNT > 1. (5) What is the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN — conceptual. (6) Write a query to find the second-highest salary — requires LIMIT 1 OFFSET 1 or DENSE_RANK(). (7) Explain NULL handling in SQL — IS NULL vs = NULL. Companies like Flipkart, Swiggy, PhonePe, Paytm, and analytics consulting firms ask questions in this range.