What PostgreSQL queries should a data analyst know? Start with the patterns that select and filter records, then use joins and aggregation to combine and summarize them, and finish with CASE, window functions, and common table expressions for richer analysis. These nine patterns form a practical learning sequence, not an official or exhaustive PostgreSQL list.
The examples use PostgreSQL 17 and one small shop schema. They are illustrative SQL for those tables; PGExercises provides its own dataset and exercises, so do not assume these custom examples run there unchanged.
Example schema: customers, orders, and order_items
Assume these PostgreSQL tables and column types:
customers(customer_id integer, customer_name text, region text)orders(order_id integer, customer_id integer, order_date date, status text)order_items(order_item_id integer, order_id integer, product_name text, quantity integer, unit_price numeric)
Each order belongs to a customer, and an order can have multiple order items. The examples use quantity * unit_price as an item revenue calculation; they do not account for discounts, taxes, returns, or shipping.
1. Choose the output columns with SELECT
Suppose you need a customer list for a regional analysis. This returns one row per customer, with only the identifier, name, and region needed for that task.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
SELECT customer_id, customer_name, region
FROM customers;In PostgreSQL, a SELECT query retrieves rows from tables or views. The selected expressions define the result’s columns; naming only what you need makes the output easier to inspect and use than an indiscriminate SELECT *. See the PostgreSQL 17 SELECT reference.
2. Filter rows with WHERE
To inspect orders created during January 2026, filter the input rows before any grouping. Because order_date is a date, this half-open range includes January 1 through January 31 without needing a time-of-day boundary.
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE order_date >= DATE '2026-01-01'
AND order_date < DATE '2026-02-01';The result contains only matching orders. Explicit date literals make the intended type clear. For timestamp columns, a half-open range is also useful: use a start inclusive and next-period start exclusive, rather than relying on an assumed final instant.
3. Sort results and limit a preview
To preview the ten most recent orders, request an explicit ordering and limit the output. The second sort key makes the order deterministic when multiple records share a date.
SELECT order_id, customer_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 10;This returns at most ten rows. Without ORDER BY, the database does not promise a particular result order. If ranking by a value that can tie, add a unique tie-breaker when you need a stable list. PostgreSQL’s SELECT syntax includes both ORDER BY and LIMIT.
4. Join related tables
To list each order alongside its customer name, match the customer identifier in both tables. An INNER JOIN returns only rows with a match on both sides.
SELECT o.order_id, o.order_date, c.customer_name
FROM orders AS o
INNER JOIN customers AS c
ON c.customer_id = o.customer_id;Use a LEFT JOIN when the analysis must retain every row from the left-hand table even when no matching record exists on the right. For example, it can keep customers with no orders:
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id;A join’s type controls whether unmatched rows are kept. Also watch relationship cardinality: joining one customer to several orders, or one order to several items, repeats the parent data across result rows. Summing a customer-level value after such a join can inflate it unless the aggregation is designed for that grain. PostgreSQL explains join behavior in its table expressions documentation.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
5. Group rows with GROUP BY
To calculate recognized item revenue by customer from completed orders, join orders to their items and group the result. The output grain is one row per customer with at least one matching completed order.
SELECT o.customer_id,
SUM(oi.quantity * oi.unit_price) AS item_revenue
FROM orders AS o
JOIN order_items AS oi
ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.customer_id;SUM adds the item-level calculated values within each customer group. Grouping changes the grain from individual order items to one result row per customer; customers without matching completed items do not appear in this query.
6. Filter groups with HAVING
To keep only customers whose completed-order item revenue exceeds 500, first filter input orders in WHERE, then remove groups that fail the aggregate condition in HAVING.
SELECT o.customer_id,
SUM(oi.quantity * oi.unit_price) AS item_revenue
FROM orders AS o
JOIN order_items AS oi
ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.customer_id
HAVING SUM(oi.quantity * oi.unit_price) > 500;WHERE applies to rows before grouping; HAVING applies to the groups produced by GROUP BY. Use WHERE for conditions on individual input records and HAVING for conditions involving group aggregates, as described in PostgreSQL’s table expressions documentation.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitches7. Label values with CASE
To classify each order by status, CASE maps mutually exclusive conditions to readable labels. The ELSE branch gives an explicit label to any status not listed.
SELECT order_id,
status,
CASE
WHEN status = 'completed' THEN 'Complete'
WHEN status = 'cancelled' THEN 'Cancelled'
ELSE 'Other or pending'
END AS status_group
FROM orders;The query returns every order with its original status and a derived status group. Conditions are tested in order, so if conditions overlap, the first matching branch supplies the result.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.8. Compare rows with a window function
To rank each customer’s orders from newest to oldest while retaining every order row, use ROW_NUMBER partitioned by customer. The order identifier breaks date ties so the numbering is stable for a given dataset.
SELECT customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS order_recency
FROM orders;Unlike GROUP BY, this window calculation leaves the output at one row per order while computing a value across each customer’s partition. Use this pattern when row-level detail and a group-level comparison are both needed. Consult PostgreSQL’s dedicated window-function reference for exact behavior and frame details; the SELECT reference also documents window syntax.
Recommended Free Tools
Best Value
- Used Book in Good Condition
9. Name a step with a common table expression
To identify customers whose completed-order item revenue exceeds 500, a CTE can name the intermediate customer totals before the final filter. This returns qualifying customer identifiers and totals.
WITH customer_revenue AS (
SELECT o.customer_id,
SUM(oi.quantity * oi.unit_price) AS item_revenue
FROM orders AS o
JOIN order_items AS oi
ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.customer_id
)
SELECT customer_id, item_revenue
FROM customer_revenue
WHERE item_revenue > 500;The WITH clause gives a query step a name so it can be referenced by the main query. A CTE is a way to structure SQL, not a universal performance improvement; PostgreSQL documents WITH queries and materialization options in its SELECT reference.
Practice the patterns in a browser
PGExercises offers questions and explanations using a shared practice dataset. Its coverage includes selection and filtering, joins, CASE, aggregation, window functions, and recursive queries. Its exercises use their own schema, so adapt the ideas rather than expecting the shop-table examples above to run unchanged. PGExercises also recommends pairing practice with a book or PostgreSQL documentation.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
