During the PhD · Interviews

Coding and SQL

You do not need to be a software engineer. You do need to pull and shape data on a whiteboard or in a shared editor without freezing.

5 min read · By Marc Sleiman

Most economist roles will not ask you to write complex software. Many will ask you to write SQL, and some will ask you to manipulate data in Python or R while someone watches. Data science roles may add basic algorithm questions. The bar is not perfection. It is whether you can get the data you need, correctly, without freezing, and explain what you are doing as you go.

SQL: what is tested

Be fluent in selecting and filtering rows (SELECT, WHERE), grouping and aggregating (GROUP BY, COUNT, SUM, AVG, HAVING), sorting (ORDER BY), joining tables (INNER JOIN and LEFT JOIN, and when each drops rows), combining results (UNION), conditional logic (CASE WHEN), and window functions (ROW_NUMBER, RANK, running totals with SUM() OVER, and LAG for comparing a row with the previous one). Common table expressions (WITH) make longer queries readable; use them.

When you answer, talk through it: restate what the output should look like (“one row per customer with their total spend”), mention edge cases such as missing values, duplicates and ties, and write the query step by step.

Practice questions

Assume two tables: orders(order_id, customer_id, order_date, amount) and customers(customer_id, signup_date, city). Try each before reading the solution.

SELECT c.city,
       COUNT(o.order_id) AS n_orders,
       SUM(o.amount)     AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
GROUP BY c.city
ORDER BY revenue DESC;

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

WITH ranked AS (
  SELECT customer_id, order_date, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date, order_id) AS rn
  FROM orders
)
SELECT customer_id, order_date AS first_order_date, amount AS first_amount
FROM ranked
WHERE rn = 1;

WITH ranked AS (
  SELECT customer_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date, order_id) AS rn
  FROM orders
),
firsts  AS (SELECT customer_id, order_date AS d1 FROM ranked WHERE rn = 1),
seconds AS (SELECT customer_id, order_date AS d2 FROM ranked WHERE rn = 2)
SELECT AVG(CASE WHEN s.d2 <= f.d1 + INTERVAL '30 days' THEN 1.0 ELSE 0 END)
       AS repeat_30d_rate
FROM firsts f
LEFT JOIN seconds s ON f.customer_id = s.customer_id;

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
       100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month) AS pct_change
FROM monthly
ORDER BY month;

WITH spend AS (
  SELECT c.city, o.customer_id, SUM(o.amount) AS revenue
  FROM orders o JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY c.city, o.customer_id
),
ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY city ORDER BY revenue DESC) AS r
  FROM spend
)
SELECT city, customer_id, revenue FROM ranked WHERE r <= 3;

Working with data in Python or R

If you work in Stata, translate your everyday tasks into pandas or R’s tidyverse before your interviews. Interviewers commonly ask you to load a dataset, inspect it, compute summary statistics, handle missing values, duplicates and outliers, create new variables, recode categories (including one-hot encoding), merge two datasets, reshape between long and wide, group and aggregate, plot a distribution or trend, and run a regression or a hypothesis test.

A typical live exercise, in pandas:

import pandas as pd
import statsmodels.formula.api as smf

df = pd.read_csv("experiment.csv")         # user_id, group, revenue, country
df = df.drop_duplicates("user_id")
print(df.isna().mean())                    # share missing by column
df["revenue"] = df["revenue"].fillna(0)    # no purchase means zero revenue

summary = df.groupby("group")["revenue"].agg(["count", "mean", "std"])
print(summary)

df["treated"] = (df["group"] == "treatment").astype(int)
model = smf.ols("revenue ~ treated + C(country)", data=df).fit(cov_type="HC1")
print(model.summary().tables[1])

Narrate as you go: why you dropped duplicates on user_id, why a missing value means zero revenue here (and when it would not), why you added country as a control (precision, not identification, since treatment was randomized) and why you used robust standard errors.

Algorithms and data structures

These are rare for economist roles but more common for data scientist and applied scientist roles at large technology companies. If your target roles include them, learn the basic data structures (lists or arrays, dictionaries or hash maps, sets, stacks, queues, heaps and trees), common techniques (sorting, binary search, two pointers, counting with a hash map) and how to describe time and space complexity. Typical questions: remove duplicates from a list while keeping order, check whether a string is a palindrome, find the two numbers in a list that sum to a target, or count the most frequent items.

If you have time early in your PhD, take one computer science course on data structures. It makes these questions far easier and improves your everyday code.

How to practice

Do a few SQL problems several times a week on a practice platform such as LeetCode, StrataScratch or Interview Query, starting with easy ones. Rebuild one of your Stata projects in Python or R from scratch. Practice live: have a friend give you a dataset and a question and watch you work for thirty minutes while you talk. The talking is the part people skip and the part that interviews actually test.