DEV Community: Vivek Kumar

SQL Date & Time Functions: A Practical Guide for Real-World Queries

Vivek Kumar — Mon, 04 May 2026 05:53:12 +0000

Working with dates and times is one of those things that sounds simple until you're staring at a timestamp column wondering why your "last 30 days" query returns unexpected results. Date and time functions are essential for analytics, reporting, and any application that tracks events over time — yet the subtle differences between database engines trip up even experienced developers.

In this guide, we'll cover the most useful SQL date/time functions across PostgreSQL and MySQL — two of the most widely used databases — with practical, real-world examples. By the end, you'll know how to confidently filter by time ranges, calculate durations, group data by period, and avoid the most common pitfalls.

Why Date/Time Functions Matter

Think about how many queries involve time: "Show me orders placed in the last 7 days," "Calculate how long customers have been subscribed," "Group revenue by month." Nearly every meaningful business question has a temporal dimension.

The challenge is that SQL databases don't all speak the same date dialect. PostgreSQL has DATE_TRUNC and AGE. MySQL has DATE_FORMAT and TIMESTAMPDIFF. Standard SQL gives you EXTRACT and CURRENT_TIMESTAMP. Knowing what's available — and what differs — saves you hours of debugging.

We'll use a consistent example throughout: an e-commerce database with orders and customers tables.

-- Our example schema
CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  name          VARCHAR(100),
  joined_at     TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  order_id      INT PRIMARY KEY,
  customer_id   INT REFERENCES customers(customer_id),
  placed_at     TIMESTAMP NOT NULL,
  shipped_at    TIMESTAMP,
  total_amount  DECIMAL(10,2)
);

1. Getting the Current Date and Time

The most basic need: "what time is it right now?" Every major database supports these, but syntax varies slightly:

-- PostgreSQL
SELECT NOW();                  -- current timestamp with time zone
SELECT CURRENT_TIMESTAMP;      -- same as NOW(), SQL standard
SELECT CURRENT_DATE;           -- date only (no time)
SELECT CURRENT_TIME;           -- time only

-- MySQL
SELECT NOW();                  -- current datetime
SELECT CURDATE();              -- date only
SELECT CURTIME();              -- time only
SELECT UTC_TIMESTAMP();        -- current UTC datetime (very useful!)

Real-world use: Find all orders placed today.

-- PostgreSQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE placed_at::date = CURRENT_DATE;

-- MySQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE DATE(placed_at) = CURDATE();

Note: Casting a timestamp to date (or using DATE()) forces the database to discard the time component before comparing — otherwise placed_at = CURRENT_DATE would fail because a timestamp like 2026-04-28 14:33:00 is not equal to the date 2026-04-28.

2. Extracting Parts of a Date

Sometimes you need just the year, month, or hour from a timestamp. EXTRACT is the SQL-standard way to do this, supported by both PostgreSQL and MySQL:

-- Standard SQL — works in both PostgreSQL and MySQL
SELECT
  order_id,
  placed_at,
  EXTRACT(YEAR  FROM placed_at) AS order_year,
  EXTRACT(MONTH FROM placed_at) AS order_month,
  EXTRACT(DOW   FROM placed_at) AS day_of_week,  -- 0=Sunday in PostgreSQL
  EXTRACT(HOUR  FROM placed_at) AS order_hour
FROM orders;

Real-world use: Group orders by month to see monthly revenue trends.

SELECT
  EXTRACT(YEAR  FROM placed_at) AS yr,
  EXTRACT(MONTH FROM placed_at) AS mo,
  COUNT(*)                      AS order_count,
  SUM(total_amount)             AS revenue
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;

MySQL shorthand functions: MySQL also offers dedicated extraction functions that are a bit more readable:

-- MySQL-specific shortcuts
SELECT
  YEAR(placed_at)    AS order_year,
  MONTH(placed_at)   AS order_month,
  DAY(placed_at)     AS order_day,
  HOUR(placed_at)    AS order_hour,
  DAYNAME(placed_at) AS weekday_name   -- e.g. "Tuesday"
FROM orders;

3. Date Arithmetic with INTERVAL

Adding or subtracting time from a date is a common need: "all orders from the last 30 days," "subscriptions expiring in the next 7 days," etc.

-- PostgreSQL: use INTERVAL keyword
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL '30 days';

-- MySQL: use DATE_SUB() or INTERVAL notation
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL 30 DAY;

-- Also valid in MySQL:
SELECT * FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

INTERVAL supports a wide range of units:

-- PostgreSQL examples
NOW() + INTERVAL '1 year'
NOW() - INTERVAL '3 months'
NOW() + INTERVAL '2 hours 30 minutes'
'2026-01-01'::date + INTERVAL '90 days'

-- MySQL examples
DATE_ADD(NOW(), INTERVAL 1 YEAR)
DATE_ADD(NOW(), INTERVAL -3 MONTH)
DATE_ADD(shipped_at, INTERVAL 14 DAY)   -- estimated delivery date

Real-world use: Flag orders that shipped more than 14 days ago but never had a follow-up.

-- PostgreSQL
SELECT order_id, customer_id, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL
  AND shipped_at < NOW() - INTERVAL '14 days';

4. Calculating Differences Between Dates

How many days between two dates? How long has a customer been with us? These questions require date difference functions.

DATEDIFF (MySQL)

-- MySQL: DATEDIFF returns difference in days
SELECT
  order_id,
  placed_at,
  shipped_at,
  DATEDIFF(shipped_at, placed_at) AS days_to_ship
FROM orders
WHERE shipped_at IS NOT NULL;

TIMESTAMPDIFF (MySQL)

TIMESTAMPDIFF is more flexible — you choose the unit:

-- MySQL: specify the unit you want
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  TIMESTAMPDIFF(YEAR,  c.joined_at, NOW()) AS years_as_customer,
  TIMESTAMPDIFF(MONTH, c.joined_at, NOW()) AS months_as_customer,
  TIMESTAMPDIFF(DAY,   c.joined_at, NOW()) AS days_as_customer
FROM customers c;

AGE (PostgreSQL)

PostgreSQL has the elegant AGE() function that returns a human-readable interval:

-- PostgreSQL
SELECT
  customer_id,
  name,
  joined_at,
  AGE(NOW(), joined_at)          AS customer_age,  -- e.g. "2 years 3 mons 12 days"
  EXTRACT(DAY FROM AGE(NOW(), joined_at))           AS days_as_customer
FROM customers;

Real-world use: Find customers who joined more than 1 year ago but have placed no order in the last 90 days (candidates for a win-back campaign).

-- PostgreSQL
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  MAX(o.placed_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.joined_at < NOW() - INTERVAL '1 year'
GROUP BY c.customer_id, c.name, c.joined_at
HAVING MAX(o.placed_at) < NOW() - INTERVAL '90 days'
    OR MAX(o.placed_at) IS NULL;

5. Truncating Dates with DATE_TRUNC (PostgreSQL)

DATE_TRUNC is a PostgreSQL superpower for grouping data by time periods. It rounds a timestamp down to the start of the given period:

SELECT DATE_TRUNC('month', NOW());
-- Result: 2026-04-01 00:00:00+00

SELECT DATE_TRUNC('week',  NOW());
-- Result: 2026-04-27 00:00:00+00 (start of current week, Monday)

SELECT DATE_TRUNC('year',  NOW());
-- Result: 2026-01-01 00:00:00+00

Supported precision values include: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century.

Real-world use: Weekly revenue report with clean week boundaries:

-- PostgreSQL
SELECT
  DATE_TRUNC('week', placed_at)  AS week_start,
  COUNT(*)                        AS orders,
  SUM(total_amount)               AS revenue,
  AVG(total_amount)               AS avg_order_value
FROM orders
WHERE placed_at >= NOW() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', placed_at)
ORDER BY week_start;

In MySQL, you can approximate this with DATE_FORMAT:

-- MySQL: group by week using DATE_FORMAT
SELECT
  DATE_FORMAT(placed_at, '%Y-%u')  AS year_week,  -- e.g. "2026-17"
  COUNT(*)                          AS orders,
  SUM(total_amount)                 AS revenue
FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 12 WEEK)
GROUP BY DATE_FORMAT(placed_at, '%Y-%u')
ORDER BY year_week;

6. Formatting Dates for Display

Raw timestamps aren't user-friendly. Format them for reports and APIs:

-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(placed_at, 'Month DD, YYYY')   AS display_date;  -- "April 28, 2026"
SELECT TO_CHAR(placed_at, 'YYYY-MM-DD HH24:MI') AS display_dt;   -- "2026-04-28 14:33"
SELECT TO_CHAR(placed_at, 'Day')               AS weekday;       -- "Tuesday"

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(placed_at, '%M %d, %Y')     AS display_date;  -- "April 28, 2026"
SELECT DATE_FORMAT(placed_at, '%Y-%m-%d %H:%i') AS display_dt;   -- "2026-04-28 14:33"
SELECT DATE_FORMAT(placed_at, '%W')             AS weekday;       -- "Tuesday"

Common Mistakes and Gotchas

1. Applying a function to an indexed column kills performance

If you have an index on placed_at, this query can't use it:

-- ❌ Wrapping the column in a function prevents index usage
WHERE DATE(placed_at) = CURDATE()          -- MySQL
WHERE DATE_TRUNC('day', placed_at) = ...   -- PostgreSQL

Instead, use a range comparison that leaves the column untouched:

-- ✅ Range filter — the index on placed_at can be used
WHERE placed_at >= '2026-04-28 00:00:00'
  AND placed_at <  '2026-04-29 00:00:00'

-- Or dynamically:
WHERE placed_at >= CURRENT_DATE
  AND placed_at <  CURRENT_DATE + INTERVAL '1 day'

2. Ignoring time zones

NOW() in PostgreSQL returns timestamptz (with time zone). If your app stores timestamps in UTC but your NOW() uses a local session timezone, you can get wildly wrong results. Always be explicit:

-- PostgreSQL: force UTC
WHERE placed_at >= NOW() AT TIME ZONE 'UTC' - INTERVAL '24 hours'

In MySQL, use UTC_TIMESTAMP() instead of NOW() when you want UTC regardless of session settings.

3. NULL shipped_at breaks duration calculations

If shipped_at is NULL (order not yet shipped), a DATEDIFF or subtraction involving it returns NULL — not an error, just silent NULL propagation. Always handle this:

-- MySQL
SELECT
  order_id,
  COALESCE(DATEDIFF(shipped_at, placed_at), 'Not yet shipped') AS fulfillment_days
FROM orders;

4. DATEDIFF argument order differs by database

MySQL: DATEDIFF(end_date, start_date) → positive if end is after start
SQL Server: DATEDIFF(unit, start_date, end_date) → note the unit comes first!
PostgreSQL: use subtraction directly: end_date - start_date

