Database Tutorial #3: PostgreSQL — Advanced Queries

Basic SELECT queries only get you so far. Real applications need joins, aggregations, and complex filtering. This tutorial covers the SQL features you will use every day.

We assume you have PostgreSQL running. See PostgreSQL Setup and Basics if you need to set that up first.

Sample Schema

Let’s use a simple e-commerce schema for all examples:

CREATE TABLE users (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  name       text NOT NULL,
  email      text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT NOW()
);

CREATE TABLE orders (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    uuid REFERENCES users(id),
  status     text NOT NULL DEFAULT 'pending',
  total      decimal(10, 2) NOT NULL,
  created_at timestamptz DEFAULT NOW()
);

CREATE TABLE order_items (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  order_id   uuid REFERENCES orders(id),
  product    text NOT NULL,
  quantity   integer NOT NULL,
  price      decimal(10, 2) NOT NULL
);

JOINs

JOINs combine rows from multiple tables based on a related column.

INNER JOIN

Returns only rows that have a match in both tables.

-- Get all orders with their user names
SELECT
  orders.id,
  users.name,
  orders.total,
  orders.status
FROM orders
INNER JOIN users ON orders.user_id = users.id;

If a user has no orders, they will not appear. If an order has no matching user (broken data), it will not appear either.

LEFT JOIN

Returns all rows from the left table. If there is no match in the right table, the right-side columns are NULL.

-- Get all users, with their order count (including users with no orders)
SELECT
  users.name,
  COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.name;

Users with no orders will show order_count = 0.

Multiple JOINs

-- Get order items with order info and user name
SELECT
  users.name,
  orders.id AS order_id,
  order_items.product,
  order_items.quantity,
  order_items.price
FROM order_items
JOIN orders ON order_items.order_id = orders.id
JOIN users ON orders.user_id = users.id
WHERE orders.status = 'completed';

FULL OUTER JOIN

Returns all rows from both tables. Useful for finding orphaned records.

-- Find orders without users, or users without orders
SELECT users.name, orders.id
FROM users
FULL OUTER JOIN orders ON orders.user_id = users.id
WHERE users.id IS NULL OR orders.id IS NULL;

GROUP BY and Aggregation

-- Total revenue by user
SELECT
  users.name,
  SUM(orders.total) AS total_spent,
  COUNT(orders.id) AS order_count
FROM users
JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.name
ORDER BY total_spent DESC;

HAVING — Filter Groups

WHERE filters individual rows. HAVING filters groups after aggregation.

-- Users who spent more than $500
SELECT
  users.name,
  SUM(orders.total) AS total_spent
FROM users
JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.name
HAVING SUM(orders.total) > 500
ORDER BY total_spent DESC;

CTEs (WITH Clause)

A CTE (Common Table Expression) is a named temporary result set. It makes complex queries easier to read.

-- Without CTE (hard to read)
SELECT name, total_spent
FROM (
  SELECT users.name, SUM(orders.total) AS total_spent
  FROM users
  JOIN orders ON orders.user_id = users.id
  GROUP BY users.id, users.name
) AS user_totals
WHERE total_spent > 100;

-- With CTE (easy to read)
WITH user_totals AS (
  SELECT users.name, SUM(orders.total) AS total_spent
  FROM users
  JOIN orders ON orders.user_id = users.id
  GROUP BY users.id, users.name
)
SELECT name, total_spent
FROM user_totals
WHERE total_spent > 100;

Chained CTEs

You can use multiple CTEs in one query:

WITH
  completed_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
  ),
  user_revenue AS (
    SELECT
      users.name,
      SUM(completed_orders.total) AS revenue
    FROM users
    JOIN completed_orders ON completed_orders.user_id = users.id
    GROUP BY users.id, users.name
  )
SELECT name, revenue
FROM user_revenue
WHERE revenue > 500
ORDER BY revenue DESC;

Window Functions

Window functions perform calculations across a set of rows related to the current row. Unlike GROUP BY, they do not collapse rows.

ROW_NUMBER

Assigns a unique number to each row within a partition.

-- Rank orders by total, per user
SELECT
  users.name,
  orders.total,
  ROW_NUMBER() OVER (PARTITION BY users.id ORDER BY orders.total DESC) AS rank
FROM orders
JOIN users ON orders.user_id = users.id;

Output:

name   | total  | rank
-------|--------|------
Alex   | 250.00 | 1
Alex   | 150.00 | 2
Alex   | 50.00  | 3
Sam    | 400.00 | 1
Sam    | 200.00 | 2

RANK and DENSE_RANK

RANK skips numbers on ties. DENSE_RANK does not skip.

SELECT
  product,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM order_items;

LAG and LEAD

Access the previous or next row’s value.

