The example tables
Every example below uses two small tables: customers (customer_id, name, city, signup_date) and orders (order_id, customer_id, order_date, amount, status). The same patterns apply to any database — PostgreSQL, MySQL, SQL Server or BigQuery.
1. Select and filter
SELECT chooses columns, WHERE filters rows, ORDER BY sorts and LIMIT caps the result.
- Use IN ('a','b') for lists and BETWEEN x AND y for ranges
- Use LIKE 'Hyd%' for text patterns
- Test for missing values with IS NULL — never = NULL
SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'completed'
AND order_date >= '2026-01-01'
ORDER BY amount DESC
LIMIT 10;2. Aggregate with GROUP BY
Aggregate functions summarise many rows into one: COUNT, SUM, AVG, MIN, MAX. HAVING filters after grouping; WHERE filters before.
SELECT customer_id,
COUNT(*) AS orders,
SUM(amount) AS revenue,
AVG(amount) AS avg_order
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 50000
ORDER BY revenue DESC;3. Combine tables with JOINs
- INNER JOIN — only rows that match in both tables
- LEFT JOIN — every row from the left table, with matches from the right (NULL when none)
- RIGHT / FULL OUTER JOIN — less common; same idea from the other side or both sides
-- Revenue by city, including cities whose customers haven't ordered yet
SELECT c.city,
COUNT(o.order_id) AS orders,
COALESCE(SUM(o.amount), 0) AS revenue
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'completed'
GROUP BY c.city
ORDER BY revenue DESC;4. Subqueries and CTEs
A subquery is a query inside another query. A CTE (WITH … AS) does the same job but reads top to bottom, which makes complex logic far easier to follow and debug.
WITH customer_revenue AS (
SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
JOIN customers c ON c.customer_id = cr.customer_id
WHERE cr.revenue > (SELECT AVG(revenue) FROM customer_revenue);5. Window functions
Window functions calculate across related rows without collapsing them — perfect for rankings, running totals and comparisons with the previous period. They appear in almost every analytics interview.
- ROW_NUMBER — unique sequence; RANK / DENSE_RANK — handle ties differently
- LAG / LEAD — previous / next row's value
- SUM / AVG OVER — running totals and moving averages
SELECT customer_id,
order_date,
amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_number,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_amount,
RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM orders
WHERE status = 'completed';6. Dates and CASE
Date function names differ slightly between databases (for example DATE_TRUNC in PostgreSQL, DATE_FORMAT in MySQL), but the idea is the same.
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(CASE WHEN amount >= 10000 THEN 1 ELSE 0 END) AS large_orders,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;Common interview questions to practise
- Find the second-highest order amount
- Find customers who signed up but never ordered (LEFT JOIN … WHERE o.order_id IS NULL)
- Monthly revenue and month-over-month growth (LAG)
- Top 3 customers per city (ROW_NUMBER with PARTITION BY)
- Remove duplicate rows while keeping the latest record
- Explain the difference between WHERE and HAVING, and between INNER and LEFT JOIN
Habits that make you better at SQL
- Write the FROM and JOINs first, then SELECT — think about which rows you need
- Check row counts after every join to catch accidental duplication
- Format queries with one clause per line and meaningful aliases
- Use CTEs to break problems into named steps