This trips up developers switching between databases constantly.

Summary: Key Takeaways

Working with dates in SQL is a skill that pays dividends across virtually every query you'll write for analytics or reporting. The core ideas to remember:

Use NOW() / CURRENT_TIMESTAMP for the current moment; CURRENT_DATE for today's date
EXTRACT() pulls out specific date parts (year, month, day of week) — it's standard SQL
Use INTERVAL for date arithmetic — keep the indexed column bare on the left side of your WHERE clause
DATEDIFF / TIMESTAMPDIFF (MySQL) and AGE() / simple subtraction (PostgreSQL) handle duration calculations
DATE_TRUNC (PostgreSQL) is your best friend for grouping by week/month/quarter
DATE_FORMAT (MySQL) and TO_CHAR (PostgreSQL) format dates for display
Always consider NULLs and time zones — they're the source of most date-related bugs

What's Your Favorite Date Function?

Date functions are deceptively powerful. Once you're comfortable with DATE_TRUNC for time-series aggregation or TIMESTAMPDIFF for cohort analysis, your SQL queries go from useful to genuinely insightful.

Drop a comment below — what's the most useful date/time query pattern you use regularly? I'd love to see what real-world problems you're solving! And if you found this helpful, share it with a colleague who's wrestling with a "last 30 days" filter. 🗓️

SQL GROUP BY & HAVING: The Beginner's Guide to Summarizing Data Like a Pro

Vivek Kumar — Mon, 27 Apr 2026 05:23:51 +0000

If you've ever needed to answer questions like "How many orders did each customer place?" or "Which product categories generated more than $10,000 in revenue?", you've needed GROUP BY and HAVING. These two clauses are the backbone of summarizing and analyzing relational data in SQL.

In this guide, we'll go from zero to confident with both clauses. You'll understand what they do, how they differ from each other (and from WHERE), and walk away with real working examples you can adapt immediately.

What Is GROUP BY?

The GROUP BY clause collapses multiple rows that share the same value in one or more columns into a single summary row. You almost always pair it with an aggregate function — COUNT(), SUM(), AVG(), MIN(), or MAX() — to compute something meaningful about each group.

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(other_column)
FROM table_name
GROUP BY column_name;

A Simple Example

Imagine an orders table:

order_id	customer_id	amount	status
1	101	50.00	completed
2	102	120.00	completed
3	101	30.00	completed
4	103	200.00	pending
5	102	75.00	completed

How many orders has each customer placed?

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Result:

customer_id	order_count
101	2
102	2
103	1

SQL gathered all rows for customer 101 and counted them — giving you one result row per unique customer_id.

GROUP BY With Multiple Aggregate Functions

You're not limited to one aggregate per query. Here's how to get the total and average order amount per customer in one shot:

SELECT
  customer_id,
  COUNT(*)        AS order_count,
  SUM(amount)     AS total_spent,
  AVG(amount)     AS avg_order_value
FROM orders
GROUP BY customer_id;

Result:

customer_id	order_count	total_spent	avg_order_value
101	2	80.00	40.00
102	2	195.00	97.50
103	1	200.00	200.00

GROUP BY Multiple Columns

You can group by more than one column to create finer-grained buckets. Let's say you want to see order counts broken down by both customer_id and status:

SELECT
  customer_id,
  status,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status;

Result:

customer_id	status	order_count
101	completed	2
102	completed	2
103	pending	1

Each unique combination of customer_id + status becomes its own group.

Introducing HAVING: Filtering After Grouping

GROUP BY creates groups. HAVING lets you filter those groups based on the result of an aggregate function.

Think of HAVING as a WHERE clause that runs after grouping — it can reference aggregate results that don't exist until after grouping happens.

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(other_column)
FROM table_name
GROUP BY column_name
HAVING AGGREGATE_FUNCTION(other_column) condition;

Example: Find High-Value Customers

Which customers have spent more than $100 in total?

SELECT
  customer_id,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

Result:

customer_id	total_spent
102	195.00
103	200.00

Customer 101 (who spent $80 total) is filtered out because their aggregate didn't meet the condition.

WHERE vs. HAVING: The Critical Difference

This trips up many beginners. Here's a clear mental model:

WHERE filters individual rows before grouping
HAVING filters groups after grouping

They can work together in the same query. Here's an example using both:

Find customers with more than one completed order:

SELECT
  customer_id,
  COUNT(*) AS completed_order_count
FROM orders
WHERE status = 'completed'        -- Filter rows BEFORE grouping
GROUP BY customer_id
HAVING COUNT(*) > 1;              -- Filter groups AFTER grouping

Result:

customer_id	completed_order_count
101	2
102	2

The WHERE status = 'completed' removes the pending order for customer 103 before any grouping occurs. Then HAVING COUNT(*) > 1 removes customer groups with only one order.

The Execution Order of a SQL Query

Understanding why WHERE and HAVING behave differently becomes much clearer when you know the logical order SQL processes a query:

FROM — identify the table(s)
WHERE — filter individual rows
GROUP BY — collapse rows into groups
HAVING — filter groups
SELECT — compute output columns
ORDER BY — sort
LIMIT/OFFSET — paginate

WHERE runs at step 2, before any grouping. HAVING runs at step 4, after groups exist. This is why you cannot use an aggregate function inside a WHERE clause — the aggregates simply don't exist yet at that stage.

Common Mistakes to Avoid

1. Selecting a non-grouped, non-aggregated column

-- ❌ This will fail (or return unpredictable results in MySQL)
SELECT customer_id, amount, COUNT(*)
FROM orders
GROUP BY customer_id;

amount is neither in GROUP BY nor wrapped in an aggregate function. The database doesn't know which amount to show for the group. Fix it: either add amount to GROUP BY, or aggregate it (e.g., SUM(amount)).

2. Using WHERE to filter aggregates

-- ❌ This will throw an error
SELECT customer_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 100
GROUP BY customer_id;

SUM(amount) doesn't exist at the WHERE stage. Use HAVING instead:

-- ✅ Correct
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

3. Forgetting that GROUP BY treats NULLs as a single group

If your grouped column has NULL values in multiple rows, all those NULL rows will be collapsed into one group. This is usually fine — just be aware that a NULL group will appear in your results unless you filter it out with WHERE column IS NOT NULL before grouping.

4. Confusing COUNT(*) and COUNT(column)

COUNT(*) counts all rows in the group, including those with NULL values
COUNT(column) counts only rows where that column is not NULL

SELECT
  customer_id,
  COUNT(*)           AS total_rows,
  COUNT(amount)      AS non_null_amounts
FROM orders
GROUP BY customer_id;

If amount is never NULL in your table, these will be equal. But in tables with optional fields, the difference matters.

A Real-World Example: Sales Report by Category

Let's put it all together with a slightly richer scenario. Here's a products table and a sales table:

-- Find product categories with more than 5 sales,
-- showing total revenue and average sale price,
-- sorted by total revenue descending
SELECT
  p.category,
  COUNT(s.sale_id)    AS total_sales,
  SUM(s.sale_price)   AS total_revenue,
  AVG(s.sale_price)   AS avg_price
FROM sales s
JOIN products p ON s.product_id = p.product_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY p.category
HAVING COUNT(s.sale_id) > 5
ORDER BY total_revenue DESC;

This query:

Joins sales with products
Filters to sales from 2024 onward (WHERE)
Groups by product category
Keeps only categories with more than 5 sales (HAVING)
Sorts by revenue, highest first

Key Takeaways

GROUP BY collapses rows that share a value into summary groups; always pair it with an aggregate function
HAVING filters groups based on aggregate results — it's the post-grouping version of WHERE
WHERE and HAVING can coexist in the same query and serve complementary roles
Every column in your SELECT list must be either in GROUP BY or inside an aggregate function
SQL processes WHERE before grouping and HAVING after grouping — this is why you can't use aggregate functions in a WHERE clause

What's Next?

GROUP BY and HAVING open the door to powerful data analysis. Once you're comfortable here, great next steps include:

Window Functions — aggregate over groups without collapsing rows
CTEs — break complex GROUP BY queries into readable, step-by-step logic
Indexes on grouped columns — for performance when your tables get large

Have you run into a tricky GROUP BY or HAVING situation at work? Drop it in the comments — I'd love to see real-world examples from the community!

SQL Window Functions Explained: Stop Collapsing Your Data with GROUP BY

Vivek Kumar — Mon, 27 Apr 2026 05:23:42 +0000

You're writing a query to find each employee's salary alongside the average salary for their department. Your first instinct might be to reach for GROUP BY — but then you'd lose the individual employee rows. So you write a subquery, or a join against an aggregated CTE, and suddenly a simple question becomes a 20-line monster.

Window functions exist precisely to solve this. They let you perform calculations across a set of related rows without collapsing your result set. The row stays intact; the calculation rides alongside it. Once you understand them, you'll wonder how you ever lived without them.

This guide covers the fundamentals: what a window is, how OVER, PARTITION BY, and ORDER BY work inside it, and the most useful window functions you'll reach for every day.

What Is a Window Function?

A window function performs a calculation over a "window" — a defined set of rows related to the current row. Unlike GROUP BY, which aggregates many rows into one, a window function adds a new computed column to each row.

The syntax always follows this pattern:

function_name() OVER (
  [PARTITION BY column(s)]
  [ORDER BY column(s)]
  [ROWS/RANGE frame_clause]
)

The OVER clause is what tells SQL "this is a window function." Without it, SUM() is a regular aggregate; with it, SUM() OVER (...) is a window function.

The Setup: A Sample Schema

We'll use two tables throughout:

-- employees table
CREATE TABLE employees (
  id         INT,
  name       VARCHAR(100),
  department VARCHAR(50),
  salary     NUMERIC(10,2),
  hire_date  DATE
);

INSERT INTO employees VALUES
  (1,  'Alice',   'Engineering', 95000, '2019-03-15'),
  (2,  'Bob',     'Engineering', 88000, '2020-07-01'),
  (3,  'Carol',   'Engineering', 102000,'2018-11-20'),
  (4,  'Dave',    'Marketing',   72000, '2021-01-10'),
  (5,  'Eve',     'Marketing',   78000, '2020-05-22'),
  (6,  'Frank',   'Marketing',   68000, '2022-03-30'),
  (7,  'Grace',   'HR',          61000, '2021-09-14'),
  (8,  'Heidi',   'HR',          65000, '2019-06-01');

PARTITION BY: The Heart of Windowing

PARTITION BY divides your rows into groups — like GROUP BY, but the groups are only used to scope the calculation, not reduce the rows.

Example 1: Department average salary alongside each employee

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

Result:

