PostgreSQL Interview & Business Metrics Cheat Sheet

Window functions compute aggregates without collapsing individual rows into a single summary row.

-- Row Number vs Rank vs Dense Rank
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
-- 1, 2, 3, 4 (Strict sequential index)

RANK()       OVER (PARTITION BY dept ORDER BY salary DESC)
-- 1, 2, 2, 4 (Gaps after duplicate ties)

DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
-- 1, 2, 2, 3 (No gaps after duplicate ties)

-- Lead & Lag Historical Offsets
LAG(revenue, 1)  OVER (ORDER BY month_date) -- Previous row
LEAD(revenue, 1) OVER (ORDER BY month_date) -- Next row

Control the window frame bounds to calculate rolling 7-day or 30-day running metrics.

-- Running Total from Beginning to Current Row
SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY transaction_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

-- 7-Day Centered Moving Average
AVG(daily_sales) OVER (
  ORDER BY sale_date
  ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
)

Break down complex pipelines into testable phases or traverse hierarchical employee structures.

-- Recursive Hierarchy Traversal (Org Chart)
WITH RECURSIVE OrgHierarchy AS (
  -- Anchor Member: Top CEO
  SELECT emp_id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive Step: Direct Reports
  SELECT e.emp_id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  INNER JOIN OrgHierarchy o ON e.manager_id = o.emp_id
)
SELECT * FROM OrgHierarchy ORDER BY depth, name;

The #1 most asked business question in Product and Analytics Engineer technical screenings.

WITH UserFirstLogin AS (
  SELECT user_id, DATE_TRUNC('month', MIN(login_date)) AS cohort_month
  FROM user_logins
  GROUP BY user_id
)
SELECT 
  c.cohort_month,
  DATE_PART('month', AGE(l.login_date, c.cohort_month))::int AS month_number,
  COUNT(DISTINCT l.user_id) AS active_users
FROM user_logins l
JOIN UserFirstLogin c ON l.user_id = c.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

PostgreSQL timestamp arithmetic rules and date truncation.

-- Truncate to start of period
DATE_TRUNC('month', created_at) -- YYYY-MM-01 00:00:00
DATE_TRUNC('day', created_at)   -- YYYY-MM-DD 00:00:00

-- Interval arithmetic
NOW() - INTERVAL '30 days'      -- 30 days prior
created_at + INTERVAL '1 year'

-- Extract components
EXTRACT(DOW FROM created_at)    -- 0 = Sunday, 6 = Saturday
AGE(NOW(), signup_date)         -- Returns formatted 'X years Y mons'

Modern aggregation filtering and native JSON document extraction.

-- FILTER (WHERE ...) (Faster than CASE WHEN)
COUNT(*) FILTER (WHERE status = 'active') AS active_count,
SUM(amount) FILTER (WHERE currency = 'USD') AS usd_volume

-- JSONB Extraction
payload->>'user_id'              -- Extract as TEXT
(payload->>'duration_ms')::int   -- Cast to INTEGER
payload ? 'is_beta'              -- Key existence check