7 SQL Concepts Every Data Analyst in India Must Know 2026

You have probably been told to "learn SQL" more times than you can count, and that advice is not wrong, just unhelpful. SQL is a big language, and most analyst jobs and interviews lean on a small, specific slice of it. Knowing which slice saves you weeks. This guide walks through the seven concepts that do the heavy lifting in real analyst work and in the SQL rounds at Indian product companies, startups, banks and analytics firms. Each one comes with a plain explanation, a short query you can run yourself, and a note on where it tends to show up in interviews.

Why These Seven

SQL is one of the most listed skills for analyst roles in India. In an AccioJob analysis of 328 data analytics job descriptions from companies that hire through it, SQL appeared in 197 of them, about 60 percent, second only to Excel. The sample is small and covers analyst, business analyst, data engineer and data scientist roles, so treat it as a signal, not a census. The job descriptions, however, only say "SQL". What interviewers test is narrower, and these seven concepts cover most of it.
 

All examples below use two small tables. customers has customer_id, name and city. orders has order_id, customer_id, city, order_date, amount (in ), status and discount. The queries are written in standard SQL and run on MySQL and PostgreSQL, with the few dialect differences called out where they matter.

1. Joins

Data rarely lives in one table. Customers sit in one place, orders in another, and a join is how you bring them together. The two you will use constantly are INNER JOIN, which keeps only rows that match on both sides, and LEFT JOIN, which keeps every row from the left table and fills the gaps with NULL.

The most useful trick built on a left join is finding what is missing, such as customers who have never placed an order:

sql

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Rows that found no match come back with NULL in the order columns, and the WHERE clause keeps exactly those. This pattern, called an anti-join, answers questions like "which users signed up but never bought" and "which products never sold".

Two things trip people up. A join on a column with duplicates multiplies rows, which quietly inflates totals, so always check row counts before and after a join. And putting a filter on the right table in the WHERE clause of a left join turns it into an inner join, so move that filter into the ON condition if you want to keep unmatched rows.

In interviews: "Difference between INNER and LEFT JOIN" is close to guaranteed. If you want the full walkthrough, read our guide on the difference between inner join and left join in SQL.

2. GROUP BY, HAVING and CASE WHEN

Business questions almost always begin with "how many" or "how much, by what". GROUP BY collapses rows into groups, aggregate functions such as COUNT, SUM and AVG summarise each group, and HAVING filters the groups after they are built. WHERE filters rows before grouping, which is the whole difference between the two.

CASE WHEN is what makes aggregation flexible. It lets you count or sum only the rows that meet a condition, all inside one query:

sql

SELECT city,
       COUNT(*) AS orders,
       SUM(amount) AS revenue,
       SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) AS returns
FROM orders
GROUP BY city
HAVING SUM(amount) > 50000
ORDER BY revenue DESC;

This returns one row per city with its order count, revenue in and number of returns, and drops any city under 50,000. A query like this is the backbone of most dashboards and weekly reports.

In interviews: expect "WHERE vs HAVING", and expect to write a query that reports two or three metrics per category in one go. Our explainer on the difference between WHERE and HAVING covers the edge cases.

3. Subqueries and CTEs

Some questions cannot be answered in one step. "Which customers spend more than the average customer?" needs the average first and the comparison second. A subquery nests one query inside another. A CTE (common table expression), written with WITH, gives that inner step a name and puts it at the top, which makes long queries readable.

sql

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spend
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spend
FROM customer_totals
WHERE total_spend > (SELECT AVG(total_spend) FROM customer_totals);

Read it top to bottom: first work out what each customer spent, then keep the ones above the average. Anyone reviewing your work can follow it, which counts for a lot in a team. In practice, CTEs are the habit that separates analysts who write maintainable SQL from those who write one giant nested query nobody dares to edit.

In interviews: you will be asked to "find customers above average" or "compare each group with the overall figure". For the basics of nesting, see what are subqueries and how do you use them in SQL.

4. Window Functions

If one concept decides who gets shortlisted at product companies, it is this one. A window function does a calculation across a set of related rows without collapsing them into one, so you keep the detail and gain the context. You define the set with OVER, split it with PARTITION BY and order it with ORDER BY.

