SQL Lab

Window functions compute values across related rows while keeping row detail.

A recursive CTE has an anchor query and a recursive query.

Joining multiple one-to-many tables can multiply rows.

A subquery is a query nested inside another query. It can appear in SELECT, FROM, WHERE, or HAVING.

Start by identifying the tables you need and how they relate.

GROUP BY collapses rows into groups. Window functions keep every row and compute a value across a related set.

JSONPath is a query language for JSONB built into PostgreSQL 12+.

JSONB is common for event payloads, settings, and semi-structured metadata.

PostgreSQL full-text search can cover many product-search and admin-search needs before adding a separate search system.

Functions let you encapsulate logic inside the database.

PostgreSQL supports arrays as first-class column types.

Create, manage, and optimize views including materialized views, updatable views, and view dependencies.

Build and query full-text search indexes using tsvector, tsquery, and ranking functions.

Master SERIAL, IDENTITY, UUID, and custom sequence patterns for robust ID generation.

Use LATERAL to correlate subqueries and build powerful row-by-row computations.

PostgreSQL has useful query forms beyond the smallest shared SQL core.

EXPLAIN is how PostgreSQL shows the plan it picked for a query.

Application validation is useful, but database constraints protect every write path.

Row-level security moves some authorization rules into PostgreSQL itself.

Application SQL is not only SELECT: write paths need predictable conflict handling and returned state.

Indexes are part of query design, not an afterthought.

High-read dashboards often need precomputed query shapes.

Partitioning is a physical design tool for very large tables and retention-heavy workloads.

Triggers run code automatically when rows change.

RETURNING lets you get the inserted or updated row without a second query.

Production SQL is often about making the database do less work predictably.

Small and medium systems often use PostgreSQL as the first durable job queue.

Production migrations should avoid invalid intermediate states: add nullable columns first, backfill data, then add defaults and NOT NULL constraints.

A race condition appears when two workers read the same old state and both try to write a new state.

Essential DBA operations: user management, backups, replication basics, and monitoring queries.

Schema design is the foundation of reliable data. Choose types that match your domain and constraints that prevent invalid states.

This pack connects the separate skills: schema design, seed data, reporting queries, indexes, migration, and restore verification.

This lab turns SQL from syntax practice into analyst work: metric definitions, grain, cohorts, funnels, sessions, segments, and business interpretation.

You are looking at a small payment dataset with users, devices, transactions, chargebacks, and rule hits.