The Analytics Engineering Interview Kit

Generic advice such as “Practice SQL” does not define what to practice. This kit organizes representative SQL, modeling, portfolio, and behavioral exercises around four common interview formats. Written by Eric Provencio — analytics engineer at Disney, Hulu, Nike, Peloton, and Gopuff, and a 5.0-rated mentor.

Learn the recurring patterns, practice explaining them, and prepare one project you can discuss without notes.

The shape of the loop

Many analytics engineering interview processes draw from four common components. An employer may use all four, combine them, or replace one with a take-home or case study:

1. 01Live SQL or SQL take-home — write a query against a small schema, explain the grain and assumptions, and validate the result. When it is live, the interviewer may care as much about your reasoning as the final syntax.
2. 02Data modeling round — usually conceptual or a whiteboard: design a model for X, explain a star schema, handle a slowly changing dimension.
3. 03Portfolio walkthrough — you drive, 10–15 minutes, explaining a project while they poke at your decisions.
4. 04Behavioral — “tell me about a time,” stakeholder conflict, a mistake you made.

Do not make traditional algorithm practice your default unless the employer says it is part of the process. For most analytics engineering preparation, spend more time on SQL, data modeling, dbt decisions, validation, and explaining a project clearly.

Part 1 — The SQL screen

These five patterns cover useful ground for many SQL screens. Practice the edge cases and explain your reasoning instead of memorizing one query shape.

1. Aggregation with a twist (GROUP BY + HAVING)

From an orders table, find customers with more than 3 orders and total spend over $500.

select customer_id, count(*) as orders, sum(amount) as total_spend
from orders
group by customer_id
having count(*) > 3 and sum(amount) > 500;

What they're checking: do you know HAVING filters after aggregation (not WHERE).

2. The “latest record per group” (window functions)

Return the most recent order per customer.

select * from (
  select *,
    row_number() over (partition by customer_id order by order_date desc) as rn
  from orders
) where rn = 1;

Practice this latest-record-per-group pattern with duplicate timestamps, null values, and deterministic tie-breaking.

3. Period-over-period (LAG)

Show each month's revenue and the % change from the prior month.

with monthly as (
  select date_trunc('month', order_date) as month, sum(amount) as revenue
  from orders group by 1
)
select month, revenue,
  round(100.0 * (revenue - lag(revenue) over (order by month))
        / lag(revenue) over (order by month), 1) as pct_change
from monthly;

4. The join that doesn't explode

Count orders per customer, including customers with zero orders. The trap is an inner join (drops the zero-order customers) or a fan-out that double counts. The answer is a LEFT JOIN from customers, COUNT(orders.id) (not COUNT(*)), grouped by customer.

5. Deduplication

Given duplicate rows, return distinct records keeping the latest. Same ROW_NUMBER() pattern as #2 — partition by the natural key, order by updated-at, keep rn = 1.

State your plan as you work—for example, explain why you are starting with a monthly CTE. This gives the interviewer evidence about your reasoning as well as your final query.

Part 2 — Data modeling

These separate junior from senior. Have crisp, one-paragraph answers ready.

• Fact vs dimension table: Facts store measurable events (one row per order, click, payment) with numeric measures and foreign keys. Dimensions store the descriptive context you slice by. Facts are tall and narrow; dimensions are wide and short.
• Grain: what one row represents. It's the first thing you declare when modeling a fact table — “one row per order line per day” — because mixing grains silently double-counts every metric.
• Changing attributes over time: Slowly Changing Dimension. Type 1 overwrites (no history); Type 2 keeps history by closing the old row and inserting a new one with valid-from/valid-to dates. Type 2 is the one they want you to know.
• “Walk me through modeling X”: use Kimball's four steps out loud — pick the business process, declare the grain, choose the dimensions, choose the facts.

Part 3 — The portfolio walkthrough

This is where offers are won or lost, and it's the part people prep least.

• Lead with the problem, not the tech. “The business couldn't trust revenue because three teams defined it differently” beats “I used dbt and BigQuery.”
• Show the repo and the lineage. A real GitHub repo with staging → marts layers and a dbt DAG signals you've done the actual job.
• Have a decision to defend. Explain one modeling trade-off: “I went Type 2 here because finance needed point-in-time reporting.”
• Rehearse out loud ten times. The walkthrough fails on delivery, not content.

Part 4 — Behavioral

Use STAR (Situation, Task, Action, Result) and keep answers under two minutes. Prepare three stories you can adapt:

1. 01A time you found and fixed a data quality issue.
2. 02A time you disagreed with a stakeholder about a metric definition.
3. 03A mistake you made in production and what you changed afterward.

The mistake story builds the most trust: “I shipped a model that double-counted because I mixed grains; now I add a uniqueness test on the grain of every fact” is a great answer.

The disqualifiers

• Going silent during the SQL screen.
• Not being able to explain your own portfolio project's decisions.
• Confusing WHERE and HAVING, or COUNT(*) vs COUNT(column) with NULLs.
• Talking tools instead of business impact.
• No questions for the interviewer (always have two).

Your 7-day drill plan

• Days 1–2: The five SQL patterns above, timed, until automatic.
• Day 3: Window functions deep — ROW_NUMBER, RANK, LAG/LEAD, running totals.
• Day 4: Modeling answers — say each one out loud in one paragraph.
• Day 5: Portfolio walkthrough rehearsal (×3, out loud).
• Day 6: Behavioral stories in STAR format.
• Day 7: Full mock — one SQL problem, one modeling question, one walkthrough.