PostgreSQL exercises

Windows and CTEs

RANK, ROW_NUMBER, and SUM() OVER are the same idea in PostgreSQL. CTEs are the same shape. PostgreSQL materializes a CTE less aggressively than it used to, so you can use one for readability without assuming it is an optimization fence.

Dates

If paid_at is a real date or timestamptz, use to_char(paid_at, 'YYYY-MM') or date_trunc('month', paid_at) instead of substr. Interval math such as created_at + INTERVAL '1 month' replaces date(created_at, '+1 month'). The retention exercise is the one to translate first.

Example

-- PostgreSQL form of second-month retention bounds
SELECT id,
       to_char(created_at, 'YYYY-MM') AS signup_month,
       to_char(created_at + INTERVAL '1 month', 'YYYY-MM') AS next_month
FROM users;

Keep the two EXISTS tests from the exercise. Replace only the month keys.

Practice

• HardHR
  Employees and their managers

  Show each employee and the manager they report to.

• HardE-commerce
  Customer spend with a CTE

  Calculate spend per customer on paid and shipped orders.

• HardSaaS
  Workspace cohorts and active accounts

  For each workspace creation month, count workspaces and how many are currently active.

• HardHR
  Current salary for each employee

  Return one current salary row per employee.

• MediumE-commerce
  Top customer in each country

  Find the customer with the highest recognized spend in each country.

• HardSaaS
  Second-month user retention

  Find users who were active in their signup month and the following calendar month.

• HardE-commerce
  Running total of cash collected

  Build a running total of succeeded payments in chronological order.

• HardHR
  Rank employees by current salary

  Rank every employee by their current annual salary.

• HardHR
  Months in previous roles

  Measure how long each closed job-history role lasted, in calendar months.

Common mistakes

• Dividing integers and expecting a decimal average. Cast to numeric.
• Forgetting that a LEFT JOIN plus a WHERE on the right table becomes an inner join.
• Using RANK when the product owner asked for exactly one row and ties must be broken. That is ROW_NUMBER plus a tie breaker.
• Comparing timestamps to a date string without a cast and depending on the session TimeZone.

FAQ

Where is FILTER (WHERE ...)?

PostgreSQL can write SUM(amount) FILTER (WHERE status = 'succeeded'). The exercises use a WHERE or a CASE so the same query runs in the sandbox. FILTER is worth learning once the CASE version is clear.

Does DISTINCT ON appear here?

Not in the checker, because SQLite does not support it. The current-salary exercise is the portable version of the DISTINCT ON (employee_id) pattern.

Related practice

• SQL practice
• Beginner SQL practice
• SQL JOINs
• GROUP BY