SQL for data analysis: beyond a basic SELECT *
Filtering and sorting are just the start. Grouping, joining tables, and window functions are where SQL actually becomes useful for answering business questions.
Anyone can write SELECT * FROM table. The gap between knowing SQL and using it for real analysis is in how you combine and aggregate data across tables to answer a concrete question.
GROUP BY: the base of any report
Almost every business report starts with an aggregation: sales by month, users by country, orders by status. GROUP BY collapses many rows into one per unique value of the grouped column.
SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country
ORDER BY total_users DESC;JOIN: combining data from several tables
Real data rarely lives in a single table. A JOIN combines rows from two tables based on an equality condition — typically between a foreign key and the primary key it references.
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;Why LEFT JOIN and not INNER JOIN here
An INNER JOIN would exclude customers with no orders; LEFT JOIN keeps them with 0 orders, which is usually the correct answer for an activity report.
Window functions: comparing without losing detail
A window function computes a value (a ranking, a running total) related to each individual row without collapsing the result, something GROUP BY can't do on its own.
SELECT name, total, RANK() OVER (ORDER BY total DESC) AS position
FROM sales;Practice with real data
The only way these patterns become natural is by writing queries against real data and seeing the result instantly. Explore the SQL courses and practice each pattern with immediate feedback.