SQL is the language every data analyst must speak fluently. Whether you're working with MySQL, PostgreSQL, SQL Server, or BigQuery — these 10 functions appear in real analytical work every single day. Master them and you'll handle 90% of business data questions confidently.
1. SELECT with WHERE and ORDER BY
The foundation of every query. SELECT specifies columns, WHERE filters rows by condition, ORDER BY sorts results. Always filter as early as possible — it reduces data volume and speeds up execution. Use LIMIT during development to avoid accidentally pulling millions of rows.
2. GROUP BY with Aggregate Functions
GROUP BY collapses rows into groups and aggregate functions calculate summary statistics per group:
SUM() — total values
COUNT() — number of rows (use COUNT(*) to include NULLs)
AVG() — average
MAX() / MIN() — highest and lowest values
Example: SELECT region, SUM(revenue) FROM sales GROUP BY region ORDER BY 2 DESC
3. INNER JOIN
Combines rows from two tables where a key matches in both. This is the most common join in practice. Always specify which table each column belongs to using aliases to avoid ambiguity — especially when column names repeat across tables.
4. LEFT JOIN
Returns all rows from the left table and matching rows from the right. When there's no match, right-side columns return NULL. Invaluable for finding customers with no orders, products with no sales, or employees with no manager assigned.
5. CASE WHEN
SQL's conditional logic, equivalent to IF/ELSE. Used to create custom categories, labels, or calculated columns:
CASE WHEN revenue > 100000 THEN 'High' WHEN revenue > 50000 THEN 'Medium' ELSE 'Low' END AS tier
Use inside aggregate functions to count or sum by condition without filtering rows out.
6. COALESCE
Returns the first non-NULL value from a list of arguments. Essential for replacing NULLs with defaults to prevent calculation errors: COALESCE(phone_number, 'Not provided')
7. HAVING
Filters groups after aggregation — WHERE cannot filter on aggregate results. Use HAVING when your condition involves SUM, COUNT, AVG, etc:
SELECT product, COUNT(*) as orders FROM sales GROUP BY product HAVING COUNT(*) > 100
8. Subqueries
A query nested inside another query. Use subqueries when you need to filter on an aggregated value that isn't available in the outer query. For readability, prefer CTEs (below) over deeply nested subqueries.
9. CTEs (WITH Clause)
CTEs let you define temporary named result sets and reference them later in the same query. They make complex logic readable and maintainable — like breaking a long formula into named intermediate steps. Always prefer CTEs over repeated subqueries.
10. Window Functions
The most powerful SQL feature most analysts learn last. Window functions calculate values across rows without collapsing the result set:
ROW_NUMBER() — unique sequential number per partition
RANK() — rank with gaps for ties
LAG() / LEAD() — access previous/next row values
SUM() OVER (PARTITION BY ...) — running totals
Example: SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank FROM employees
How to Actually Learn These
Don't just read — practice on real data immediately. Use Mode Analytics, SQLZoo, or LeetCode's SQL section. Solve 50 problems covering all 10 functions. At that point, SQL stops feeling like a language you're translating and starts feeling like thinking.