name	department	salary	dept_avg_salary
Carol	Engineering	102000	95000.00
Alice	Engineering	95000	95000.00
Bob	Engineering	88000	95000.00
Eve	Marketing	78000	72666.67
Dave	Marketing	72000	72666.67
Frank	Marketing	68000	72666.67
Heidi	HR	65000	63000.00
Grace	HR	61000	63000.00

Every row is preserved. Each employee now has their department's average salary right next to their own. With a traditional GROUP BY, you'd need a join or subquery to get this result.

ORDER BY Inside OVER: Running Calculations

When you add ORDER BY inside OVER, SQL calculates the function cumulatively as it processes rows in order. This is perfect for running totals or running averages.

Example 2: Running total of salary by hire date within each department

SELECT
  name,
  department,
  hire_date,
  salary,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
  ) AS running_total
FROM employees
ORDER BY department, hire_date;

Result for Engineering:

name	department	hire_date	salary	running_total
Carol	Engineering	2018-11-20	102000	102000
Alice	Engineering	2019-03-15	95000	197000
Bob	Engineering	2020-07-01	88000	285000

The running_total grows as we move through hire dates — that's the ORDER BY hire_date doing its work.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

Three of the most useful window functions are ranking functions. They all assign a number to each row, but they handle ties differently.

Example 3: Ranking employees by salary within their department

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY department, salary DESC;

To see the difference between RANK and DENSE_RANK, let's imagine two Marketing employees with the same salary. Here's what happens:

name	salary	ROW_NUMBER	RANK	DENSE_RANK
Eve	78000	1	1	1
Dave	72000	2	2	2
Frank	68000	3	3	3

The differences become clear with ties:

ROW_NUMBER() — Always assigns a unique number, even to ties. Ties get arbitrary ordering (non-deterministic).
RANK() — Ties share the same rank, and the next rank skips numbers. Two employees tied at rank 2 means the next rank is 4.
DENSE_RANK() — Ties share the same rank, but the next rank is always consecutive. Two tied at rank 2 means the next is rank 3.

Use ROW_NUMBER() when you need exactly one row per partition (e.g., "get the single most recent order per customer"). Use DENSE_RANK() when the gap in RANK() would be confusing to end users.

A Practical Use Case: Top-N Per Group

One of the most common interview questions and real-world problems: "Give me the top 2 earners in each department."

WITH ranked_employees AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS salary_rank
  FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE salary_rank <= 2
ORDER BY department, salary DESC;

Result:

name	department	salary
Carol	Engineering	102000
Alice	Engineering	95000
Eve	Marketing	78000
Dave	Marketing	72000
Heidi	HR	65000
Grace	HR	61000

This pattern — window function in a CTE, filter in the outer query — is a workhorse of SQL analytics.

Common Mistakes and Gotchas

1. Filtering on a window function directly in WHERE

This doesn't work:

-- ❌ This will error
SELECT name, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 2;

Window functions are evaluated after WHERE, so you can't filter on them in the same query level. Always wrap them in a CTE or subquery first, then filter — as shown in the top-N example above.

2. Forgetting that ORDER BY inside OVER changes the frame

Adding ORDER BY to your OVER clause implicitly changes the window frame from "all rows in the partition" to "all rows up to and including the current row." This is usually what you want for running totals, but it can surprise you when you use SUM() or AVG():

-- These two behave differently!
AVG(salary) OVER (PARTITION BY department)                    -- whole partition
AVG(salary) OVER (PARTITION BY department ORDER BY hire_date) -- cumulative avg

If you want the whole-partition average but with a specific sort for row numbering, you may need to use separate window function expressions.

3. Mixing window functions and GROUP BY

Window functions and GROUP BY can coexist in the same query, but the window function operates on the already-grouped result set. This can lead to unexpected results if you're not careful about what rows exist after grouping.

4. Performance on large tables without proper indexes

Window functions with PARTITION BY and ORDER BY can be expensive. Make sure the columns you're partitioning and ordering by are indexed, especially on large tables. Use EXPLAIN ANALYZE to check the query plan.

Key Takeaways

Window functions are one of the most powerful tools in SQL for analytics and reporting:

The OVER() clause is what makes a function a window function — it defines the rows in scope for each calculation.
PARTITION BY divides rows into independent groups, like GROUP BY but without collapsing rows.
ORDER BY inside OVER enables running/cumulative calculations.
ROW_NUMBER(), RANK(), and DENSE_RANK() solve ranking and top-N problems elegantly.
You cannot filter on window function results in WHERE — use a CTE or subquery instead.

The next step after mastering these basics is exploring LAG() and LEAD() for comparing a row to previous/next rows, and frame clauses (ROWS BETWEEN) for precise control over the window range. Those are the power tools on top of this foundation.

Over to You

Window functions clicked for me the moment I stopped thinking of them as "fancy aggregates" and started thinking of them as "a calculation that knows its neighbors." What was the moment it clicked for you?

Drop your favorite window function use case in the comments — I'd love to see the creative ways people apply these in production. And if you're just getting started, try rewriting one of your correlated subqueries as a window function. You might never go back.

SQL LIKE and Wildcards: Pattern Matching Made Simple

Vivek Kumar — Fri, 24 Apr 2026 03:47:44 +0000

Searching for an exact value in a database is easy — you just use =. But what do you do when you only know part of the value? Maybe you want to find all customers whose email ends in @gmail.com, or all products with "pro" somewhere in the name. That's where SQL's LIKE operator comes in.

In this guide you'll learn how LIKE works, how to use both wildcard characters, and the gotchas that trip people up. By the end you'll be writing confident pattern-matching queries.

The LIKE Operator

LIKE is used in a WHERE clause to match a column value against a pattern string. If the value matches the pattern, the row is included in the results.

SELECT *
FROM customers
WHERE email LIKE '%@gmail.com';

The % is a wildcard — we'll cover exactly what it means in a moment.

Let's use a concrete table for all examples. Imagine a products table:

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    name         VARCHAR(100),
    category     VARCHAR(50),
    sku          VARCHAR(20)
);

With data like this:

product_id	name	category	sku
1	Wireless Mouse Pro	Electronics	EL-001-A
2	Mouse Pad XL	Accessories	AC-002-B
3	Mechanical Keyboard	Electronics	EL-003-A
4	USB-C Hub Pro	Electronics	EL-004-C
5	Laptop Stand	Accessories	AC-005-A
6	ProCam Webcam	Electronics	EL-006-B

The Two Wildcard Characters

SQL's LIKE operator uses two special wildcard characters:

% — matches zero or more characters of any kind

_ — matches exactly one character

That's it. Mastering LIKE is just about knowing when to use each.

Using % (Percent) — Any Length Match

The percent sign is the wildcard you'll reach for most often. It can stand in for any number of characters, including zero.

Starts with a pattern:

-- Products whose name starts with "Mouse"
SELECT name FROM products WHERE name LIKE 'Mouse%';

Result: Mouse Pad XL

Ends with a pattern:

-- Products whose name ends with "Pro"
SELECT name FROM products WHERE name LIKE '%Pro';

Result: Wireless Mouse Pro, USB-C Hub Pro

Contains a pattern anywhere:

-- Products with "pro" anywhere in the name (case-insensitive in many DBs)
SELECT name FROM products WHERE name LIKE '%Pro%';

Result: Wireless Mouse Pro, USB-C Hub Pro, ProCam Webcam

Notice that ProCam Webcam matched too — %Pro% matches "Pro" at the start, middle, or end.

Combining multiple percent signs:

-- SKUs that start with "EL" and end with "A"
SELECT name, sku FROM products WHERE sku LIKE 'EL%A';

name	sku
Wireless Mouse Pro	EL-001-A
Mechanical Keyboard	EL-003-A
Laptop Stand	AC-005-A

Wait — Laptop Stand (SKU AC-005-A) didn't match because its SKU starts with AC, not EL. Only EL-001-A and EL-003-A would actually be returned. The pattern EL%A means: starts with "EL", then any characters, then ends with "A".

Using _ (Underscore) — Single Character Match

The underscore matches exactly one character — no more, no less. It's useful when you know the structure of a value but not one specific character.

-- SKUs where the category code is 2 characters, followed by "-00", then any digit, then "-A"
SELECT name, sku FROM products WHERE sku LIKE '__-00_-A';

Each _ stands for exactly one character:

name	sku
Wireless Mouse Pro	EL-001-A
Mechanical Keyboard	EL-003-A
Laptop Stand	AC-005-A

A more practical example: finding phone numbers with a specific area code structure.

-- Phone numbers matching the pattern (555) ###-####
SELECT name, phone FROM contacts WHERE phone LIKE '(555) ___-____';

NOT LIKE — Excluding Patterns

Just like NOT IN or NOT NULL, you can flip LIKE with NOT:

-- Products that are NOT in the Electronics category (by SKU prefix)
SELECT name, sku FROM products WHERE sku NOT LIKE 'EL%';

Result: Mouse Pad XL (AC-002-B) and Laptop Stand (AC-005-A).

Case Sensitivity: It Depends on Your Database

This is one of the most common sources of confusion with LIKE:

MySQL: Case-insensitive by default for text columns with standard collation
PostgreSQL: Case-sensitive by default — use ILIKE for case-insensitive matching
SQLite: Case-insensitive for ASCII characters only
SQL Server: Depends on the column's collation (often case-insensitive)

-- PostgreSQL: this WON'T match "Wireless Mouse Pro"
SELECT name FROM products WHERE name LIKE '%pro%';

-- PostgreSQL: use ILIKE for case-insensitive
SELECT name FROM products WHERE name ILIKE '%pro%';
-- Returns: Wireless Mouse Pro, USB-C Hub Pro, ProCam Webcam

Always check your database's collation settings if results look unexpected.

Escaping Wildcards — When % or _ Are Literal Characters

What if you need to search for a literal percent sign or underscore — like finding discount codes that contain _? You need to escape them with an escape character.

-- Find discount codes that literally contain an underscore
SELECT code FROM discount_codes WHERE code LIKE '%\_%' ESCAPE '\';

-- Or in PostgreSQL, the default escape character is \
SELECT code FROM discount_codes WHERE code LIKE '%\_%';

Most databases use \ as the default escape character, but you can define your own with the ESCAPE keyword.

Common Mistakes to Avoid

1. Leading wildcard kills index usage

-- ❌ This forces a full table scan — no index can help
SELECT * FROM products WHERE name LIKE '%Mouse%';

-- ✅ A leading literal is index-friendly
SELECT * FROM products WHERE name LIKE 'Mouse%';

If you frequently search with a leading %, consider a full-text search solution instead (PostgreSQL has tsvector/tsquery, MySQL has FULLTEXT indexes).

2. Forgetting case sensitivity differences across databases

A query that works in MySQL may silently return wrong results in PostgreSQL. Use ILIKE (PostgreSQL) or LOWER() to be explicit:

SELECT * FROM products WHERE LOWER(name) LIKE LOWER('%pro%');

