SQL is the language of data. As a data analyst, you start by mastering the basics — but real analytical power comes from learning advanced SQL queries. These queries allow you to handle complex datasets, optimize performance, and extract insights that basic queries simply can’t reveal.
In this guide, we’ll cover 25 advanced SQL queries every data analyst should know — complete with code examples, use cases, and explanations.
1. Using Multiple Window Functions Together
SELECT
employee_name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees;Combine window functions to create layered analytics such as rankings, running totals, or percentiles.
2. Cumulative Totals with Partitioning
SELECT
department,
employee_name,
SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cumulative_salary
FROM employees;Track running totals within groups for advanced trend analysis.
3. Percentile and NTILE Analysis
SELECT
employee_name,
department,
NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;Segment employees into quartiles or percentiles — useful for salary or performance distribution.
4. Recursive CTEs (Hierarchical Data)
WITH RECURSIVE org_chart AS (
SELECT employee_id, manager_id, employee_name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name
FROM employees e
INNER JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;
Handle hierarchical or tree-structured data, like org charts or category trees.
5. Dynamic Ranking Changes Over Time
SELECT
order_date,
product_id,
RANK() OVER (PARTITION BY EXTRACT(MONTH FROM order_date) ORDER BY revenue DESC) AS monthly_rank
FROM sales;Analyze how rankings or performance shift across time periods.
6. Advanced Date Calculations
SELECT
order_id,
order_date,
LEAD(order_date) OVER (ORDER BY order_date) AS next_order,
LEAD(order_date) OVER (ORDER BY order_date) - order_date AS days_between
FROM orders;
Use LEAD() and LAG() to calculate gaps between events — perfect for churn or retention analysis.
7. Pivoting with Conditional Aggregation
SELECT
department,
SUM(CASE WHEN gender = 'Male' THEN salary ELSE 0 END) AS male_salary,
SUM(CASE WHEN gender = 'Female' THEN salary ELSE 0 END) AS female_salary
FROM employees
GROUP BY department;
Transform rows into columns — a common requirement for BI dashboards.
8. Unpivoting Data
SELECT department, metric, value
FROM (
SELECT department, sales, profit
FROM finance
) t
UNPIVOT (
value FOR metric IN (sales, profit)
) AS unpvt;
Reverse pivot data back into long format for modeling and visualizations.
9. Correlated Subqueries
SELECT employee_name, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
Compare each row with an aggregate computed from its own group.
10. Common Table Expressions for Modular Queries
WITH filtered_orders AS (
SELECT * FROM orders WHERE order_date >= '2025-01-01'
),
ranked_orders AS (
SELECT *, RANK() OVER (ORDER BY total DESC) AS order_rank FROM filtered_orders
)
SELECT * FROM ranked_orders WHERE order_rank <= 5;
Break complex logic into reusable components — much cleaner than nested subqueries.
11. Analytical Rolling Averages
SELECT
order_date,
AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM sales;Smooth out fluctuations in time-series data — ideal for trend forecasting.
12. Finding Gaps in Sequential Data
SELECT
id + 1 AS missing_id
FROM sales s
WHERE NOT EXISTS (SELECT 1 FROM sales WHERE id = s.id + 1);Detect missing rows or IDs — crucial for data quality checks.
13. Ranking Within Nested Categories
SELECT
region,
department,
RANK() OVER (PARTITION BY region, department ORDER BY revenue DESC) AS rank
FROM sales;Perform granular analysis across multiple hierarchical levels.
14. Identifying Duplicates
SELECT name, COUNT(*) AS count
FROM customers
GROUP BY name
HAVING COUNT(*) > 1;
Locate duplicate records for cleaning and deduplication.
15. Self-Referencing Joins for Time Differences
SELECT
a.order_id,
a.customer_id,
b.order_id AS next_order,
b.order_date - a.order_date AS days_between
FROM orders a
JOIN orders b ON a.customer_id = b.customer_id AND b.order_date > a.order_date;Analyze time between actions — useful for retention or repeat-purchase analytics.
16. Recursive CTE for Date Generation
WITH RECURSIVE dates AS (
SELECT DATE '2025-01-01' AS dt
UNION ALL
SELECT dt + INTERVAL '1 DAY'
FROM dates
WHERE dt < '2025-01-31'
)
SELECT * FROM dates;Generate date series on the fly — great for filling in missing time intervals.
17. Advanced JOINs with Multiple Conditions
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
AND o.region = c.region;
Ensure your joins are logically precise and performance-friendly.
18. Windowed SUM with Conditional Logic
SELECT
customer_id,
SUM(CASE WHEN status = 'Completed' THEN amount ELSE 0 END)
OVER (PARTITION BY customer_id) AS total_completed
FROM transactions;Combine conditional logic with windowed aggregations for detailed analytics.
19. Using JSON Data in SQL
SELECT
order_id,
json_extract_path_text(order_details, 'product_name') AS product_name
FROM orders;
Modern databases (PostgreSQL, Snowflake, BigQuery) let you query structured JSON directly.
20. String Aggregation (GROUP_CONCAT / STRING_AGG)
SELECT
department,
STRING_AGG(employee_name, ', ') AS employees
FROM employees
GROUP BY department;Combine text values from multiple rows — useful for summaries or reports.
21. Lateral Joins (PostgreSQL / BigQuery)
SELECT c.customer_id, x.total_orders
FROM customers c
LEFT JOIN LATERAL (
SELECT COUNT(*) AS total_orders
FROM orders o
WHERE o.customer_id = c.customer_id
) x ON TRUE;
Use lateral joins to create subqueries dependent on each row of the main query.
22. Query Optimization with Index Hints
SELECT /*+ INDEX(customers idx_customer_name) */ *
FROM customers
WHERE customer_name = 'John Doe';Guide the optimizer to use a specific index for performance gains.
23. Analytical Lag Comparison
SELECT
order_date,
revenue,
revenue - LAG(revenue) OVER (ORDER BY order_date) AS revenue_change
FROM sales;
Quickly identify growth trends or performance dips between time periods.
24. Pivot with Dynamic Columns (SQL Server Example)
DECLARE @cols NVARCHAR(MAX) =
(SELECT STRING_AGG(DISTINCT QUOTENAME(region), ',') FROM sales);
EXEC('
SELECT * FROM
(SELECT region, month, revenue FROM sales) src
PIVOT (SUM(revenue) FOR region IN (' + @cols + ')) pvt;
');
Handle flexible pivoting when categories change dynamically.
25. Materialized Views for Speed
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
region,
EXTRACT(MONTH FROM order_date) AS month,
SUM(revenue) AS total_revenue
FROM sales
GROUP BY region, EXTRACT(MONTH FROM order_date);Precompute complex queries to improve performance on large datasets.
Final Thoughts
Mastering these advanced SQL queries transforms you from a data analyst into a data problem-solver. You’ll be able to:
- Handle large datasets efficiently
- Build scalable reporting systems
- Identify patterns across time, users, and categories
- Optimize query performance
Pro Tips for SQL Mastery
- Study query execution plans to improve speed.
- Practice with PostgreSQL, MySQL, or BigQuery for real-world versatility.
- Learn window functions and CTEs deeply — they’re the backbone of advanced SQL.
