The sample tables
The examples use two simple tables. orders has order_id, customer_id, order_date, amount and city. customers has customer_id, name and signup_date. Syntax follows PostgreSQL; date functions differ slightly between databases.
1–3: filter, aggregate, filter groups
-- 1. Filter and sort
SELECT order_id, amount FROM orders
WHERE city = 'Pune' AND amount > 5000
ORDER BY amount DESC LIMIT 10;
-- 2. Aggregate by group
SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders GROUP BY city;
-- 3. Filter groups with HAVING
SELECT city, SUM(amount) AS revenue FROM orders
GROUP BY city HAVING SUM(amount) > 100000;4–6: joins and subqueries
-- 4. Inner join
SELECT o.order_id, c.name, o.amount
FROM orders o JOIN customers c ON c.customer_id = o.customer_id;
-- 5. Left join to find customers with no orders
SELECT c.customer_id, c.name
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
-- 6. Subquery: orders above the average
SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);7–9: CTEs, CASE and top-N per group
-- 7. CTE for monthly revenue
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1)
SELECT month, revenue FROM monthly ORDER BY month;
-- 8. Bucket values with CASE
SELECT order_id,
CASE WHEN amount >= 10000 THEN 'high'
WHEN amount >= 3000 THEN 'medium' ELSE 'low' END AS band
FROM orders;
-- 9. Top 3 orders per city
SELECT * FROM (
SELECT city, order_id, amount,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
FROM orders) t
WHERE rn <= 3;10–12: running totals, latest row, pivots
-- 10. Running total
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- 11. Latest order per customer
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders) t
WHERE rn = 1;
-- 12. Pivot with conditional aggregation
SELECT city,
SUM(CASE WHEN order_date >= '2026-01-01' AND order_date < '2026-04-01' THEN amount ELSE 0 END) AS q1,
SUM(CASE WHEN order_date >= '2026-04-01' AND order_date < '2026-07-01' THEN amount ELSE 0 END) AS q2
FROM orders GROUP BY city;How to practise
- Type each query yourself against a small practice database instead of copying
- Change one condition at a time and predict the result before you run it
- Keep a file of your own patterns, each with a one-line note on when to use it
Pair SQL with Python for data science, build a project using our portfolio guide and explore Power BI careers.
Building a data career?
Talk to an advisor about data science programmes that fit your background.
Frequently Asked Questions
Which SQL concepts matter most for analysts?
Filtering, grouping, joins, CTEs and window functions cover most day-to-day analysis.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation.
When should I use a window function?
When you need a calculation across related rows without collapsing them, such as running totals or top-N per group.
Will these queries run on any database?
The patterns are general, but functions such as date truncation differ between databases, so check your system's documentation.