3. Using LIKE when you mean =

If you're matching a complete, exact value with no wildcards, just use = — it's faster and clearer:

-- ❌ Unnecessary LIKE
WHERE category LIKE 'Electronics'

-- ✅ Just use equals
WHERE category = 'Electronics'

4. Expecting _ to match zero characters

The underscore always matches exactly one character. 'Pro_' will NOT match 'Pro' — it needs at least one more character after "Pro".

Putting It All Together

Here's a realistic query combining several concepts — finding electronics products with "Pro" in the name and an "A"-grade SKU:

SELECT
    product_id,
    name,
    sku,
    category
FROM products
WHERE
    category = 'Electronics'
    AND name ILIKE '%pro%'         -- case-insensitive "pro" anywhere in name
    AND sku LIKE '%-A';            -- SKU ends with "-A" (A-grade)
ORDER BY name;

product_id	name	sku	category
1	Wireless Mouse Pro	EL-001-A	Electronics

Key Takeaways

LIKE matches values against a pattern using two wildcards: % (any number of characters) and _ (exactly one character)
NOT LIKE excludes rows that match a pattern
Case sensitivity depends on your database — PostgreSQL uses ILIKE for case-insensitive matching
Leading wildcards (LIKE '%value') disable index usage — avoid them on large tables where performance matters
Use = instead of LIKE when you don't need pattern matching
Escape literal % or _ characters using the ESCAPE keyword

Pattern matching with LIKE comes up constantly — searching user-inputted names, filtering SKUs, querying log messages, and more. Once you internalize the two wildcards and the case-sensitivity quirk, you'll reach for LIKE with confidence.

Do you have a favourite LIKE pattern you use regularly? Or ran into a tricky case-sensitivity bug? Share it in the comments — I'd love to hear how you use pattern matching in the real world!

SQL NULLs Demystified: What They Are and How to Handle Them

Vivek Kumar — Tue, 21 Apr 2026 03:44:41 +0000

SQL NULLs can be one of the most confusing aspects of working with databases — even for people who already know how to write SELECT statements and JOIN tables. You write a perfectly logical WHERE clause, run your query, and still get results (or missing results) that don't make sense. Nine times out of ten, NULL is the culprit.

In this article, we'll demystify what NULL actually is in SQL, why it behaves the way it does, and how to handle it confidently in your queries.

What Exactly Is NULL?

NULL is not zero. NULL is not an empty string ''. NULL is not false. NULL means the absence of a value — it represents the unknown.

Think of it this way: if you have a users table and a user hasn't provided their phone number yet, the phone column isn't empty — it's unknown. SQL represents that unknown state as NULL.

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    phone       VARCHAR(20),        -- optional, can be NULL
    age         INT                 -- optional, can be NULL
);

INSERT INTO users (name, email, phone, age) VALUES
    ('Alice Johnson', 'alice@example.com', '555-1234', 29),
    ('Bob Smith',     'bob@example.com',   NULL,       34),
    ('Carol White',   'carol@example.com', '555-5678', NULL),
    ('David Brown',   'david@example.com', NULL,       NULL);

Here, Bob and David have no phone on file, Carol and David have no age recorded. Their values are truly unknown, not blank.

Checking for NULL: IS NULL and IS NOT NULL

Here's where beginners make their first mistake. You cannot use = NULL to check for NULL values. This is because NULL is not equal to anything — not even to itself.

-- ❌ This will NOT work — returns 0 rows!
SELECT * FROM users WHERE phone = NULL;

-- ✅ This is correct
SELECT * FROM users WHERE phone IS NULL;

id	name	email	phone	age
2	Bob Smith	bob@example.com	NULL	34
4	David Brown	david@example.com	NULL	NULL

And to find users who do have a phone number:

SELECT name, phone
FROM users
WHERE phone IS NOT NULL;

name	phone
Alice Johnson	555-1234
Carol White	555-5678

NULL in Comparisons: Three-Valued Logic

SQL uses what's called three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL evaluates to UNKNOWN — and rows that evaluate to UNKNOWN are excluded from results, just like FALSE.

-- What does SQL think of these?
SELECT
    NULL = NULL,         -- UNKNOWN (not TRUE!)
    NULL <> NULL,        -- UNKNOWN
    NULL > 5,            -- UNKNOWN
    NULL = 0,            -- UNKNOWN
    5 = 5                -- TRUE
;

This is why WHERE phone = NULL silently returns nothing — the comparison is UNKNOWN for every row.

COALESCE: Replacing NULL with a Default Value

The COALESCE function is your best friend for handling NULLs in output. It returns the first non-NULL value in a list of arguments.

SELECT
    name,
    COALESCE(phone, 'No phone on file') AS phone_display,
    COALESCE(age, 0) AS age_display
FROM users;

name	phone_display	age_display
Alice Johnson	555-1234	29
Bob Smith	No phone on file	34
Carol White	555-5678	0
David Brown	No phone on file	0

You can also chain multiple fallback values:

-- Try preferred_email first, then email, then a hard-coded fallback
SELECT
    name,
    COALESCE(preferred_email, email, 'unknown@example.com') AS contact_email
FROM users;

NULLIF: Turning a Value INTO NULL

NULLIF is the reverse of COALESCE — it converts a specific value to NULL if it matches a second argument. This is useful for avoiding division-by-zero errors or treating sentinel values like empty strings as NULLs.

-- Safely divide without crashing on zero denominators
SELECT
    product_name,
    total_revenue,
    total_units,
    total_revenue / NULLIF(total_units, 0) AS revenue_per_unit
FROM product_sales;

Without NULLIF, dividing by zero raises a database error. With it, you get NULL instead — which is the honest answer when there are no units sold.

How NULL Affects Aggregate Functions

This one surprises many beginners: aggregate functions like COUNT, AVG, SUM, MIN, and MAX all ignore NULL values automatically.

SELECT
    COUNT(*)          AS total_rows,
    COUNT(phone)      AS rows_with_phone,
    COUNT(age)        AS rows_with_age,
    AVG(age)          AS avg_age
FROM users;

total_rows	rows_with_phone	rows_with_age	avg_age
4	2	2	31.5

Notice:

COUNT(*) counts all 4 rows (including rows with NULLs)
COUNT(phone) counts only 2 rows — those where phone is not NULL
AVG(age) calculates (29 + 34) / 2 = 31.5 — it ignores Carol and David's NULL ages entirely

This is usually what you want, but be aware of it! If you need to count rows including NULLs for a specific column, use COUNT(*) or COALESCE first.

Common NULL Gotchas

Gotcha 1: NULL in a NOT IN list

This one bites even experienced developers:

-- Say blocked_ids has values (2, NULL)
SELECT * FROM users
WHERE id NOT IN (2, NULL);
-- Returns 0 rows! Because NOT IN with NULL = UNKNOWN for everything

When using NOT IN, if the subquery or list contains any NULL values, the whole expression evaluates to UNKNOWN for every row and you get nothing back. Use NOT EXISTS or filter out NULLs from the list instead.

Gotcha 2: NULL in GROUP BY

NULL values in a GROUP BY column are grouped together as if they were equal (a special exception to the normal NULL comparison rules):

SELECT age, COUNT(*) AS user_count
FROM users
GROUP BY age;

age	user_count
29	1
34	1
NULL	2

Carol and David are grouped together under the NULL age bucket.

Gotcha 3: String concatenation with NULL

In most databases, concatenating NULL with a string produces NULL:

SELECT 'Hello, ' || NULL;  -- Returns NULL, not 'Hello, '

Use COALESCE to guard against this:

SELECT 'Hello, ' || COALESCE(name, 'stranger') FROM users;

Key Takeaways

NULL means unknown, not zero, not empty string
Always use IS NULL or IS NOT NULL — never = NULL
Any comparison with NULL produces UNKNOWN (not TRUE or FALSE)
Use COALESCE to substitute a default for NULL values in output
Use NULLIF to convert a specific value back into NULL (great for preventing division by zero)
Aggregate functions silently ignore NULLs — COUNT(column) vs COUNT(*) behaves differently
Be very careful with NOT IN when NULLs might be present in the list

Once NULL "clicks," a whole category of mysterious query bugs disappears. Keep these rules handy the next time your WHERE clause returns unexpected results!

Have you ever been burned by a NULL-related bug? Share your story in the comments — I'd love to hear it! And if this cleared things up, hit the ❤️ button to help other beginners find it.

SQL LIMIT and OFFSET: Paginate Your Query Results Like a Pro

Vivek Kumar — Wed, 15 Apr 2026 07:41:25 +0000

Every app that shows lists of data — a product catalog, a blog feed, a leaderboard — needs pagination. And at the heart of SQL-based pagination sit two deceptively simple keywords: LIMIT and OFFSET. If you've ever wondered how websites show you "Page 1 of 47" or a "Load more" button, this is the mechanism behind it.

In this guide we'll cover exactly what LIMIT and OFFSET do, how to use them correctly, and — just as importantly — where they can bite you when your dataset grows.

What Problem Do LIMIT and OFFSET Solve?

Imagine your products table has 50,000 rows. If you run a bare SELECT * FROM products, your database has to send all 50,000 rows to your application. That's slow, wastes memory, and overwhelms users who only want to see the first 20 items.

LIMIT caps how many rows the database returns. OFFSET tells it where to start.

Basic Syntax

SELECT column1, column2
FROM table_name
ORDER BY some_column
LIMIT number_of_rows
OFFSET rows_to_skip;

Note: MySQL also supports a shorthand: LIMIT offset, count — but the two-argument form can be confusing because the order is reversed from what you might expect. Using the explicit LIMIT … OFFSET … form is clearer and works in PostgreSQL, SQLite, and MySQL alike.

Example: A Simple Product Catalog

Let's say we have this table:

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100),
    category    VARCHAR(50),
    price       NUMERIC(10, 2),
    created_at  TIMESTAMP DEFAULT NOW()
);

Get the first 10 products, sorted by price:

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10;

id	name	category	price
42	USB-C Cable 1m	Accessories	4.99
7	Microfiber Cloth	Accessories	5.49
19	AA Batteries (4pk)	Electronics	6.99
…7 more rows…

Get the second page (rows 11–20):

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10
OFFSET 10;

Get the third page (rows 21–30):

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10
OFFSET 20;

The pattern is simple: OFFSET = (page_number - 1) * page_size.

Real-World Example: Blog Post Feed

Here's what this looks like in a blog application with a posts table:

