SQL (Structured Query Language) is the backbone of data analysis. Once you’ve mastered the basics — SELECT, WHERE, and simple joins — it’s time to level up. Intermediate SQL queries allow analysts to unlock deeper insights, handle complex data relationships, and optimize performance.
In this post, we’ll explore 25 intermediate SQL queries every data analyst should know, along with explanations and use cases that will make you stand out in any data-driven role.
1. Filtering with Multiple Conditions
SELECT *
FROM sales
WHERE region = 'East' AND (revenue > 5000 OR discount > 10);Use logical operators (AND, OR, NOT) to fine-tune data retrieval and focus on relevant segments.
2. Using Aliases for Readability
SELECT c.customer_name AS name, o.order_id AS id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;Aliases make complex queries more readable — a must for teamwork and maintainability.
3. Filtering with BETWEEN
SELECT *
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30';Easily filter data within a range of dates or numbers.
4. Matching Multiple Values with IN
SELECT *
FROM employees
WHERE department IN ('Finance', 'HR', 'Marketing');A cleaner alternative to multiple OR conditions.
5. Pattern Matching with LIKE
SELECT *
FROM products
WHERE product_name LIKE 'Samsung%';Find values matching patterns — especially useful for text-based searches.
6. Aggregate Functions
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;Functions like SUM(), AVG(), MIN(), MAX() are key to summarizing data.
7. Filtering Aggregates with HAVING
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;Filter aggregated data — something WHERE can’t do.
8. INNER JOIN – Combining Data
SELECT c.customer_name, o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;The bread and butter of relational databases.
9. LEFT JOIN – Keeping All Left Records
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;Retrieve all records from the left table, even if there’s no match in the right table.
10. RIGHT JOIN
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;Less common but useful when the right table’s completeness matters.
11. Self JOIN – Comparing Within One Table
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;Compare rows within the same table — perfect for hierarchical data.
12. Subqueries (Nested Queries)
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Use subqueries to compare data with computed results dynamically.
13. Common Table Expressions (CTE)
WITH high_earners AS (
SELECT employee_id, salary
FROM employees
WHERE salary > 70000
)
SELECT * FROM high_earners;
CTEs improve readability and can be reused in complex queries.
14. CASE Statements
SELECT name,
CASE
WHEN score >= 90 THEN 'Excellent'
WHEN score >= 75 THEN 'Good'
ELSE 'Needs Improvement'
END AS performance
FROM students;
Bring logic into SQL for categorized outputs.
15. Handling NULL Values with COALESCE
SELECT employee_name, COALESCE(phone_number, 'N/A') AS phone
FROM employees;
Replace NULL values with defaults to keep data clean.
16. Removing Duplicates with DISTINCT
SELECT DISTINCT region
FROM sales;Remove duplicates and count unique values efficiently.
17. Combining Queries with UNION
SELECT city FROM customers
UNION
SELECT city FROM suppliers;Combine results from multiple queries — UNION ALL keeps duplicates.
18. ORDER BY Multiple Columns
SELECT *
FROM sales
ORDER BY region ASC, revenue DESC;
Sort data for better visualization or ranking.
19. LIMIT or TOP
SELECT *
FROM sales
ORDER BY revenue DESC
LIMIT 5;
Retrieve the top results — great for leaderboards or summaries.
20. Working with Dates
SELECT
order_id,
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month
FROM orders;
Analyze trends over time using date parts.
21. String Functions
SELECT UPPER(customer_name) AS name_caps, LENGTH(customer_name) AS name_length
FROM customers;Clean and manipulate text data efficiently.
22. Window Functions (ROW_NUMBER, RANK)
SELECT
employee_name,
department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;Powerful analytics for ranking, running totals, and comparisons.
23. Cumulative Sum (Running Total)
SELECT
order_id,
order_date,
SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM sales;Track growth or trends over time — vital for business reporting.
24. Pivoting Data
SELECT
department,
SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;Turn rows into columns to create summarized, report-friendly tables.
25. Creating Temporary Tables
CREATE TEMP TABLE temp_sales AS
SELECT * FROM sales WHERE region = 'West';Use temporary tables to simplify large analytical workflows.
Final Thoughts
Mastering these intermediate SQL queries is a turning point in your data analysis journey. You’ll gain the ability to:
- Handle real-world datasets efficiently
- Build powerful dashboards
- Optimize query performance
- Impress in SQL interviews
Remember — practice is key. Use sample databases like Chinook, Sakila, or public datasets on Kaggle to solidify your skills.
Pro Tips for Data Analysts
- Learn SQL indexing and query optimization next.
- Use window functions for advanced analytics.
- Practice writing SQL queries in PostgreSQL or MySQL Workbench.
If you found this helpful, consider bookmarking it or sharing it with your fellow analysts.
