If you try to retrieve only the latest login for each user with a 1:N JOIN, the users rows expand by the number of rows in login_history.

The window function ROW_NUMBER() assigns a sequence number to every row in a group. Specify the group with PARTITION BY and the sort order with ORDER BY to select the newest row for each user, where rn=1.

-- ROW_NUMBER() syntax
ROW_NUMBER() OVER (
  PARTITION BY user_id         -- reset for each user
  ORDER BY login_at DESC      -- number newest first
) AS rn                        -- rn=1 is the latest login

Join each user's latest login timestamp (last_login) to the users table. It is acceptable for last_login to be NULL when a user has no login history.

Note: a direct JOIN would expand each users row by its number of logins. Use the correct approach: narrow the N side to one row with CTE + ROW_NUMBER before joining.

WITH ranked_logins AS (
  SELECT
    user_id,
    login_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_at DESC   -- the newest row gets rn=1
    ) AS rn
  FROM login_history
)
SELECT
  u.name,
  r.login_at AS last_login
FROM users AS u
INNER JOIN ranked_logins AS r
  ON  u.user_id = r.user_id
  AND r.rn = 1              -- join only the latest row → becomes 1:1
ORDER BY u.user_id;

/*
  Execution order:
  1. CTE ranked_logins                → Read login_history
  2. ROW_NUMBER() OVER (...)          → Number each user's rows newest first
  3. FROM users AS u                  → Read users
  4. INNER JOIN ranked_logins (rn=1)  → Join only the latest rows
  5. SELECT u.name, r.login_at        → Project two columns
  6. ORDER BY u.user_id               → Sort and output
  */

In Part 1 Q5, we avoided a fan trap with an inline subquery. Writing the same solution with a CTE (WITH clause) makes the query much easier to read. A CTE is a named subquery that is defined before the main query.

-- Basic CTE syntax: define aggregate tables before joining
WITH emp_agg AS (
  SELECT dept_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY dept_id             -- convert 1:N to 1:1
),
sales_agg AS (...)           -- chain multiple CTEs with commas
SELECT ... FROM departments
JOIN emp_agg ...               -- safe 1:1 × 1:1 join

Retrieve the total employee salary (total_salary) and total sales (total_sales) for each department. Use the correct approach: pre-aggregate each 1:N table with a CTE (WITH clause) before joining them.

Note: a direct JOIN creates an employees×dept_sales Cartesian product and doubles every value (the fan trap).

WITH emp_agg AS (
  SELECT dept_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY dept_id            -- convert 1:N → 1:1 (dept_id becomes unique)
),
sales_agg AS (
  SELECT dept_id, SUM(amount) AS total_sales
  FROM dept_sales
  GROUP BY dept_id            -- convert 1:N → 1:1 (dept_id becomes unique)
)
SELECT
  d.dept_name,
  e.total_salary,
  s.total_sales
FROM departments AS d
INNER JOIN emp_agg  AS e ON d.dept_id = e.dept_id  -- 1:1
INNER JOIN sales_agg AS s ON d.dept_id = s.dept_id  -- 1:1
ORDER BY d.dept_id;

/*
  Execution order:
  1. CTE emp_agg           → Aggregate employees (unique dept_id)
  2. CTE sales_agg         → Aggregate dept_sales (unique dept_id)
  3. FROM departments      → Read departments
  4. INNER JOIN emp_agg    → 1:1 join on dept_id
  5. INNER JOIN sales_agg  → 1:1 join on dept_id
  6. SELECT, ORDER BY      → Project columns and sort
  */

The granularity of a table describes what one row represents. In a monthly table, one row represents one department and one month; in a daily table, one row represents one department and one day.

When tables with different granularities are joined directly, the coarser-grained value is copied once for every finer-grained row. Joining a monthly budget (one row per month) to three daily sales rows copies the budget three times.

-- ✗ Direct JOIN → monthly_budget.budget is copied for every daily_sales row
FROM monthly_budget AS b
JOIN daily_sales    AS d ON b.dept_id = d.dept_id
                              AND b.month = DATE_TRUNC('month', d.sale_date)
-- dept_id=1: budget=500000 copied for 3 days → SUM(budget) = 1500000 (3x!)

Using monthly_budget (monthly budgets) and daily_sales (daily sales), retrieve the budget (budget), monthly sales total (total_revenue), and budget achievement rate (achievement_rate: %, rounded to one decimal place) for each department and month.

Note: a direct JOIN inflates monthly_budget.budget by the number of daily rows (threefold). Use a CTE to aggregate daily data to monthly granularity before joining.

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', sale_date)::DATE AS month,  -- convert daily data to monthly granularity
    dept_id,
    SUM(revenue)                              AS total_revenue
  FROM daily_sales
  GROUP BY DATE_TRUNC('month', sale_date)::DATE, dept_id
)
SELECT
  b.month,
  b.dept_id,
  b.budget,
  ms.total_revenue,
  ROUND(ms.total_revenue::NUMERIC / b.budget * 100, 1) AS achievement_rate
FROM monthly_budget AS b
LEFT JOIN monthly_sales AS ms
  ON  b.month   = ms.month
  AND b.dept_id = ms.dept_id
ORDER BY b.month, b.dept_id;

/*
  Execution order:
  1. CTE monthly_sales              → Read daily_sales
  2. DATE_TRUNC('month', ...)       → Round dates down to the first of the month
  3. GROUP BY month, dept_id        → Group by month × department
  4. SUM(revenue)                   → Aggregate monthly sales
  5. FROM monthly_budget AS b       → Read budgets
  6. LEFT JOIN monthly_sales AS ms  → Join on month + dept_id
  7. ROUND(...)                     → Calculate the achievement rate (%)
  8. SELECT, ORDER BY               → Project columns and sort
  */

When looking for users who bought both Coffee and Cake through an N:N junction table, it is tempting to write WHERE product = 'A' AND product = 'B'. But this always returns zero rows.

-- ✗ AND conditions apply to the same row
WHERE p.product_name = 'Coffee'
  AND p.product_name = 'Cake'
-- one product_name cannot hold two values at once → always 0 rows

-- ✓ narrow candidate rows with IN, then test “has both” with HAVING
WHERE p.product_name IN ('Coffee', 'Cake')   -- retrieve candidate rows with OR
HAVING COUNT(DISTINCT p.product_name) = 2   -- keep groups containing two products

Using the users and purchases tables, retrieve users who bought both “Coffee” and “Cake”.

Note: WHERE product_name = 'Coffee' AND product_name = 'Cake' returns zero rows. Use IN + GROUP BY + HAVING COUNT(DISTINCT ...).

-- ✗ AND: one row cannot be both “Coffee” and “Cake” → 0 rows
-- WHERE p.product_name = 'Coffee' AND p.product_name = 'Cake'

SELECT
  u.user_id,
  u.name
FROM users AS u
INNER JOIN purchases AS p
  ON u.user_id = p.user_id
WHERE p.product_name IN ('Coffee', 'Cake')  -- first narrow candidate rows with OR
GROUP BY u.user_id, u.name
HAVING COUNT(DISTINCT p.product_name) = 2  -- keep only groups with both products
ORDER BY u.user_id;

/*
  Execution order:
  1. FROM users AS u                      → Read users
  2. INNER JOIN purchases AS p            → Join (expanded 1:N)
  3. WHERE product_name IN (...)          → Keep target products
  4. GROUP BY u.user_id, u.name           → Group by user
  5. HAVING COUNT(DISTINCT product_name)  → Keep users with both products
  6. SELECT u.user_id, u.name             → Project two columns
  7. ORDER BY u.user_id                   → Sort and output
  */