CREATE TABLE posts (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(200),
    author_id  INT,
    published  BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

Fetch page 3 of published posts, 5 per page, newest first:

SELECT id, title, created_at
FROM posts
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 5
OFFSET 10;

You can even make this dynamic. In application code you'd pass the page number as a parameter:

-- Page number: 5, Page size: 20
SELECT id, title, created_at
FROM posts
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 20
OFFSET 80;   -- (5 - 1) * 20 = 80

Common Mistakes and Gotchas

1. Forgetting ORDER BY

This is the most important rule: always pair LIMIT/OFFSET with ORDER BY.

Without ORDER BY, the database can return rows in any order — and that order can change between queries. Your "page 2" might contain rows that also appeared on "page 1".

-- ❌ BAD — results are unpredictable
SELECT * FROM products LIMIT 10 OFFSET 10;

-- ✅ GOOD — deterministic ordering
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

2. OFFSET Doesn't Skip Work — It Just Throws It Away

Here's the performance trap that surprises many developers: OFFSET 10000 doesn't mean the database starts reading from row 10,001. It means the database reads all 10,001 rows and then discards the first 10,000 before returning results to you.

For small datasets this is fine. For large datasets this becomes very slow:

-- This query reads and discards 990,000 rows, then returns 10
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 990000;

For most beginner use cases (tables with a few thousand rows), this is not a problem. But it's good to know for when your app grows.

3. LIMIT Without a Number Is Invalid in Standard SQL

-- ❌ INVALID — can't use OFFSET without LIMIT in most databases
SELECT * FROM products OFFSET 5;

-- ✅ Use a very large number if you want "all rows starting from offset"
SELECT * FROM products ORDER BY id OFFSET 5 LIMIT ALL;  -- PostgreSQL only

4. Counting Total Pages Requires a Separate Query

LIMIT and OFFSET only return a slice of data. To show "Page 3 of 47", you need a separate count:

-- Get total count for pagination controls
SELECT COUNT(*) AS total_posts
FROM posts
WHERE published = TRUE;

-- Then compute: total_pages = CEIL(total_posts / page_size)

Putting It All Together: A Complete Pagination Example

Here's a realistic scenario: a comments section on a news site, showing 10 comments per page.

-- Schema
CREATE TABLE comments (
    id         SERIAL PRIMARY KEY,
    post_id    INT NOT NULL,
    author     VARCHAR(100),
    body       TEXT,
    upvotes    INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Page 1: top-voted comments on post #42
SELECT id, author, body, upvotes
FROM comments
WHERE post_id = 42
ORDER BY upvotes DESC, created_at DESC
LIMIT 10
OFFSET 0;

-- Page 2
SELECT id, author, body, upvotes
FROM comments
WHERE post_id = 42
ORDER BY upvotes DESC, created_at DESC
LIMIT 10
OFFSET 10;

Notice the double ORDER BY — we sort by votes first (so the best comments appear first), then by date as a tiebreaker. This makes results stable and predictable.

Quick Reference

Goal	SQL snippet
First N rows	`LIMIT N`
Skip first N rows, take next M	`LIMIT M OFFSET N`
Page P with page size S	`LIMIT S OFFSET (P-1)*S`
Count total rows (for page count)	`SELECT COUNT(*) FROM …`

Key Takeaways

LIMIT controls how many rows are returned. OFFSET controls where in the result set to start.
Always use ORDER BY with LIMIT/OFFSET — without it, results are non-deterministic.
The formula for page-based pagination is OFFSET = (page - 1) * page_size.
OFFSET can become slow on very large datasets because the database still reads (and discards) all skipped rows — but for most beginner use cases, this won't be an issue.
Always run a separate COUNT(*) query to know the total number of pages.

LIMIT and OFFSET are among the first tools you'll reach for when building real applications with SQL. They're simple, powerful, and supported by virtually every relational database out there.

Have you hit any pagination bugs in your own projects? Drop them in the comments — I'd love to hear what tripped you up and how you solved it! 🚀

SQL Indexes Explained: Write Queries That Don't Make Your Database Cry

Vivek Kumar — Mon, 13 Apr 2026 17:41:34 +0000

SQL Indexes Explained: Write Queries That Don't Make Your Database Cry

Every developer eventually hits the moment where a query that looked perfectly fine suddenly takes 12 seconds to run in production. You stare at it in disbelief — it's not that complicated! Then someone suggests adding an index, you do, and the query drops to 20ms. Magic? Not quite. Once you understand what indexes actually do, you'll know exactly when to add them, how to design them well, and when not to use them.

This guide is for developers who write SQL regularly but want to move beyond "just add an index" to actually understanding the mechanics. We'll cover B-tree indexes, composite indexes, covering indexes, and the common mistakes that leave performance on the table — using PostgreSQL syntax throughout (MySQL and SQL Server are nearly identical).

What Is an Index, Really?

An index is a separate data structure that the database maintains alongside your table. Think of it like the index at the back of a textbook: instead of reading every page to find "normalization," you look it up alphabetically and jump straight to the right page.

Without an index, the database does a sequential scan — it reads every row in the table to find what you need. For a table with 10 rows, that's fine. For a table with 10 million rows, it's painful.

-- Without an index on email, this scans every row
SELECT * FROM users WHERE email = 'alice@example.com';

With an index on email, the database uses a tree structure to find the matching row in a fraction of the time.

B-Tree: The Default Index Type

When you create an index without specifying a type, you get a B-tree (balanced tree). B-tree indexes store values in sorted order, organized as a tree where each level narrows down the search. They work brilliantly for:

Exact matches: WHERE status = 'active'
Range queries: WHERE created_at >= '2025-01-01'
Prefix searches: WHERE name LIKE 'Smi%'
Sorting: ORDER BY last_name

Creating a Simple Index

-- Syntax
CREATE INDEX idx_users_email ON users (email);

-- Confirm it exists
\d users   -- in psql, or:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

Now the query below uses the index instead of scanning the table:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

You'll see something like Index Scan using idx_users_email in the query plan — a good sign.

Composite Indexes: Indexing Multiple Columns

A composite (multi-column) index covers more than one column in a single index structure. This is where index design gets interesting.

-- A composite index for order lookups by customer and date
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

The critical rule: the leftmost prefix principle.

The database can only use a composite index if the query filters on the leftmost column(s) in order. Think of it like a phone book sorted by last name, then first name: you can find everyone named "Smith", or find "John Smith" — but you can't efficiently search for everyone named "John" without scanning the whole book.

-- ✅ Uses idx_orders_customer_date (filters on customer_id)
SELECT * FROM orders WHERE customer_id = 42;

-- ✅ Uses idx_orders_customer_date (filters on both columns in order)
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= '2025-01-01';

-- ❌ Cannot use the index (skips customer_id, goes straight to created_at)
SELECT * FROM orders WHERE created_at >= '2025-01-01';

For the last query, you'd need a separate index on created_at alone.

General rule: Put equality columns first, range columns last.

-- Good: filter by status (equality) first, then date range
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- A query that benefits
SELECT * FROM orders WHERE status = 'pending' AND created_at >= NOW() - INTERVAL '7 days';

Covering Indexes: Eliminating Table Lookups

Even after finding the matching rows in an index, the database usually has to go back to the main table to fetch the columns you actually need. This is called a heap lookup (or table lookup). For very high-frequency queries, you can eliminate this extra step with a covering index — an index that contains all the columns your query needs.

-- Without covering index: finds row IDs in index, then fetches full rows from table
SELECT email, full_name FROM users WHERE status = 'active';

-- Create a covering index that includes the SELECT columns
CREATE INDEX idx_users_status_covering ON users (status) INCLUDE (email, full_name);
-- (INCLUDE available in PostgreSQL 11+, SQL Server 2005+)

With this index, the database can answer the entire query from the index alone — never touching the main table. This is called an index-only scan and is extremely fast for read-heavy queries.

You can verify it worked with:

EXPLAIN ANALYZE SELECT email, full_name FROM users WHERE status = 'active';
-- Look for "Index Only Scan" in the output

Partial Indexes: Index Only What You Query

If you only ever query a subset of rows, a partial index (PostgreSQL) lets you index just that subset:

-- Only index orders that are still pending (the ones you actually look up)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

This index is much smaller and faster to maintain than a full index on created_at. It's perfect for tables where most queries target a minority of rows — think "active users", "unprocessed jobs", or "open tickets".

Common Indexing Mistakes

1. Over-Indexing

Every index speeds up reads but slows down writes. When you INSERT, UPDATE, or DELETE a row, the database must update every index on that table. A table with 12 indexes on a write-heavy workload is going to hurt.

-- Anti-pattern: indexes on every column "just in case"
CREATE INDEX idx_users_first_name ON users (first_name);
CREATE INDEX idx_users_last_name  ON users (last_name);
CREATE INDEX idx_users_phone      ON users (phone);
CREATE INDEX idx_users_created_at ON users (created_at);
-- ...and five more you never use

-- Better: only index columns that appear in WHERE, JOIN, or ORDER BY
-- for high-traffic queries

Audit your indexes regularly. Most databases let you check whether an index is actually being used.

2. LIKE Wildcards Defeat Indexes

A trailing wildcard (LIKE 'Smi%') can use an index. A leading wildcard (LIKE '%smith') cannot — it forces a full scan because the index is sorted by the start of the string, not the end.

-- ✅ Can use an index on last_name
WHERE last_name LIKE 'Smi%'

-- ❌ Forces a full scan — index is useless here
WHERE last_name LIKE '%smith'

For full-text searching within strings, consider a full-text index or a tool like Elasticsearch instead.

3. Functions on Indexed Columns Kill the Index

Wrapping an indexed column in a function prevents the optimizer from using the index:

-- ❌ The function LOWER() breaks index usage on email
WHERE LOWER(email) = 'alice@example.com'

-- ✅ Option 1: Normalize data at insert time (store email already lowercased)
-- ✅ Option 2: Create a functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
WHERE LOWER(email) = 'alice@example.com'  -- now uses the functional index

The same applies to DATE(timestamp_col), YEAR(date_col), and similar functions.

4. Low-Cardinality Columns

A column like is_active (only TRUE or FALSE) is a poor candidate for a B-tree index — the optimizer may decide a full scan is faster because the index doesn't narrow things down much. Partial indexes (as shown above) are a better choice for low-cardinality filtering.

How to Check If Your Index Is Being Used

Always verify with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT order_id, total_amount
FROM orders
WHERE customer_id = 42
  AND created_at >= '2025-01-01';

Look for these keywords in the output:

Output keyword	Meaning	Good?
Index Scan	Found rows via index, then fetched from table	✅
Index Only Scan	Answered entirely from the index (covering index)	✅✅
Bitmap Index Scan	Built a bitmap of matching rows from index	✅
Seq Scan	Full table scan — index not used	⚠️ investigate

If you see a Seq Scan on a large table and you have an index, something is wrong: the query may not match the index's column order, a function may be wrapping the column, or the table might be too small for the optimizer to bother.

Key Takeaways

Indexing well is one of the highest-leverage skills in SQL. Here's the short version:

B-tree indexes are the default and work for equality, range, prefix, and sort queries
Composite indexes follow the leftmost prefix rule — equality columns first, range columns last
Covering indexes (with INCLUDE) eliminate heap lookups for read-heavy queries
Partial indexes keep things lean when you only query a known subset of rows
Every index has a write overhead — index strategically, not speculatively
Functions on indexed columns break index usage — use functional indexes or normalize data at write time
Always verify with EXPLAIN ANALYZE — never assume an index is being used

A well-indexed table can handle orders of magnitude more load without any code changes. And once you start reading EXPLAIN ANALYZE output comfortably, you'll have a superpower that most developers don't.

Which indexing trick has saved you the most query time? Drop a comment — I'd love to hear about real production wins (or the horror stories that led to them). If this was useful, share it with a colleague who's still hitting query timeouts!

Write Your SQL Like a Pro: Mastering Common Table Expressions (CTEs)

Vivek Kumar — Fri, 10 Apr 2026 17:50:13 +0000

Write Your SQL Like a Pro: Mastering Common Table Expressions (CTEs)

If you've ever stared at a deeply nested SQL query and wondered who wrote that monster — chances are it could have been written much more cleanly with a CTE.

Common Table Expressions (CTEs) are one of the most powerful readability and maintainability tools in SQL. They let you break a complex query into named, logical building blocks that read almost like a story. Once you start using them, you'll wonder how you ever got along without them.

In this guide, you'll learn what CTEs are, how to write them, how to chain multiple CTEs together, and some real-world scenarios where they shine.

What Is a CTE?

A CTE — defined with the WITH keyword — is a temporary named result set that exists only for the duration of a single query. Think of it like a temporary view you define at the top of your query and then reference below.

Basic syntax:

WITH cte_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name;

That's it. You define the CTE inside parentheses after AS, name it, and then use it in the main query below like any regular table.

Your First CTE: A Simple Example

Say you have an orders table and you want to find all customers who placed more than 5 orders. You could write this as a subquery:

-- Subquery approach (harder to read)
SELECT customer_id, order_count
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS order_summary
WHERE order_count > 5;

Here's the same logic as a CTE:

-- CTE approach (much easier to read)
WITH order_summary AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, order_count
FROM order_summary
WHERE order_count > 5;

Both queries return the same result, but the CTE version makes the intent clear immediately. The named block order_summary tells you exactly what it contains before you even read it.

Chaining Multiple CTEs

Here's where CTEs get really powerful: you can define multiple CTEs in a single query, each building on the previous. Separate them with commas.

Let's say you're working with an e-commerce database and you want to find the top 10 customers by revenue, but only count orders that were actually delivered (not cancelled or refunded).

Tables:

orders(order_id, customer_id, status, created_at)
order_items(order_id, product_id, quantity, unit_price)
customers(customer_id, name, email, country)

WITH 
-- Step 1: Only look at delivered orders
delivered_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE status = 'delivered'
),

-- Step 2: Calculate revenue per order
order_revenue AS (
    SELECT 
        do.customer_id,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM delivered_orders do
    JOIN order_items oi ON oi.order_id = do.order_id
    GROUP BY do.customer_id
),

-- Step 3: Rank customers by total revenue
ranked_customers AS (
    SELECT 
        or.customer_id,
        or.revenue,
        RANK() OVER (ORDER BY or.revenue DESC) AS revenue_rank
    FROM order_revenue or
)

-- Final query: Get the top 10
SELECT 
    c.name,
    c.email,
    rc.revenue,
    rc.revenue_rank
FROM ranked_customers rc
JOIN customers c ON c.customer_id = rc.customer_id
WHERE rc.revenue_rank <= 10
ORDER BY rc.revenue_rank;

Notice how each CTE has one clear job. delivered_orders filters, order_revenue aggregates, ranked_customers ranks. Reading this query top to bottom tells the story of what you're doing — no nested parentheses archaeology required.

Using CTEs with UPDATE and DELETE

CTEs aren't just for SELECT queries. You can use them with UPDATE and DELETE too, which is incredibly handy for complex conditional modifications.

Example: Archive orders older than 2 years

WITH old_orders AS (
    SELECT order_id
    FROM orders
    WHERE created_at < NOW() - INTERVAL '2 years'
      AND status IN ('delivered', 'cancelled')
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM old_orders);

Or with UPDATE — say you want to flag high-value customers:

WITH high_value_customers AS (
    SELECT customer_id
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 10000
)
UPDATE customers
SET tier = 'platinum'
WHERE customer_id IN (SELECT customer_id FROM high_value_customers);

Real-World Use Case: Running Totals

CTEs pair beautifully with window functions. Here's a classic example — a running total of daily sales:

WITH daily_sales AS (
    SELECT 
        DATE(created_at) AS sale_date,
        SUM(total_amount) AS daily_total
    FROM orders
    WHERE status = 'delivered'
    GROUP BY DATE(created_at)
)
SELECT 
    sale_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
ORDER BY sale_date;

Sample output:

sale_date	daily_total	running_total
2026-01-01	4200.00	4200.00
2026-01-02	3800.00	8000.00
2026-01-03	5100.00	13100.00

CTEs vs. Subqueries: When to Use Which?

Feature	CTE	Subquery
Readability	✅ Much easier for complex logic	❌ Can get deeply nested
Reusability within query	✅ Reference multiple times	❌ Must repeat the query
Recursive queries	✅ Supported	❌ Not supported
Performance	Roughly equivalent	Roughly equivalent
Short, simple filters	🤷 Overkill	✅ Fine for one-liners

The general rule: if your subquery is more than 5–6 lines, or you need to reference it more than once, use a CTE.

Common Mistakes to Avoid

1. Trying to reference a CTE outside its query

CTEs only live within the single statement they're defined in. You cannot define a CTE in one query and use it in another.

-- ❌ This does NOT work
WITH my_cte AS (SELECT * FROM products)

-- ... some other SQL here ...

SELECT * FROM my_cte; -- ERROR: my_cte doesn't exist here

2. Forgetting the comma between multiple CTEs

-- ❌ Missing comma — will throw a syntax error
WITH cte_one AS (
    SELECT ...
)
cte_two AS (   -- needs a comma after the closing parenthesis above
    SELECT ...
)
SELECT * FROM cte_two;

-- ✅ Correct
WITH cte_one AS (
    SELECT ...
),
cte_two AS (
    SELECT ...
)
SELECT * FROM cte_two;

3. Overusing CTEs for simple queries

CTEs add clarity, not magic. For a simple single-table SELECT, a CTE just adds boilerplate. Use them when they actually help readability.

Key Takeaways

CTEs are defined with the WITH keyword and give you a named, temporary result set
They dramatically improve readability for complex queries
You can chain multiple CTEs in a single query, each building on the last
CTEs work with SELECT, INSERT, UPDATE, and DELETE
They're the natural home for queries you'd otherwise write as deeply nested subqueries
Performance is roughly equivalent to subqueries in most modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.)

