As a fresher you will mostly write queries on modest data. In a real company, tables can have hundreds of millions of rows, and a careless query can run for many minutes or slow the database for everyone. You do not need to be a database administrator, but a few habits go a long way.
1. Select only the columns you need
SELECT * reads every column. On wide tables, especially in columnar warehouses, that is much more data than you need. Name the columns.
2. Filter as early as possible
Put conditions in WHERE so the database discards rows before joining and grouping:
SELECT c.city, SUM(o.amount)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01'
GROUP BY c.city;Also filter on the partition or date column when tables are partitioned by date; many warehouses then read only the relevant partitions.
3. Understand indexes
An index is like the index at the back of a book: it lets the database find matching rows without reading the whole table. Columns used often in WHERE conditions and joins (IDs, dates) are usually indexed.
Indexes speed up reading but slow down writing and take space, so they are added deliberately, usually by engineers. As an analyst, the main thing is to write queries that can use them.
4. Do not wrap filtered columns in functions
-- Often cannot use an index on order_date
WHERE EXTRACT(YEAR FROM order_date) = 2026
-- Can use it
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'The same applies to LOWER(email) = ... or calculations on the column. Compare the raw column against a range or value instead where possible.
5. Be careful with leading wildcards
LIKE 'AC-%' can use an index; LIKE '%-AC' usually cannot, because the beginning of the text is unknown.
6. Aggregate before joining when you can
Joining a large orders table to another large table and then grouping creates a huge intermediate result. If you only need totals per customer, group orders first in a CTE, then join the small result.
7. Use EXISTS for "has at least one"
EXISTS can stop at the first match, while a join followed by DISTINCT processes every match.
8. Avoid accidental row explosions
A missing join condition or a join on a non-unique key multiplies rows. If a query is unexpectedly slow, check row counts at each step.
9. Limit while exploring
When you are just looking at the shape of data, add LIMIT 100. Do not run heavy queries just to peek.
10. Read the query plan
Most databases show how they will run a query:
EXPLAIN SELECT ...;Look for full table scans on huge tables, very large row estimates and expensive sorts. You do not need to understand every line to spot the obvious problem.
Readable is also important
A query that runs fast but nobody can understand is a risk. CTEs with clear names make queries easier to check, and modern databases usually optimise them well.
Practise
Writing clean, step-by-step queries is the foundation of fast ones. Our CTE, subquery and window function sets build exactly those habits.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: