Gapless Sequence (DENSE_RANK())

-- DENSE_RANK: same rank for ties, NO gaps
SELECT
  name, salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- Alice: 90000 → 1
-- Bob:   85000 → 2
-- Carol: 85000 → 2  (tie — same rank as Bob)
-- Dave:  80000 → 3  (NOT 4 — no gap!)

-- Find Nth highest salary (classic interview question)
SELECT DISTINCT salary FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) ranked
WHERE dr = 2;   -- 2nd highest salary

-- Top N per category (gapless)
SELECT category, product_name, price, cat_rank
FROM (
  SELECT cat.name AS category, p.name AS product_name, p.price,
         DENSE_RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS cat_rank
  FROM products p JOIN categories cat ON p.category_id = cat.id
) r WHERE cat_rank <= 3;

-- Coding competition: score rankings (fair tie handling)
WITH contest_scores AS (
  SELECT
    u.name,
    u.college,
    SUM(s.score)                                                AS total_score,
    COUNT(DISTINCT s.problem_id)                                AS problems_solved,
    DENSE_RANK() OVER (ORDER BY SUM(s.score) DESC,
                                COUNT(DISTINCT s.problem_id) DESC) AS overall_rank,
    DENSE_RANK() OVER (
      PARTITION BY u.college
      ORDER BY SUM(s.score) DESC
    )                                                           AS college_rank
  FROM users u
  JOIN contest_submissions s ON u.id = s.user_id
  WHERE s.contest_id = 42 AND s.is_correct = TRUE
  GROUP BY u.id, u.name, u.college
)
SELECT
  overall_rank,
  CASE overall_rank WHEN 1 THEN '🥇' WHEN 2 THEN '🥈' WHEN 3 THEN '🥉' ELSE '' END AS medal,
  name, college, total_score, problems_solved, college_rank
FROM contest_scores
ORDER BY overall_rank, name;