What's Next?

Once you're comfortable with regular CTEs, the next step is recursive CTEs — which allow a CTE to call itself, making them perfect for traversing hierarchical data like org charts, category trees, or folder structures. That's a topic for another day, but knowing that CTEs can be recursive shows just how powerful this feature really is.

Have you been using CTEs in your projects? Drop a comment below — I'd love to hear about any creative use cases you've found. And if this cleared up any confusion, share it with a teammate who's still writing those 10-level-deep subqueries! 😅

SQL CASE Expressions: Write Smarter Queries with Conditional Logic

Vivek Kumar — Fri, 10 Apr 2026 17:44:55 +0000

SQL CASE expressions are one of those features that seem simple at first glance — "just an if/else in SQL, right?" — but once you start using them beyond the basics, you realize they're one of the most versatile tools in your query-writing arsenal. Whether you're bucketing numeric data, computing dynamic labels, pivoting rows into columns, or embedding business logic directly into your SELECT, CASE does the heavy lifting without any application-layer gymnastics.

In this guide, we'll go from the fundamentals to genuinely useful patterns you can drop into real production queries today.

The Two Flavors: Simple vs. Searched CASE

Before jumping into examples, it's worth understanding that CASE comes in two syntactic forms.

Simple CASE

A simple CASE compares one expression against a list of values:

SELECT
  order_id,
  status,
  CASE status
    WHEN 'pending'    THEN 'Awaiting Payment'
    WHEN 'confirmed'  THEN 'Processing'
    WHEN 'shipped'    THEN 'On Its Way'
    WHEN 'delivered'  THEN 'Complete'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Clean and readable when you're mapping discrete values. The ELSE clause is your safety net for anything not explicitly listed — skip it and SQL returns NULL for unmatched rows (which is often a silent bug).

Searched CASE

A searched CASE evaluates a boolean condition per branch — much more flexible:

SELECT
  product_name,
  price,
  CASE
    WHEN price < 10.00  THEN 'Budget'
    WHEN price < 50.00  THEN 'Mid-Range'
    WHEN price < 200.00 THEN 'Premium'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

Each WHEN clause is evaluated top-to-bottom and SQL stops at the first match. This matters: if a product costs $8, it hits 'Budget' and the rest are never checked. Structure your conditions from most specific to least specific (or lowest to highest) to avoid unexpected results.

Real-World Pattern 1: Data Bucketing in Reports

Say you run an e-commerce platform and want a quick breakdown of customer lifetime value:

SELECT
  customer_id,
  total_spent,
  CASE
    WHEN total_spent >= 1000 THEN 'VIP'
    WHEN total_spent >= 500  THEN 'Gold'
    WHEN total_spent >= 100  THEN 'Silver'
    ELSE 'Standard'
  END AS customer_tier,
  CASE
    WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
    WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'At Risk'
    ELSE 'Churned'
  END AS engagement_status
FROM customers;

Two CASE expressions in the same SELECT, each adding a different dimension of classification. This logic lives in the database layer, which means your application code stays clean and your BI tool can use it directly.

Real-World Pattern 2: Conditional Aggregation (This One Is Gold)

This is where CASE earns its reputation among intermediate SQL developers. By combining CASE with aggregate functions like SUM and COUNT, you can pivot row-level data into columnar summaries — without a separate PIVOT clause.

Suppose you have a sales table and you want monthly totals broken out by region in a single row per month:

SELECT
  DATE_TRUNC('month', sale_date) AS month,
  SUM(CASE WHEN region = 'North America' THEN revenue ELSE 0 END) AS na_revenue,
  SUM(CASE WHEN region = 'Europe'        THEN revenue ELSE 0 END) AS eu_revenue,
  SUM(CASE WHEN region = 'Asia Pacific'  THEN revenue ELSE 0 END) AS apac_revenue,
  COUNT(CASE WHEN region = 'North America' AND revenue > 10000 THEN 1 END) AS na_big_deals
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;