-- Compare each order's total to the previous order
SELECT
  orders.id,
  orders.total,
  LAG(orders.total) OVER (PARTITION BY orders.user_id ORDER BY orders.created_at) AS previous_total,
  orders.total - LAG(orders.total) OVER (PARTITION BY orders.user_id ORDER BY orders.created_at) AS change
FROM orders;

Running Total

-- Running total of revenue over time
SELECT
  created_at::date AS day,
  SUM(total) AS daily_revenue,
  SUM(SUM(total)) OVER (ORDER BY created_at::date) AS running_total
FROM orders
GROUP BY created_at::date
ORDER BY day;

DISTINCT ON

DISTINCT ON is a PostgreSQL-only feature. It returns one row per group, keeping the first row by the ORDER BY.

-- Get the latest order for each user
SELECT DISTINCT ON (user_id)
  user_id,
  id AS order_id,
  total,
  created_at
FROM orders
ORDER BY user_id, created_at DESC;

This is often faster and simpler than using ROW_NUMBER() = 1.

Subqueries

A subquery is a query inside another query.

-- Users who have placed at least one order
SELECT name, email
FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders
);

-- Users who have never ordered
SELECT name, email
FROM users
WHERE id NOT IN (
  SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
);

-- Same with NOT EXISTS (faster for large tables)
SELECT name, email
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Python Example

import psycopg

conn = psycopg.connect(
    "postgresql://admin:secret@localhost:5432/myapp"
)

# Get users with order totals using CTE
with conn.cursor() as cur:
    cur.execute("""
        WITH user_totals AS (
            SELECT
                users.id,
                users.name,
                COUNT(orders.id) AS order_count,
                COALESCE(SUM(orders.total), 0) AS total_spent
            FROM users
            LEFT JOIN orders ON orders.user_id = users.id
            GROUP BY users.id, users.name
        )
        SELECT name, order_count, total_spent
        FROM user_totals
        ORDER BY total_spent DESC
        LIMIT 10
    """)
    rows = cur.fetchall()
    for name, order_count, total_spent in rows:
        print(f"{name}: {order_count} orders, ${total_spent:.2f}")

conn.close()

TypeScript Example

import { Pool } from "pg";

const pool = new Pool({
  connectionString: "postgresql://admin:secret@localhost:5432/myapp",
});

// Get top users by revenue with window function
async function getTopUsersByRevenue(limit: number = 10) {
  const result = await pool.query(
    `
    SELECT
      users.name,
      SUM(orders.total) AS total_spent,
      RANK() OVER (ORDER BY SUM(orders.total) DESC) AS rank
    FROM users
    JOIN orders ON orders.user_id = users.id
    GROUP BY users.id, users.name
    ORDER BY total_spent DESC
    LIMIT $1
    `,
    [limit]
  );
  return result.rows;
}

// Get latest order per user
async function getLatestOrderPerUser() {
  const result = await pool.query(`
    SELECT DISTINCT ON (user_id)
      users.name,
      orders.total,
      orders.status,
      orders.created_at
    FROM orders
    JOIN users ON orders.user_id = users.id
    ORDER BY user_id, orders.created_at DESC
  `);
  return result.rows;
}

async function main() {
  const top = await getTopUsersByRevenue(5);
  console.log("Top users:", top);

  const latest = await getLatestOrderPerUser();
  console.log("Latest orders:", latest);

  await pool.end();
}

main();

Common Mistakes

1. SELECT * with JOINs. When two tables both have a column called id, SELECT * returns both. Be explicit: SELECT users.id, orders.id.

2. Forgetting NULL in NOT IN. If a subquery returns any NULL, NOT IN always returns nothing. Use NOT EXISTS instead.

3. Grouping by the wrong columns. Every column in SELECT must either be in GROUP BY or inside an aggregate function (SUM, COUNT, etc.).

4. Using HAVING when WHERE is enough. WHERE filters before grouping (faster). HAVING filters after grouping. Use WHERE whenever possible.

What We Learned

INNER JOIN — matching rows only
LEFT JOIN — all left rows, NULL if no match
GROUP BY + HAVING — aggregate and filter groups
CTEs (WITH) — named subqueries for readable complex queries
Window functions — ROW_NUMBER, RANK, LAG, LEAD, running totals
DISTINCT ON — get first row per group
Subqueries with IN and NOT EXISTS

What’s Next?

In the next tutorial, we learn about indexing — how to make your queries 100x faster with B-tree, GIN, and GiST indexes.

Sample Schema#

JOINs#

INNER JOIN#

LEFT JOIN#

Multiple JOINs#

FULL OUTER JOIN#

GROUP BY and Aggregation#

HAVING — Filter Groups#

CTEs (WITH Clause)#

Chained CTEs#

Window Functions#

ROW_NUMBER#

RANK and DENSE_RANK#

LAG and LEAD#

Running Total#

DISTINCT ON#

Subqueries#

Python Example#

TypeScript Example#

Common Mistakes#

What We Learned#

Related Articles#

What’s Next?#