Interview Prep I — SQL & Coding

Problem. Given employees(id, name, dept, salary): (a) return the second-highest salary overall, and (b) return the top 2 highest-paid employees in each department.

Try it before revealing.

-- (a) second-highest salary overall.
-- DENSE_RANK handles ties (two people tied for #1 still makes the next #2).
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;

-- (b) top 2 earners per department.
SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 2
ORDER BY dept, salary DESC;

Why it works: rank rows inside a subquery, then filter on the rank in the outer query — you can't put a window function in WHERE directly, so the subquery (or a CTE) is mandatory. Use DENSE_RANK when ties should share a place; ROW_NUMBER when you want exactly N rows per group.

Problem. Given orders(id, order_date, amount): produce, per calendar month, (a) that month's total revenue, (b) a cumulative running total across months, and (c) the percent change versus the previous month.

Try it before revealing.

WITH monthly AS (
  SELECT date_trunc('month', order_date) AS mth,
         SUM(amount)                     AS revenue
  FROM orders
  GROUP BY date_trunc('month', order_date)
)
SELECT
  mth,
  revenue,
  SUM(revenue) OVER (ORDER BY mth)        AS running_total,
  LAG(revenue) OVER (ORDER BY mth)        AS prev_month,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY mth))
          / LAG(revenue) OVER (ORDER BY mth), 1
  )                                       AS mom_pct
FROM monthly
ORDER BY mth;

Why it works: first roll the raw rows up to one row per month in a CTE, then layer window functions on top. SUM() OVER (ORDER BY mth) accumulates because an ORDER BY in a window defaults to "everything from the start up to the current row." LAG grabs the previous month's value for the growth comparison; the first month's mom_pct is NULL, which is correct — there's nothing before it.

Problem. A table events(user_id, status, updated_at) has multiple rows per user from repeated updates. Return exactly one row per user — the most recent one. Then, as a stretch, count how many users have a contiguous streak: their two latest events both have status = 'active'.

Try it before revealing.

-- Dedup: number each user's rows newest-first, keep #1.
WITH ranked AS (
  SELECT user_id, status, updated_at,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY updated_at DESC) AS rn
  FROM events
)
SELECT user_id, status, updated_at
FROM ranked
WHERE rn = 1;

-- Stretch: users whose two most-recent events are both 'active'.
WITH ranked AS (
  SELECT user_id, status,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY updated_at DESC) AS rn
  FROM events
)
SELECT COUNT(*) AS streaking_users
FROM (
  SELECT user_id
  FROM ranked
  WHERE rn <= 2
  GROUP BY user_id
  HAVING COUNT(*) = 2                       -- needs at least 2 events
     AND SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) = 2
) t;

Why it works: "one row per group" is the canonical ROW_NUMBER() … = 1 pattern — partition by the entity, order by recency, keep the top. The stretch reuses the same ranking, takes the two newest rows, and uses a conditional SUM to confirm both are active. That CASE-inside-SUM trick (counting rows that meet a condition) is itself a recurring interview move.

Problem. Given a list of order records, return total revenue per region, sorted highest-first. Each record looks like {"region": "us", "amount": 120}.

Try it before revealing.

from collections import defaultdict

orders = [
    {"region": "us", "amount": 120},
    {"region": "eu", "amount": 80},
    {"region": "us", "amount": 200},
    {"region": "eu", "amount": 95},
]

totals = defaultdict(float)
for o in orders:
    totals[o["region"]] += o["amount"]

# sort by revenue, highest first -> list of (region, total) tuples
result = sorted(totals.items(), key=lambda kv: kv[1], reverse=True)
print(result)   # [('us', 320.0), ('eu', 175.0)]

Why it works: defaultdict(float) lets you += into a key that doesn't exist yet (no "is this key here?" check), which is the cleanest group-and-sum in Python. Then sorted with a key= lambda orders by the value. Say the complexity out loud: one pass to group (O(n)), one sort (O(k log k)) over the number of regions.

Problem. You're handed raw lines like "alice,alice@x.com" (some with stray whitespace and mixed case in the email). Parse them, and return the list of email addresses that appear more than once — normalised (trimmed + lowercased) and deduplicated.

Try it before revealing.

from collections import Counter

lines = [
    "alice, Alice@X.com ",
    "bob,bob@x.com",
    "alice2, alice@x.com",   # same email as the first row, different name
    "carol,carol@x.com",
    "bob3, BOB@x.com ",      # same email as bob
]

def email_of(line):
    _name, email = line.split(",", 1)   # split once; names never contain commas here
    return email.strip().lower()        # normalise: trim + lowercase

counts = Counter(email_of(line) for line in lines)
dupes = [email for email, n in counts.items() if n > 1]
print(dupes)   # ['alice@x.com', 'bob@x.com']

Why it works: the parsing step is the real trap — interviewers love messy input. Normalising (strip().lower()) before comparing is what makes the dedup correct. Counter tallies in one pass; the comprehension keeps only emails seen more than once. Mention edge cases out loud: malformed lines with no comma, or empty emails — handling them gracefully scores points even if the prompt didn't ask.

Problem. Given logins(user_id, login_date) with at most one row per user per day, find the length of each user's longest run of consecutive calendar days. Try it before revealing — this is the classic "gaps and islands" pattern dressed down.

WITH numbered AS (
  SELECT user_id, login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY login_date) AS rn
  FROM logins
),
grouped AS (
  -- Subtracting the row number from the date is constant
  -- for any run of consecutive days -> that constant IS the group key.
  SELECT user_id,
         login_date - (rn * INTERVAL '1 day') AS streak_key
  FROM numbered
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM (
  SELECT user_id, streak_key, COUNT(*) AS streak_len
  FROM grouped
  GROUP BY user_id, streak_key
) runs
GROUP BY user_id
ORDER BY user_id;

Why it works: the key insight is that date − row_number stays constant across a stretch of consecutive days (both increase in lockstep) but jumps whenever there's a gap. So that difference becomes a group key for each "island" of consecutive days. Group by it, count the rows in each island, take the max per user. If you recognised this as gaps-and-islands and reached for the ROW_NUMBER trick, you're ready for the live round.