JSTAcademy
0 XP
Dashboard
Crash Courses
CTEs & Advanced Queries
11 min
Masters+175 XP
Crash Courses · Masters

CTEs & Advanced Queries

Write readable complex queries with CTEs and use JSON operators.
11 min read+175 XP on completionCert: PostgreSQL Crash Course
Tap any word in the text below to start reading from there.

CTEs & Advanced Queries

CTEs break complex queries into readable steps. PostgreSQL's JSON support handles semi-structured data without a separate document store.

CTE (WITH clause)

-- Find users who completed all 8 modules of a crash course
WITH crash_completions AS (
  SELECT
    user_id,
    LEFT(course_id, 8) AS crash_id,
    COUNT(*) AS modules_done
  FROM progress
  WHERE course_id LIKE 'cc-%'
  GROUP BY user_id, LEFT(course_id, 8)
),
certified_users AS (
  SELECT user_id, crash_id
  FROM crash_completions
  WHERE modules_done = 8
)
SELECT
  u.email,
  c.crash_id,
  now() AS certified_at
FROM certified_users c
INNER JOIN auth.users u ON u.id = c.user_id
ORDER BY c.crash_id, u.email;

DISTINCT ON (PostgreSQL-specific)

-- Latest progress entry per user
SELECT DISTINCT ON (user_id)
  user_id,
  course_id,
  completed_at
FROM progress
ORDER BY user_id, completed_at DESC;

JSONB Queries

-- Table with JSONB column
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Query JSONB
SELECT payload->>'event_type' AS type,
       payload->>'user_id' AS user
FROM events
WHERE payload->>'event_type' = 'course_completed';

-- Contains operator
SELECT * FROM events
WHERE payload @> '{"event_type": "course_completed"}';

-- Index on JSONB
CREATE INDEX events_payload_gin ON events USING GIN (payload);
0%