Knowing how to write SELECT * FROM table is the very basics. What separates people who are just starting out from those who really deliver value with data is the ability to answer business questions: "what's the average ticket?", "which customers bought the most?". For that, you need aggregations, grouping and subqueries.

Aggregate functions: boiling many rows down to one number

Aggregate functions take many rows and return a single value. The five fundamental ones are:

• COUNT — counts rows. COUNT(*) counts everything; COUNT(column) ignores nulls.
• SUM — adds up numeric values.
• AVG — computes the average.
• MIN / MAX — the smallest and largest value.

On their own, they summarize the entire table. SELECT AVG(valor) FROM pedidos returns the average ticket across all orders.

GROUP BY: aggregating by category

We almost never want a summary of everything — we want it per customer, per month, per category. GROUP BY splits the rows into groups and applies the aggregation to each one separately.

SELECT cliente_id, COUNT(*) AS total_pedidos, SUM(valor) AS gasto
FROM pedidos
GROUP BY cliente_id;

The rule that causes the most errors: every column in the SELECT that is not inside an aggregate function must be in the GROUP BY.

WHERE vs HAVING: the difference that always comes up

Both filter, but at different moments of processing. Think of a production line:

For example: "customers who spent more than $1,000". The filter is on the sum, so you use HAVING:

SELECT cliente_id, SUM(valor) AS gasto
FROM pedidos
WHERE valor > 0
GROUP BY cliente_id
HAVING SUM(valor) > 1000;

Subqueries: queries inside queries

A subquery is a SELECT nested inside another one. Useful when the filter depends on a calculation. "Orders above the average":

SELECT id, valor
FROM pedidos
WHERE valor > (SELECT AVG(valor) FROM pedidos);

The inner subquery runs first, computes the average, and the result becomes the criterion of the outer query.

CTE: getting organized with WITH

When the logic gets complex, nested subqueries turn into a tangle. The CTE (Common Table Expression), with the WITH clause, gives a name to an intermediate result, making the query read like a step-by-step recipe.

WITH gasto_por_cliente AS (
  SELECT cliente_id, SUM(valor) AS gasto
  FROM pedidos
  GROUP BY cliente_id
)
SELECT * FROM gasto_por_cliente
WHERE gasto > 1000;

Most business questions are an aggregation with a filter. Whoever understands the order in which SQL processes things — filter, group, filter again — writes correct queries on the first try instead of guessing.

In interviews it's common to be asked to translate a question in plain words ("the top 5 best-selling products of the month") into SQL, and what's being evaluated is almost always the GROUP BY with HAVING or a well-placed subquery. Day to day, this shows up in every report and dashboard you need to feed.

Conclusion

Aggregations, GROUP BY, the WHERE/HAVING distinction, subqueries and CTEs make up the toolkit that turns raw tables into answers. All of this assumes you understand how tables relate to each other — if you still have doubts there, it's worth reviewing relational modeling before moving on.