Notice the COUNT trick at the end: when the condition is false, CASE returns NULL, and COUNT ignores NULLs — so you're effectively counting only the rows that match. No subquery, no CTE, just clean conditional aggregation.

The result looks something like this:

month	na_revenue	eu_revenue	apac_revenue	na_big_deals
2026-01-01	142,500	98,300	67,200	8
2026-02-01	165,700	110,400	72,100	11
2026-03-01	158,200	104,800	81,500	9

Real-World Pattern 3: CASE in ORDER BY for Custom Sort Logic

Standard ORDER BY sorts numerically or alphabetically. What if your business has a specific priority order that doesn't follow those rules?

SELECT
  ticket_id,
  title,
  priority,
  created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY
  CASE priority
    WHEN 'critical'  THEN 1
    WHEN 'high'      THEN 2
    WHEN 'medium'    THEN 3
    WHEN 'low'       THEN 4
    ELSE 5
  END ASC,
  created_at ASC;

Critical tickets bubble to the top, then within the same priority, older tickets come first. This is much cleaner than trying to encode order into the priority column itself, and it's completely transparent to anyone reading the query.

Real-World Pattern 4: CASE in WHERE Clauses (Dynamic Filters)

You can even use CASE inside a WHERE clause for conditional filtering logic — useful when you're writing parameterized queries or stored procedures:

-- Show all orders, but if a user_id is provided, filter to that user only
SELECT
  order_id,
  user_id,
  total,
  created_at
FROM orders
WHERE
  CASE
    WHEN :filter_user_id IS NOT NULL THEN user_id = :filter_user_id
    ELSE TRUE
  END;

Note: Syntax varies by database. In PostgreSQL and most modern databases this pattern works well. In MySQL you might use (:filter_user_id IS NULL OR user_id = :filter_user_id) instead, which is actually more readable — but CASE in WHERE is a valid approach in procedural SQL contexts.

Common Mistakes & Gotchas

1. Forgetting that ELSE defaults to NULL

-- If status is anything besides 'active' or 'inactive', this returns NULL
CASE status
  WHEN 'active'   THEN 'Enabled'
  WHEN 'inactive' THEN 'Disabled'
END

If NULL showing up in your results or aggregates is not what you want, always add an explicit ELSE clause.

2. Type mismatches across branches

All THEN branches must return compatible data types. Mixing 'Yes' (text) with 1 (integer) in different branches will cause an error in strict databases like PostgreSQL. Be consistent:

-- Bad: mixed types
CASE WHEN is_premium = true THEN 'yes' ELSE 0 END

-- Good: consistent types
CASE WHEN is_premium = true THEN 'yes' ELSE 'no' END

3. Using CASE when COALESCE or IIF is cleaner

For simple null-handling, COALESCE is much more readable:

-- Verbose
CASE WHEN nickname IS NULL THEN first_name ELSE nickname END

-- Better
COALESCE(nickname, first_name)

Save CASE for situations that actually need multiple conditions or more complex logic.

4. Short-circuit evaluation is NOT guaranteed in all databases

While most databases evaluate CASE branches top-to-bottom and stop at the first match, do not rely on CASE to prevent division-by-zero errors in a way that assumes strict short-circuiting. Some query optimizers may reorder operations. Use explicit guards:

-- Safer approach for division
CASE
  WHEN total_sessions > 0 THEN conversions::float / total_sessions
  ELSE NULL
END AS conversion_rate

CASE Inside Window Functions

One of the more powerful combinations: using CASE inside a window function to do conditional running totals or ranks:

SELECT
  order_id,
  customer_id,
  revenue,
  SUM(CASE WHEN is_refund = false THEN revenue ELSE 0 END)
    OVER (PARTITION BY customer_id ORDER BY order_date) AS running_net_revenue
FROM orders;

This gives you a per-customer running total of net revenue (excluding refunds), all in a single pass over the data.

Key Takeaways

Simple CASE maps one expression to a set of values; Searched CASE evaluates arbitrary boolean conditions — use whichever fits the situation.
Always include an ELSE clause unless you intentionally want unmatched rows to return NULL.
Conditional aggregation (SUM(CASE WHEN ... THEN value ELSE 0 END)) is one of the most practical CASE patterns in real analytics work.
You can use CASE in SELECT, WHERE, ORDER BY, HAVING, and inside aggregate/window functions.
Keep branches type-consistent to avoid runtime errors.
Don't overuse CASE — COALESCE, NULLIF, and IIF (SQL Server) handle simpler scenarios more cleanly.

CASE expressions sit at the intersection of SQL's set-based thinking and the conditional logic developers are already comfortable with from application code. Master them and you'll find yourself writing fewer subqueries, doing less post-processing in your application layer, and generally producing queries that other engineers can read and maintain without a translation guide.

Have a favorite CASE trick I didn't cover? Drop it in the comments — I'd love to see how others are using conditional logic in their queries!

SQL Aggregate Functions: Stop Guessing, Start Calculating

Vivek Kumar — Thu, 09 Apr 2026 18:20:27 +0000

SQL Aggregate Functions: Stop Guessing, Start Calculating

If you've ever needed to answer questions like "How many orders did we get this month?", "What's our total revenue?", or "Who's our highest-paid employee?" — you need SQL aggregate functions. They're the workhorses of data analysis, and once you understand them, you'll wonder how you ever got by without them.

In this guide, we'll cover the five essential SQL aggregate functions — COUNT, SUM, AVG, MIN, and MAX — with realistic examples you can actually use in your projects.

What Are Aggregate Functions?

Aggregate functions take a group of rows and return a single value. Instead of seeing one result per row, you get a summary. Think of them as the SQL equivalent of a calculator that works across your entire dataset.

We'll use a sample orders table throughout this article:

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    product_name VARCHAR(100),
    quantity     INT,
    unit_price   DECIMAL(10, 2),
    order_date   DATE,
    status       VARCHAR(20)
);

And some sample data to work with:

INSERT INTO orders (order_id, customer_id, product_name, quantity, unit_price, order_date, status) VALUES
(1,  101, 'Wireless Headphones', 2,  79.99,  '2024-03-01', 'completed'),
(2,  102, 'USB-C Hub',           1,  49.99,  '2024-03-02', 'completed'),
(3,  101, 'Mechanical Keyboard', 1,  129.99, '2024-03-05', 'completed'),
(4,  103, 'Webcam HD',           3,  59.99,  '2024-03-07', 'pending'),
(5,  104, 'Monitor Stand',       1,  39.99,  '2024-03-10', 'completed'),
(6,  102, 'Wireless Mouse',      2,  34.99,  '2024-03-12', 'cancelled'),
(7,  105, 'Laptop Sleeve',       4,  24.99,  '2024-03-15', 'completed'),
(8,  103, 'HDMI Cable',          2,  14.99,  '2024-03-18', 'completed'),
(9,  106, 'Desk Lamp',           1,  44.99,  '2024-03-20', 'pending'),
(10, 101, 'Cable Organizer',     5,   9.99,  '2024-03-22', 'completed');

1. COUNT — How Many Rows Are There?

COUNT answers "how many?" It's arguably the most-used aggregate function in SQL.

Count all rows

SELECT COUNT(*) AS total_orders
FROM orders;

Result:

total_orders
10

Count non-NULL values in a column

COUNT(column_name) skips NULL values, while COUNT(*) counts every row.

SELECT COUNT(order_id) AS orders_with_id
FROM orders;

Count with a filter

How many orders were completed?

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed';

Result:

completed_orders
7

Count distinct values

How many unique customers placed orders?

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result:

unique_customers
6

2. SUM — Add It All Up

SUM totals up numeric values. Perfect for revenue calculations, quantity totals, and similar tasks.

Total quantity sold

SELECT SUM(quantity) AS total_items_sold
FROM orders;

Result:

total_items_sold
22

Total revenue from completed orders

Here we calculate revenue by multiplying quantity × unit_price for each row, then summing it all:

SELECT SUM(quantity * unit_price) AS total_revenue
FROM orders
WHERE status = 'completed';

Result:

total_revenue
704.81

Revenue per customer

This is where SUM gets really powerful when combined with GROUP BY:

SELECT 
    customer_id,
    SUM(quantity * unit_price) AS customer_revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY customer_revenue DESC;

Result:

customer_id	customer_revenue
101	369.95
105	99.96
103	29.98
104	39.99
102	49.99

Customer 101 is clearly your best customer!

3. AVG — Find the Average

AVG calculates the mean of a set of values. Great for understanding typical behavior.

Average order value

SELECT AVG(unit_price) AS avg_product_price
FROM orders;

Result:

avg_product_price
48.99

Average items per order

SELECT AVG(quantity) AS avg_items_per_order
FROM orders;

Result:

avg_items_per_order
2.2000

Average revenue per completed order

SELECT ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
FROM orders
WHERE status = 'completed';

Result:

avg_order_value
100.69

Pro tip: AVG ignores NULL values automatically. If a column has NULLs, they won't drag down your average — but be aware of this behavior to avoid surprises.

4. MIN — Find the Smallest Value

MIN returns the lowest value in a column. Works with numbers, dates, and even strings.

Cheapest product

SELECT MIN(unit_price) AS cheapest_product_price
FROM orders;

Result:

cheapest_product_price
9.99

Earliest order date

SELECT MIN(order_date) AS first_order_date
FROM orders;

Result:

first_order_date
2024-03-01

First order per customer

SELECT 
    customer_id,
    MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id;

Result:

customer_id	first_order_date
101	2024-03-01
102	2024-03-02
103	2024-03-07
104	2024-03-10
105	2024-03-15
106	2024-03-20

5. MAX — Find the Largest Value

MAX is the mirror image of MIN — it returns the highest value.

Most expensive product

SELECT MAX(unit_price) AS most_expensive_product
FROM orders;

Result:

most_expensive_product
129.99

Most recent order date

SELECT MAX(order_date) AS last_order_date
FROM orders;

Result:

last_order_date
2024-03-22

Biggest single order value per customer

SELECT 
    customer_id,
    MAX(quantity * unit_price) AS biggest_order_value
FROM orders
GROUP BY customer_id
ORDER BY biggest_order_value DESC;

Combining Multiple Aggregates

The real magic happens when you use several aggregate functions together in one query. Here's a full sales summary report:

SELECT 
    COUNT(*)                              AS total_orders,
    COUNT(DISTINCT customer_id)           AS unique_customers,
    SUM(quantity)                         AS total_items_sold,
    ROUND(SUM(quantity * unit_price), 2)  AS gross_revenue,
    ROUND(AVG(quantity * unit_price), 2)  AS avg_order_value,
    MIN(unit_price)                       AS cheapest_item,
    MAX(unit_price)                       AS priciest_item
FROM orders
WHERE status = 'completed';

Result:

total_orders	unique_customers	total_items_sold	gross_revenue	avg_order_value	cheapest_item	priciest_item
7	5	15	704.81	100.69	9.99	129.99

