Why does my query return different results for equal sort keys?

Without ORDER BY, SQL does not guarantee any row order, even if the table has a sort key. Always add ORDER BY when order matters, and add tie-breaker columns to make it deterministic.

Amazon Redshift Module 4: Querying, Window Functions, and Semi-Structured Data

Introduction

Redshift supports rich analytical SQL. This module covers the features analysts use daily: common table expressions, window functions, date functions, and the SUPER type for JSON and other semi-structured data. Examples use the star schema from Module 2.

Prerequisites

Module 3 completed

Step-by-Step Guide

Step 1: Join facts to dimensions

Most warehouse queries join the fact table to a few dimensions, filter on dimension attributes, and aggregate. Filter on the sort key column of the fact table so Redshift can skip blocks.

SELECT d.year, d.month, p.category, sum(f.amount) AS revenue
FROM fact_sales f
JOIN dim_date d     ON d.date_key = f.sale_date
JOIN dim_product p  ON p.product_id = f.product_id
WHERE f.sale_date >= '2026-01-01'
GROUP BY d.year, d.month, p.category
ORDER BY d.year, d.month, revenue DESC;

Step 2: Structure queries with CTEs

A common table expression gives a name to a step so long queries read top to bottom. Use them to separate cleaning, aggregation, and final selection.

WITH customer_revenue AS (
  SELECT customer_id, sum(amount) AS revenue
  FROM fact_sales
  WHERE sale_date >= dateadd(month, -12, current_date)
  GROUP BY customer_id
)
SELECT c.customer_name, r.revenue
FROM customer_revenue r
JOIN dim_customer c USING (customer_id)
ORDER BY r.revenue DESC
LIMIT 10;

Step 3: Use window functions

Window functions calculate across related rows without collapsing them: rankings, running totals, and comparisons with the previous row. They are central to analytics SQL.

SELECT sale_date,
       sum(amount) AS revenue,
       sum(sum(amount)) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS running_total,
       sum(amount) - lag(sum(amount)) OVER (ORDER BY sale_date) AS change_from_prior_day
FROM fact_sales
GROUP BY sale_date
ORDER BY sale_date;

Step 4: Work with dates

Redshift provides DATE_TRUNC, DATEADD, DATEDIFF, and EXTRACT for date arithmetic. Truncate dates to group by week or month, and prefer comparing against date ranges on the raw column so the sort key can be used.

SELECT date_trunc('month', sale_date) AS month, sum(amount) AS revenue
FROM fact_sales
WHERE sale_date >= '2026-01-01' AND sale_date < '2027-01-01'
GROUP BY 1
ORDER BY 1;

SELECT datediff(day, '2026-09-01', current_date) AS days_since;

Step 5: Query JSON with the SUPER type

SUPER stores semi-structured data such as JSON without a fixed schema, and you navigate it with PartiQL syntax using dots and brackets. Load JSON text with JSON_PARSE, then read fields directly. Unnesting an array uses a FROM clause on the array.

CREATE TABLE events (id int, payload super);

INSERT INTO events VALUES
  (1, JSON_PARSE('{"user": {"id": 7, "plan": "pro"}, "items": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}]}'));

SELECT e.id, e.payload.user.plan AS plan, i.sku, i.qty
FROM events e, e.payload.items AS i;

FAQs

Why does my query return different results for equal sort keys?: Without ORDER BY, SQL does not guarantee any row order, even if the table has a sort key. Always add ORDER BY when order matters, and add tie-breaker columns to make it deterministic.

Summary

Join facts to dimensions and filter on the sort key, use CTEs for readability, window functions for rankings and running totals, date functions for time grouping, and the SUPER type with PartiQL for JSON data.