PostgreSQL

…

Tool

ACID relational database with advanced types (JSONB, arrays, range), CTEs, window functions and extensibility.

Key points

Full ACID: serializable transactions available; READ COMMITTED is the reasonable default.
JSONB for semi-structured data with GIN indexes; flexibility without leaving the relational model.
CTEs (WITH ...) keep complex queries readable; WITH RECURSIVE walks trees and graphs.
Window functions (ROW_NUMBER, LAG, PARTITION BY) enable row-by-row analytics without GROUP BY.
Extensions (pgvector, postgis, timescaledb) add new capabilities without switching engines.
EXPLAIN ANALYZE shows the real plan; pg_stat_statements pinpoints slow queries in production.

Code

WITH ranked AS (
  SELECT
    user_id,
    amount,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC
    ) AS rn
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT user_id, amount, created_at
FROM ranked
WHERE rn = 1;

CTE + window function to fetch each user's most recent order in the last 30 days.