One query, a full business snapshot. That's the power of aggregate functions.

Common Mistakes to Avoid

1. Forgetting to use GROUP BY when selecting non-aggregated columns

This will throw an error in most databases:

-- ❌ Wrong — product_name isn't aggregated or grouped
SELECT product_name, SUM(quantity)
FROM orders;

-- ✅ Correct
SELECT product_name, SUM(quantity) AS total_sold
FROM orders
GROUP BY product_name;

2. Using WHERE instead of HAVING to filter aggregated results

WHERE filters rows before aggregation. HAVING filters after. If you want to filter based on an aggregate result, you need HAVING:

-- ❌ Wrong — can't use SUM() in WHERE
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE SUM(quantity * unit_price) > 100
GROUP BY customer_id;

-- ✅ Correct
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(quantity * unit_price) > 100;

3. Confusing COUNT(*) and COUNT(column)

COUNT(*) counts all rows including NULLs. COUNT(column) skips NULLs in that column. Choose wisely depending on what you actually want to count.

4. Not accounting for NULL in AVG

If some rows have NULL in the column you're averaging, those rows are excluded from both the sum and the count. This can make your average higher than expected.

-- Example: if 3 out of 10 rows have NULL discount, AVG only divides by 7
SELECT AVG(discount_amount) FROM orders;

Key Takeaways

COUNT — Count rows or distinct values. Use COUNT(*) for all rows, COUNT(col) to skip NULLs.
SUM — Add up numeric values. Combine with GROUP BY for per-group totals.
AVG — Calculate the mean. Remember it ignores NULLs.
MIN / MAX — Find the smallest or largest value. Works on numbers, dates, and strings.
Combine multiple aggregates in a single query for powerful summary reports.
Use HAVING (not WHERE) to filter results based on aggregate values.

These five functions are the foundation of nearly every analytical SQL query you'll ever write. Master them, and you'll be answering data questions that used to take spreadsheets and manual work — all with a few clean lines of SQL.

Have a question about aggregate functions, or a favorite use case I didn't cover? Drop it in the comments — I'd love to hear how you're using these in your own projects!

SQL WHERE Clause: The Complete Guide to Filtering Data

Vivek Kumar — Thu, 09 Apr 2026 18:07:05 +0000

SQL WHERE Clause: The Complete Guide to Filtering Data

Imagine you have a database with a million customers, but you only need the ones in New York who signed up last year. Without the WHERE clause, you'd be drowning in data you don't care about. With it, you can slice through your data with surgical precision.

The WHERE clause is one of the first things every SQL developer learns — and one of the most powerful tools you'll use every single day. In this guide, we'll go beyond the basics and cover everything you need to filter data confidently.

Setup: Our Sample Tables

We'll use two tables throughout this guide:

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    city          VARCHAR(50),
    age           INT,
    signup_date   DATE,
    is_active     BOOLEAN
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    product       VARCHAR(100),
    amount        DECIMAL(10,2),
    order_date    DATE,
    status        VARCHAR(20)
);

INSERT INTO customers VALUES
(1,  'Alice',   'Johnson', 'alice@email.com',   'New York',    32, '2022-03-15', true),
(2,  'Bob',     'Smith',   'bob@email.com',     'Chicago',     27, '2023-07-01', true),
(3,  'Carol',   'Davis',   'carol@email.com',   'New York',    45, '2021-11-20', false),
(4,  'David',   'Wilson',  'david@email.com',   'Los Angeles', 31, '2023-01-10', true),
(5,  'Eva',     'Martinez','eva@email.com',     'Chicago',     29, '2022-09-05', true),
(6,  'Frank',   'Brown',   'frank@email.com',   'New York',    52, '2020-06-12', false),
(7,  'Grace',   'Lee',     'grace@email.com',   'Houston',     24, '2023-11-03', true),
(8,  'Henry',   'Taylor',  'henry@email.com',   'Los Angeles', 38, '2022-04-22', true);

Basic Comparisons

The WHERE clause uses comparison operators to filter rows. Here are all the ones you'll regularly use:

Operator	Meaning
`=`	Equal to
`<>` or `!=`	Not equal to
`<`	Less than
`>`	Greater than
`<=`	Less than or equal to
`>=`	Greater than or equal to

Examples

Find all active customers:

SELECT first_name, last_name, city
FROM customers
WHERE is_active = true;

Result: Alice, Bob, David, Eva, Grace, Henry

Find customers aged 30 or older:

SELECT first_name, age, city
FROM customers
WHERE age >= 30;

Result:

first_name	age	city
Alice	32	New York
Carol	45	New York
David	31	Los Angeles
Frank	52	New York
Henry	38	Los Angeles

Combining Conditions: AND, OR, NOT

Real-world queries almost always involve multiple conditions.

AND — both conditions must be true

Active customers in New York:

SELECT first_name, last_name
FROM customers
WHERE city = 'New York'
  AND is_active = true;

Result: Alice Johnson

OR — at least one condition must be true

Customers in New York OR Chicago:

SELECT first_name, city
FROM customers
WHERE city = 'New York'
   OR city = 'Chicago';

Result: Alice (New York), Bob (Chicago), Carol (New York), Eva (Chicago), Frank (New York)

NOT — negate a condition

Customers NOT from New York:

SELECT first_name, city
FROM customers
WHERE NOT city = 'New York';

Combining AND and OR (use parentheses!)

Find active customers who are either from New York or under 30:

-- Without parentheses — this may not do what you expect!
SELECT first_name, age, city
FROM customers
WHERE is_active = true AND city = 'New York' OR age < 30;

-- With parentheses — clear and correct
SELECT first_name, age, city
FROM customers
WHERE is_active = true
  AND (city = 'New York' OR age < 30);

The second version is explicit: it finds active customers where either city is New York or age is under 30. AND binds tighter than OR in SQL, just like multiplication binds tighter than addition in math — parentheses make your intent unmistakable.

IN — Match Any Value in a List

Instead of chaining multiple OR conditions, use IN:

-- Verbose version
SELECT first_name, city
FROM customers
WHERE city = 'New York' OR city = 'Chicago' OR city = 'Houston';

-- Clean version with IN
SELECT first_name, city
FROM customers
WHERE city IN ('New York', 'Chicago', 'Houston');

Both return the same result, but IN is much cleaner. You can also negate it:

-- Customers NOT from these cities
SELECT first_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago');

Result: David (Los Angeles), Grace (Houston), Henry (Los Angeles)

BETWEEN — Range Filtering

BETWEEN checks if a value falls within an inclusive range. Works with numbers, dates, and strings.

Numeric range

Customers aged 25–35:

SELECT first_name, age
FROM customers
WHERE age BETWEEN 25 AND 35;

Result: Alice (32), Bob (27), David (31), Eva (29)

Note: BETWEEN x AND y is inclusive on both ends — equivalent to >= x AND <= y.

Date range

Customers who signed up in 2022:

SELECT first_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2022-01-01' AND '2022-12-31';

Result: Alice (2022-03-15), Eva (2022-09-05), Henry (2022-04-22)

IS NULL / IS NOT NULL

NULL in SQL means "unknown" or "missing data." You can't use = NULL to check for it — you must use IS NULL:

-- This NEVER works (always returns 0 rows)
SELECT * FROM customers WHERE email = NULL;

-- This is correct
SELECT * FROM customers WHERE email IS NULL;

Let's add a customer with a missing email to demonstrate:

INSERT INTO customers VALUES
(9, 'Ian', 'Clark', NULL, 'Boston', 33, '2023-05-10', true);

-- Find customers with no email on file
SELECT first_name, last_name
FROM customers
WHERE email IS NULL;

Result: Ian Clark

-- Find customers who DO have an email
SELECT COUNT(*) AS customers_with_email
FROM customers
WHERE email IS NOT NULL;

LIKE — Pattern Matching

LIKE lets you search for patterns in text using two wildcards:

% — matches any sequence of characters (including none)
_ — matches exactly one character

Find emails from Gmail:

SELECT first_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

Find customers whose first name starts with a vowel (A or E):

SELECT first_name
FROM customers
WHERE first_name LIKE 'A%'
   OR first_name LIKE 'E%';

Result: Alice, Eva

Find customers with a 5-letter first name:

SELECT first_name
FROM customers
WHERE first_name LIKE '_____';

Result: Alice, Grace, Henry, Frank (5 underscores = 5 characters)

Case sensitivity: LIKE is case-sensitive in PostgreSQL but case-insensitive in MySQL. In PostgreSQL, use ILIKE for case-insensitive matching.

Putting It All Together

Here's a real-world query combining multiple WHERE techniques — find active customers aged 25–40 who live in New York or Chicago, and whose email address we have on file:

SELECT 
    first_name,
    last_name,
    age,
    city,
    email
FROM customers
WHERE is_active = true
  AND age BETWEEN 25 AND 40
  AND city IN ('New York', 'Chicago')
  AND email IS NOT NULL
ORDER BY age ASC;

Result:

first_name	last_name	age	city	email
Bob	Smith	27	Chicago	bob@email.com
Eva	Martinez	29	Chicago	eva@email.com
Alice	Johnson	32	New York	alice@email.com

One clean query, perfectly targeted results.

Common Mistakes to Avoid

1. Using = NULL instead of IS NULL

-- ❌ Always returns zero rows
WHERE discount_code = NULL

-- ✅ Correct
WHERE discount_code IS NULL

2. Forgetting parentheses with AND/OR

-- ❌ Might not mean what you think
WHERE status = 'active' AND city = 'NY' OR city = 'LA'

-- ✅ Explicit and correct
WHERE status = 'active' AND (city = 'NY' OR city = 'LA')

3. Doing math on the filtered column (kills index performance)

-- ❌ Forces a full table scan — can't use an index on order_date
WHERE YEAR(order_date) = 2023

-- ✅ Uses index on order_date
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'

4. Using LIKE with a leading wildcard (also kills index performance)

-- ❌ Can't use index — scans every row
WHERE email LIKE '%@gmail.com'

-- ✅ Can use index — only scans rows starting with 'alice'
WHERE email LIKE 'alice%'

Key Takeaways

Use comparison operators (=, <>, <, >, etc.) for basic filtering.
Combine conditions with AND, OR, and NOT — use parentheses when mixing them.
IN is cleaner than multiple OR conditions for matching a list of values.
BETWEEN provides inclusive range filtering for numbers and dates.
Always use IS NULL or IS NOT NULL — never = NULL.
LIKE with % and _ enables pattern matching; avoid leading wildcards on large tables.

The WHERE clause is the foundation of meaningful SQL queries. Mastering it means you can always get exactly the data you need — no more, no less.

Got a tricky filtering scenario you've run into? Drop it in the comments — I'd love to help work through it!