The classic use is "top N per group". Here are the top two customers by spend in each city:

sql

SELECT *
FROM (
    SELECT city, customer_id, SUM(amount) AS spend,
           DENSE_RANK() OVER (PARTITION BY city ORDER BY SUM(amount) DESC) AS rnk
    FROM orders
    GROUP BY city, customer_id
) t
WHERE rnk <= 2;

A plain GROUP BY cannot do this, because it throws away the ranking inside each group. The same family of functions handles running totals and month-over-month comparison:

sql

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total,
       LAG(amount)  OVER (ORDER BY order_date) AS previous_order
FROM orders;

Learn four of them well: ROW_NUMBER, RANK and DENSE_RANK (which differ in how they treat ties), and LAG / LEAD for looking at the previous or next row. SUM() OVER for running totals completes the set.

In interviews: "second highest salary", "top 3 products per category" and "month-on-month growth" are all window function questions in disguise.

5. NULL Handling

NULL does not mean zero or an empty string. It means "unknown", and SQL treats unknowns strictly, which is where wrong numbers sneak in. A comparison with NULL is never true, so WHERE discount = NULL returns nothing; you must write IS NULL. Aggregates quietly skip NULLs, which changes your averages.

Run this against the sample data and look at the gap between the last two columns:

sql

SELECT COUNT(*)                   AS all_rows,
       COUNT(discount)            AS rows_with_discount,
       AVG(discount)              AS avg_ignoring_nulls,
       AVG(COALESCE(discount, 0)) AS avg_treating_null_as_zero
FROM orders;

With three discounts across six orders, the first average is 1,167 and the second 583. Neither is "wrong", but they answer different questions, and you need to know which one your stakeholder is asking. COUNT(*) counts rows, while COUNT(column) counts only non-NULL values. COALESCE replaces NULL with a default, and NULLIF turns a value into NULL, which is handy for avoiding divide-by-zero errors.

One more trap: NOT IN with a subquery that returns even one NULL returns no rows at all. Prefer NOT EXISTS or a left-join anti-join. We explain the rest in how SQL handles NULL values.

In interviews: "Difference between COUNT(*) and COUNT(column)" is a favourite screening question.

6. Indexing and Query Performance

On a table with a few hundred rows, every query is fast. On a table with 50 crore rows, the way you write the query decides whether it finishes in a second or times out. An index works like the index at the back of a book: instead of reading every page, the database jumps to the right one.

sql

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Analysts rarely create indexes, but you are expected to write queries that can use them. The common mistake is wrapping an indexed column in a function. This filter cannot use an index on order_date in most databases:

sql

WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 1

while this one can:

sql

WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01'

Two habits help straight away: select only the columns you need instead of SELECT *, and read the output of EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see whether the database scanned the whole table. For a fuller treatment, see our guide to indexing in SQL and why it matters.

In interviews: this comes up more in experienced hiring, usually as "this query is slow, how would you fix it?"

7. Keys and Normalisation

The last concept is about design, not queries. A primary key uniquely identifies each row, and a foreign key links a row to a primary key in another table. That relationship is what makes the joins in concept 1 possible.

Normalisation is the practice of splitting data so that each fact is stored once. Imagine one wide table where every order repeats the customer's name, phone number and address. When a customer changes their number, you must update it in hundreds of places, and miss a few. Split it into customers and orders, link them with customer_id, and the number lives in exactly one row. That is the idea behind the normal forms (1NF, 2NF, 3NF), and the reason most company databases look the way they do.

As an analyst you will mostly read normalised tables, so knowing the design helps you predict how to join them and spot duplicates. Reporting tables, by contrast, are often deliberately denormalised for speed, and a good analyst knows why.

In interviews: "What is normalisation?" and "Primary key vs foreign key" almost always appear in the opening fundamentals round.

A 30-Day Plan to Learn All Seven

You do not need to study all seven at once. This order works because each concept builds on the one before it.

Spend roughly half of each session writing queries from scratch rather than reading solutions. The fastest way to know you have learnt a concept is to explain it to someone else. Once you are comfortable, turn the skills into proof with a portfolio piece.