Common Interview Patterns

SELECT email, COUNT(*) AS dup_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

WITH dupes AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
  FROM users
)
SELECT * FROM dupes WHERE rn > 1;

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE rnk = 2;

WITH distinct_logins AS (
  SELECT DISTINCT user_id, login_date FROM user_logins
),
grouped AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ))::INT * INTERVAL '1 day' AS group_key
  FROM distinct_logins
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS consecutive_days
FROM grouped
GROUP BY user_id, group_key
HAVING COUNT(*) >= 2;

SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4
FROM sales_data
GROUP BY product;

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS total
  FROM sales
  GROUP BY 1
)
SELECT
  month,
  total AS current_month,
  LAG(total) OVER (ORDER BY month) AS prev_month,
  ROUND(
    (total - LAG(total) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(total) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct
FROM monthly;

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort
  FROM users
),
activity AS (
  SELECT c.user_id, c.cohort, DATE_TRUNC('month', a.activity_date) AS active_month
  FROM cohorts c
  LEFT JOIN user_activity a ON c.user_id = a.user_id
),
cohort_sizes AS (
  SELECT cohort, COUNT(DISTINCT user_id) AS size
  FROM cohorts
  GROUP BY cohort
)
SELECT
  a.cohort,
  a.active_month,
  EXTRACT(MONTH FROM AGE(a.active_month, a.cohort)) AS months_since_join,
  COUNT(DISTINCT a.user_id) AS active,
  cs.size AS cohort_size,
  ROUND(COUNT(DISTINCT a.user_id) * 100.0 / cs.size, 2) AS retention_pct
FROM activity a
JOIN cohort_sizes cs ON a.cohort = cs.cohort
WHERE a.active_month IS NOT NULL
GROUP BY a.cohort, a.active_month, cs.size
ORDER BY a.cohort, a.active_month;

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

SELECT d.dept_name, e.*
FROM departments d
CROSS JOIN LATERAL (
  SELECT employee_name, salary
  FROM employees
  WHERE department_id = d.id
  ORDER BY salary DESC
  LIMIT 3
) e;