Day-Of Tactics

The night before

• Reread 00-START-HERE, 11-sql-problems, and 15-interview-questions highlights.
• Sleep. Caffeine recovers from one thing; sleep does not.
• Pick a quiet room with a good camera, good light, good audio. Test it.
• Pull up the JD in a tab; have it visible behind your camera (printed sticky if you need it).
• Have water. Have a notepad and pen. Phone face-down, off the desk.
• If it's a shared-screen SQL round: open your editor of choice (or whatever they tell you), warm up the keyboard with one window function.

30 minutes before

• Re-read your one-paragraph self-summary out loud.
• Solve one SQL window-function problem on paper. Just to warm up the muscles.
• Stand up, walk for 10 minutes. Don't keep cramming.
• Three minutes before, sit, breathe slow for 60 seconds. Smile — it changes your voice.

SQL live coding — the framework that wins rounds

Apply this every SQL coding round:

1. Restate the schema. "So we have a users table at one row per user, and an orders table at one row per order — is that right?"
2. Clarify grain & constraints. "What's the output grain? One row per user per month?" "Are timestamps UTC?" "Can a user be deleted?" "Are there duplicates?"
3. Walk through a tiny example. "For user A with orders on Jan 1 and Jan 15, the answer would be 2 — right?"
4. State the approach. "I'll filter active users, aggregate orders by month, then join."
5. Identify the pattern. "This is a latest-row-per-group — ROW_NUMBER pattern." "This is gaps-and-islands." The pattern name is half the answer.
6. Code in CTEs. One concept per CTE. Name them well: active_users, monthly_orders. Resist the urge to write one giant nested query.
7. Talk while you type. Silence reads as stuck. Narration reads as competent.
8. Test against the example. Walk your code line by line against the input.
9. Mention edge cases. NULLs, ties, time-zone confusion, empty input, single-row groups. Even if you don't fix all of them, calling them out signals seniority.
10. Discuss performance. "At scale, I'd add a partition_date filter here. And I'd worry about the grain after this join — let me verify."

SQL gotchas to flag unprompted

Senior signal: you mention these before the interviewer asks. Pick the ones relevant to the problem:

• NULL handling — "I should COALESCE this — otherwise the NOT IN silently drops rows."
• Join fanout — "If I join here without aggregating first, the grain explodes. Let me aggregate in a CTE."
• Time zones — "Are these timestamps UTC? Because DATE_TRUNC on a TZ-aware timestamp behaves differently from a naive one."
• Ties in window functions — "I'll add a secondary order-by column so ties are deterministic."
• Window frame defaults — "I'll specify the ROWS BETWEEN explicitly because the default RANGE frame can surprise."
• Division by zero — "I'll wrap the denominator in NULLIF in case it's zero."
• SELECT * — "In production I'd pull only the columns I need."

Design / system rounds — the framework

For any "design a data system for X" question:

1. Clarify the goal. What business questions does this answer? What's the latency requirement? Who consumes it?
2. Identify the entities — these become dims.
3. Identify the events — these become facts. State the grain of each out loud.
4. Sketch the data flow. Source → load → transform → serve. Mention the tools you'd use at each layer.
5. Pick the materialization strategy. Batch frequency. Incremental vs full rebuild. Aggregation layers.
6. Mention testing & quality upfront, not as an afterthought. "I'd add unique/not_null tests on this dim, plus a singular test for the business rule."
7. Mention monitoring. Source freshness, model run-time, anomaly detection.
8. Talk about evolution. "As volume scales, the daily aggregation table will get slow — I'd move to incremental, partitioned by date_key."
9. Discuss tradeoffs explicitly. "I chose batch over streaming because the dashboards consuming this don't need sub-minute freshness. If they did, I'd reach for [stream tool]."

When you don't know something

Patterns that work:

The reframe: "I don't know" → "Here's the boundary of what I know and how I'd extend it."

When they push back on your answer

They might be testing whether you'll fold. Or they might be right. Hold your view if you have a reason; update if you don't.

That sentence shows judgment and humility. Both win points.

Specific traps to watch for

Questions to ask them (have these ready)

Have 5-6 ready, ask the 2-3 best based on what came up:

1. "What's the state of the warehouse today — clean and modeled, or wedge of legacy SQL?"
2. "How does the team handle data quality alerts when they fire? Is there an on-call rotation?"
3. "How are metric definitions managed — semantic layer, dbt models, convention?"
4. "What's the one model in production right now that everyone's afraid to touch, and why?"
5. "What's the team's biggest analytics question that's still hard to answer with the current setup?"
6. "How does the data team collaborate with engineering on schema changes? Contracts or convention?"
7. "What does the on-call story look like for the warehouse — runaway queries, cost spikes, late jobs?"
8. "What's a recent project the data team got wrong, and what changed because of it?" (culture signal)

Don't ask about salary or remote in technical rounds. Save for recruiter / hiring manager.

Your closing answer

When asked "anything else you want to add?" — have one more substantive thing:

This acknowledges the gap, reframes around your strength, asks a smart calibration question.

State management — the meta-skill

• Between rounds: stand up, water, breathe, reset face. Do not replay the round.
• After all rounds: write down 5 questions they asked and 3 things you said well. Input for the next stage.
• Don't try to remember every question — pick highlights.

If something goes badly

It will. One SQL query will compile wrong. One blank moment.

• Name it briefly: "Let me come back to that — I want to give you a better answer than I started." Then pivot.
• Don't dwell. The interviewer's memory is dominated by recency. Strong answer in question 9 erases mediocre answer in question 4.
• Don't apologize twice. Once is composure; twice is anxiety leaking.

After the interview

• Send a short thank-you email within 24 hours. Three sentences. Reference one specific thing they said. Don't restate your case.
• Note things you'd answer differently. Adjust this folder.
• If you advance: schedule the next round, drill again, iterate.

The one mantra

Read it ten times before you start the call. Then go.

✓ Check yourself

Answer from memory before opening each one.

1. A dedup keeps ROW_NUMBER() = 1 ordered by updated_at DESC, and two rows share the latest timestamp. What do you say about this, and what do you add?

   Show answer

   Say that ties make the winner arbitrary, so two runs can keep different rows. Add a final unique column to the ORDER BY so the result is deterministic. See SQL gotchas to flag.

2. An interviewer asks, "What is the simplest way to dedup this table?" Why is SELECT DISTINCT the weak first answer?

   Show answer

   DISTINCT only removes rows identical in every column, so near-duplicates survive. Ask what the candidate key is, use ROW_NUMBER over it with a tiebreaker, and propose a uniqueness test upstream. See specific traps.

3. Dashboards are refreshed hourly and a stakeholder says they want them "real-time". How do you respond?

   Show answer

   Ask for the real latency requirement. Hourly stays unless the consumer needs sub-minute data; 5 to 15 minutes points to micro-batch. Streaming costs complexity in exactly-once handling, debugging and observability. See specific traps.

4. In a design round you are asked to design the data system for driver payouts at a ride-share company. What do you establish before naming any tool?

   Show answer

   The business questions, latency need and consumers. Then the entities (dimensions) and the events (facts) with the grain of each stated aloud. Tools, materialization, tests and monitoring come after. See the design framework.

5. An interviewer says your grain is wrong. You still believe it is right. What separates holding your ground from stubbornness?

   Show answer

   A concrete reason, usually a correctness or grain concern, and a visible willingness to concede the factor you had not weighed. Fold only when given a better argument. See when they push back.

6. Before coding a live SQL problem you restate the schema and walk a tiny example. What does the example catch that the schema alone does not?

   Show answer

   Misreadings of the output grain and counting rules, such as whether to count rows or distinct users. It also gives you a test input to check your query against line by line. See the SQL live coding framework.

Takeaway

• The night before is for sleep and setup; warm up with one window-function problem and stop cramming.
• In a SQL round: restate the schema, clarify grain, work a tiny example, name the pattern, write CTEs, narrate, test, and flag edge cases.
• Flag the gotchas before you are asked: NULLs, fanout, time zones, ties, frame defaults and division by zero.
• A design answer runs in order: goal, entities and events with grain, data flow, materialization, tests and monitoring, then trade-offs.
• When you do not know something, state the boundary of what you know and how you would extend it; never bluff.
• Hold a position when you have a correctness reason, and concede the factor you had not weighed.
• A bad answer is recoverable: name it once, pivot, and do not replay the round.

That completes the preparation. For more SQL creation, debugging and pandas drills, continue with the sibling guide. → Senior Data Analytics Engineer