Introduction
Data analysts rarely use SQL to answer isolated technical questions. Most tasks begin with a business question, such as which customers generated the most revenue, how monthly performance changed, which records are missing or which products rank highest within each category. Answering these questions often requires several SQL concepts to work together.
For example, an analyst may need to join customer and order tables, filter completed transactions, group revenue by month and rank results within each region. Knowing the individual commands is useful, but knowing which pattern to apply, and in what order, is what turns raw data into a reliable answer.
This cheat sheet brings the most reusable SQL patterns into one compact reference. It covers joins, common table expressions, conditional logic, date functions, window functions and ranking, using connected examples that show how the concepts support everyday analysis.
Syntax note: Examples use PostgreSQL-style SQL. Check the date and row-limiting syntax if you use MySQL, SQL Server, BigQuery or another database.
Sample schema: The examples use customers, orders and employees tables with common IDs, dates, amounts, status, region, department and salary fields.
SQL Query Order
| Logical order | Clause | Purpose |
| 1 | FROM and JOIN | Select and combine tables |
| 2 | WHERE | Filter rows |
| 3 | GROUP BY | Create groups |
| 4 | HAVING | Filter grouped results |
| 5 | SELECT | Choose output columns |
| 6 | ORDER BY | Sort results |
| 7 | LIMIT | Restrict returned rows |
SELECT region, SUM(amount) AS revenue
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY region
HAVING SUM(amount) > 1000
ORDER BY revenue DESC;
WHERE filters rows before aggregation. HAVING filters groups after aggregation.
1. SQL Joins
| Join | What it returns | Typical use |
| INNER JOIN | Matches from both tables | Customers who placed orders |
| LEFT JOIN | Every left-table row and available matches | All customers, including those without orders |
| FULL OUTER JOIN | Matched and unmatched rows from both tables | Comparing two datasets |
| SELF JOIN | Rows matched within the same table | Employees and their managers |
| CROSS JOIN | Every possible row combination | Creating combinations or data grids |
INNER JOIN
SELECT
o.order_id,
c.customer_name,
o.amount
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id;
LEFT JOIN
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
Use COUNT(o.order_id) instead of COUNT(*) when customers without orders should have an order count of zero.
LEFT JOIN Filter Placement
SELECT
c.customer_id,
COUNT(o.order_id) AS completed_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.status = 'completed'
GROUP BY c.customer_id;
Placing the order status condition in ON keeps customers without completed orders. Placing it in WHERE removes those customers from the result.
2. Common Table Expressions
A common table expression, or CTE, gives an intermediate result a name. It is useful when a query has several logical steps.
WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT
c.customer_name,
COALESCE(cr.total_revenue, 0) AS total_revenue
FROM customers AS c
LEFT JOIN customer_revenue AS cr
ON c.customer_id = cr.customer_id
ORDER BY total_revenue DESC;
| Use a CTE when | Use a subquery when |
| The logic has multiple steps | The logic is short and used once |
| An intermediate result needs a clear name | Nesting does not reduce readability |
| The same result is referenced again | No reuse is required |
A CTE is not automatically faster than a subquery. Performance depends on the database and its query plan.
3. CASE WHEN
CASE adds conditions to labels, calculations and aggregations.
Create Categories
SELECT
customer_id,
SUM(amount) AS total_revenue,
CASE
WHEN SUM(amount) >= 10000 THEN 'High value'
WHEN SUM(amount) >= 5000 THEN 'Medium value'
ELSE 'Low value'
END AS customer_segment
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;
Conditional Aggregation
SELECT
customer_id,
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END)
AS completed_value,
SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END)
AS cancelled_value
FROM orders
GROUP BY customer_id;
| Requirement | Pattern |
| Create a label | CASE WHEN amount >= 1000 THEN 'Large' ELSE 'Standard' END |
| Count matching rows | SUM(CASE WHEN condition THEN 1 ELSE 0 END) |
| Sum matching values | SUM(CASE WHEN condition THEN amount ELSE 0 END) |
| Avoid dividing by zero | value / NULLIF(denominator, 0) |
4. SQL Date Functions
Date functions differ across database systems.
| Task | PostgreSQL | MySQL | SQL Server |
| Current date | CURRENT_DATE | CURDATE() | CAST(GETDATE() AS date) |
| Add seven days | date + INTERVAL '7 days' | DATE_ADD(date, INTERVAL 7 DAY) | DATEADD(day, 7, date) |
| Days between dates | end_date - start_date | DATEDIFF(end_date, start_date) | DATEDIFF(day, start_date, end_date) |
| Extract year | EXTRACT(YEAR FROM date) | YEAR(date) | YEAR(date) |
| Start of month | DATE_TRUNC('month', date) | DATE_FORMAT(date, '%Y-%m-01') | DATEFROMPARTS(YEAR(date), MONTH(date), 1) |
Complete Date Range
SELECT *
FROM orders
WHERE order_date >= DATE '2026-08-01'
AND order_date < DATE '2026-09-01';
Using the first day of the next period as an exclusive end point includes every timestamp in August.
5. Window Functions
Window functions calculate across related rows without removing the original row-level detail.
function_name(value) OVER (
PARTITION BY group_column
ORDER BY sort_column
)
| Function | Purpose |
| SUM() OVER | Running or grouped total |
| LAG() | Previous row's value |
| ROW_NUMBER() | Unique sequential number |
| RANK() | Rank with gaps after ties |
| DENSE_RANK() | Rank without gaps after ties |
Running Total
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
Previous Value
SELECT
customer_id,
order_date,
amount,
LAG(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS previous_amount
FROM orders;
6. ROW_NUMBER, RANK and DENSE_RANK
Assume the ordered values are 90, 80, 80 and 70.
| Value | ROW_NUMBER() | RANK() | DENSE_RANK() |
| 90 | 1 | 1 | 1 |
| 80 | 2 | 2 | 2 |
| 80 | 3 | 2 | 2 |
| 70 | 4 | 4 | 3 |
Top Three Employees per Department
WITH ranked_employees AS (
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id
) AS row_num
FROM employees
)
SELECT *
FROM ranked_employees
WHERE row_num <= 3;
Use ROW_NUMBER() to return exactly three rows per department. Use RANK() or DENSE_RANK() when tied values should share a position.
Common SQL Patterns
| Task | Main SQL tools |
| Find duplicate values | GROUP BY, HAVING COUNT(*) > 1 |
| Find customers with no orders | LEFT JOIN, IS NULL or NOT EXISTS |
| Return the latest row per customer | ROW_NUMBER() ordered descending |
| Calculate month-over-month change | Date grouping and LAG() |
| Find the second-highest distinct value | DENSE_RANK() |
| Return the top N within each group | CTE and ranking function |
Mistakes to Check
| Mistake | Better approach |
| Using column = NULL | Use IS NULL |
| Using WHERE for an aggregate filter | Use HAVING |
| Hiding duplicate joins with DISTINCT | Check the join key and table relationship |
| Ignoring ties in rankings | Choose the ranking function deliberately |
| Omitting a stable secondary sort | Add a unique tie-breaker column |
| Assuming every database uses the same dates | Confirm the SQL dialect |
Put These SQL Patterns into Practice
Reviewing query patterns is useful for understanding syntax, but practical ability develops when you apply those patterns to a dataset without being shown the complete solution. A single task might require you to decide which tables to join, how to handle missing records, which date range to use and whether ties should share the same rank.
CompeteX data skills competitions provide structured challenges in SQL and data analytics where professionals can apply analytical concepts to practical problems. This helps move SQL learning beyond memorising commands and towards selecting the right approach, checking assumptions and producing a result that answers the underlying question.
Technical References