Interview guides · Excelsior University

Excelsior University Data Scientist Interview Questions & Guide 2026

Timed data scientist practice for Excelsior University: short SQL and probability sets run end to end, then scored against a rubric.

The 60-second brief

The Data Scientist role at Excelsior University serves as a bridge between complex institutional data and actionable academic strategy. As an institution dedicated to adult learners and flexible education, Excelsior University relies on these professionals to extract insights that improve student retention, optimize enrollment marketing, and refine academic programs. You will be instrumental in transforming raw data into the "why" behind student success, directly impacting the university's mission to provide accessible, high-quality education.

If the team owns experimentation, expect depth past a two-sample test: minimum detectable effect and its roughly inverse-square-root dependence on sample size (holding power, significance level and variance fixed), variance reduction from pre-period covariates, interference between units, and when a sequential design is the right call.

Focus areas

Align cohorts to term weeks, not calendar weeks
Read item difficulty and discrimination before trusting accuracy
Cluster experiments at class or school level

Overview

In this position, you will operate within specialized units such as Academic Affairs or Marketing and Enrollment Management. Whether you are developing predictive models for student outcomes or evaluating the effectiveness of outreach campaigns, your work will directly inform high-level decision-making. You can expect a collaborative environment where technical rigor meets a deep commitment to social mobility and student-centric outcomes.

Because Excelsior University serves a diverse, non-traditional student population, your ability to translate technical findings into narrative-driven insights for non-technical stakeholders is just as important as your statistical modeling skills.

The Excelsior University interview process

Excelsior University candidates report 4 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

Data Scientist covers at least four different jobs: experimentation, product analytics, causal work on observational data, and applied modelling that ships into a system. A screening call is the cheapest place to find out which of them is being hired for, and doing that diagnosis openly reads as senior rather than fussy. Ask what the last few pieces of work on the team actually were, and roughly how a week splits between querying, modelling and stakeholder time. Then say which parts of that you have done and which you have not. Claiming the whole range is the fastest way to be caught one round later.

What to demonstrate

Whether you can distinguish the flavours of the role and locate your own experience inside one of them honestly
Whether you name what you have not done instead of stretching to cover every line of the posting
Whether your hard constraints (notice period, location, work authorisation, level) surface now rather than at offer stage

How to prepare

Map the last two years of your time into rough percentages across query writing, experiment design, modelling and stakeholder work, so a question about scope has a real answer
Mark every responsibility in the posting as done, adjacent or new, and prepare one sentence for each adjacent item naming the closest thing you have actually built
Decide which logistics are non-negotiable before the call so you can state them in one sentence rather than negotiating live

Technical Assessments

Before anything else, this round is a reading test. You are given a small schema and a question phrased in business language, and most of the difficulty sits in the gap between them. Who counts as an active user, does a refunded order still count as an order, is that date column an event time or a load time. Weak answers start typing immediately and compute something precise about the wrong population. Strong ones pin the definition in one sentence, name the column that encodes it, then write the query. On a timed assessment with nobody to tell, write the definition in a comment anyway.

What to demonstrate

Whether an ambiguous term becomes a specific column and filter before any computation happens
Whether you read the schema for keys and cardinality rather than only for column names
Whether the result answers the question at the grain it was asked at, per user or per session or per day

How to prepare

Take three metrics you already use and write down the exact filter and exact grain behind each, then practise stating one of them in a single sentence out loud
On a schema you have never seen, spend the first minute writing what one row of each table means and which key it is unique on, then predict which joins can duplicate rows
Rehearse a version where the definition changes halfway through, and edit the query you have instead of starting over

Multiple Rounds

An extra round usually exists because something is still open after the standard loop: a skill the earlier interviews did not sample, a level decision, or two interviewers who disagreed. It is rarely a rerun of what you already did well. Ask the recruiter who you are meeting, what function they sit in, and how long the session runs. That is an ordinary scheduling question, and the answer changes what you should prepare. What separates a strong candidate here is treating the round as a fresh evaluation with its own bar, rather than assuming earlier performance carries you through or sinks you.

What to demonstrate

Whether you can answer well on ground the earlier rounds did not cover, without leaning on what you already said to someone else
Consistency of the facts in your stories: the same sample size, timeframe, team size and scope of your own role as in earlier conversations
How you handle an unfamiliar format live, including whether you ask what kind of answer is wanted before producing one

How to prepare

Ask the recruiter for the interviewer's function, the length, and whether to expect a coding surface, a discussion, or a presentation. Preparing for a 30 minute conversation with a partner team is not the same work as preparing for a 60 minute technical block.
Write out what each earlier round actually covered, then list the two or three areas nobody probed. That gap is the most likely subject of the extra round.
Re-read the numbers in the project stories you have already told, so a second telling does not quietly contradict the first.

Case Studies

A case round is decided by whether you leave the interviewer with a recommendation, not by how much analysis you narrate on the way there. The prompt is open on purpose, so the first job is to convert it into a decision someone could act on: ask what would be done differently depending on the answer. From there name the quantity that would settle it, state the assumptions you need, and commit. Candidates who cover more ground than anyone expected and still end on "it depends" score below candidates who scoped narrowly and said what they would do.

What to demonstrate

Whether the version of the question you choose to answer is genuinely narrower than the prompt and still worth answering
Whether the recommendation arrives as an action with a number attached, rather than as a summary of what you looked at
Whether assumptions are stated at the moment you rely on them, instead of collected into a disclaimer at the end
Whether you notice when a branch you are exploring would not change the decision either way

How to prepare

Take six open prompts and write only the scoping move for each: the one-sentence question you would actually answer and the decision it feeds. Give yourself three minutes per prompt and stop there.
Put a five-minute warning into every practice case and force a closing statement that names the action, the result that would justify it, and the result that would reverse it.
Record one case and count how long you talked before naming a measurable quantity. Past roughly five minutes, what you are calling scoping is narration.

Where candidates lose points

Pre/post gain studies that select on low pretest scores manufacture improvement.

Any measure with reliability below 1 produces regression to the mean, so a group chosen for scoring in the bottom quartile will score higher on retest with no intervention at all. The apparent gain scales with measurement error, which for a short quiz is large. The fix is a control group selected by the identical rule, or a design that models the pretest as a covariate rather than as a selection filter.

Adaptive item selection holds observed accuracy flat by construction.

A selector targeting a fixed success probability, say 70%, will keep measured first-attempt accuracy near 70% whether learners are improving or not, because as theta rises the engine simply serves harder items. Reporting 'accuracy improved 3 points' on adaptive content therefore usually means the selector got more conservative, not that anyone learned more. Accuracy is only interpretable on a fixed form with unchanged item versions, which is why the first-attempt accuracy metric above carries both restrictions.

Stopping an experiment the moment it crosses significance

Fix the sample size or duration before launch, or use a method built for continuous monitoring such as a sequential test, always-valid confidence intervals, or group-sequential boundaries. Repeatedly checking a fixed-horizon p-value against 0.05 pushes the real false-positive rate well above 5 percent.

Ending an analysis without a recommendation or next step

Close with what you would do and what would change your mind, stated as a condition you can check later. If the evidence is genuinely inconclusive, recommend the specific next measurement and say what it costs in time or exposure.

Questions & practice

Describe a time you had to select a machine learning algorithm for a predictive task; why did you choose it over alternatives?

Medium · machine learning and modelling

Approach

Check what information would not exist at prediction time, and exclude it.
Pick an evaluation metric that matches the cost of each error type, not a default.
Set a baseline first, so any model has something honest to beat.

Follow-up

How would you choose the decision threshold, and who owns that choice?
What would you monitor after launch to know the model is still valid?

Sessionise a raw heartbeat stream into activity rows

Hard · sessionisation · event streams · groupby · time series

You get a DataFrame heartbeats with columns learner_id, content_item_id, content_version_no, and ts (UTC timestamps, unsorted). Produce one row per learner per session per content item with started_at, ended_at, wall_seconds, and active_seconds. A session closes after 30 minutes with no heartbeat from that learner. active_seconds sums consecutive gaps inside a session, capping each gap at 120 seconds. Attribute each gap to the content item of the earlier heartbeat. State your rule for a gap of exactly 1800 seconds and for a session with a single heartbeat.

Approach

Sort by learner_id then ts, and compute the gap with a per-learner diff so the first heartbeat of each learner has no gap. A global diff lets one learner's last heartbeat set the next learner's first gap.
Flag a session break where gap > 1800 seconds, then cumsum that boolean within the learner to get a session index. Say whether exactly 1800 opens a new session; either choice is defensible but it must be stated because it moves the session count.
Each gap covers the interval before the current heartbeat, so attribute min(gap, 120) to the content_item_id of the previous row via shift, not the current row.
Group by (learner_id, session_index, content_item_id): sum the attributed capped gaps for active_seconds, take min and max ts for started_at and ended_at, and derive wall_seconds from those two.
Handle the degenerate group explicitly. A single heartbeat gives active_seconds 0 and wall_seconds 0. Dropping those rows quietly removes real opens and biases any completion or abandonment rate computed later.
Validate that within every session the summed active_seconds never exceeds the session's wall_seconds, which catches attribution and cap errors in one assertion.

Worked solution (35 min)

Sort by (learner_id, ts) and compute gap = ts.diff().dt.total_seconds(), then null the gap on the first row of each learner using a learner-change mask.
new_session = gap.isna() | (gap > 1800); session_index = new_session.groupby(learner_id).cumsum().
contribution = gap.clip(upper=120).fillna(0), attributed to the previous row's content_item_id using shift within the learner and session.
Aggregate by (learner_id, session_index, content_item_id) with sum for active_seconds and min and max of ts for the timestamps.
Assert per session that sum(active_seconds) <= (max(ts) - min(ts)) in seconds, and print the count of single-heartbeat groups.

Expected result: A frame keyed by (learner_id, session_index, content_item_id) where active_seconds equals the sum of min(gap, 120) over consecutive heartbeats with the first heartbeat contributing zero, wall_seconds equals ended_at minus started_at, and sum(active_seconds) <= wall_seconds holds for every session.

Verify it yourself

Heartbeats at 0, 60, and 120 seconds on one item give one session with wall_seconds 120 and active_seconds 120.
Heartbeats at 0 and 3600 seconds give two sessions, each with wall_seconds 0 and active_seconds 0.
Inserting a 300-second gap inside a session adds exactly 120 to active_seconds, not 300, while wall_seconds rises by the full 300.

Follow-up

The client emits a heartbeat every 60 seconds on desktop and every 15 seconds on a managed device fleet. What does the 120-second cap do to the comparison between those two populations?
Your reconstructed active_seconds disagrees with the stored column by 4 percent overall. How do you localise the disagreement rather than argue about the total?
A learner has two tabs open on different items at once. What does your output claim, and what should it claim?

Bootstrap a clustered interval for minutes per mastery event

Medium · bootstrap · ratio estimator · clustering · confidence intervals

You have weekly with section_id, learner_id, active_minutes, and mastery_events for one ISO week. The guardrail metric is total active_minutes divided by total mastery_events across the population, a ratio of sums rather than a mean of per-learner ratios. Write a cluster bootstrap in numpy: resample sections with replacement, recompute the ratio, and return a 95 percent percentile interval from at least 2,000 replicates. Also return the interval you get resampling learners, and state which one you would report and why.

Approach

Reduce to a section index first: per section, the total active_minutes and the total mastery_events. Because the statistic is a ratio of sums, a replicate needs only those two totals, which makes thousands of replicates cheap and keeps the resampling unit explicit.
Resample section indices with replacement up to the original section count, sum both totals over the resampled sections, then divide. Never average per-section ratios: that is a different estimand and it is dominated by whichever sections happen to be small.
Guard the denominator. A replicate drawing only sections with zero mastery events is undefined. Count how often that happens and report it rather than dropping those replicates silently, because a high rate means the weekly window is too short for this metric and the interval is not the real problem.
Take the 2.5th and 97.5th percentiles of the replicate ratios. Percentile intervals are adequate here only while the replicate distribution is roughly symmetric; check that before reporting, and move to BCa if it is skewed.
Repeat with learners as the resampling unit and compare widths. Report the section interval. The learner interval understates width by roughly the square root of the design effect, and it is the one that will be quoted back at you when the guardrail later moves.

Follow-up

Sections range from 8 to 60 learners. Does resampling sections with replacement bias the point estimate, the interval, or neither?
How does the interval change if you resample learner-weeks over four weeks instead of one, and what new assumption does that introduce?
The interval comes back wide enough to contain both a 10 percent improvement and a 10 percent regression. What do you tell the team to do?

Deduplicate retried submissions before computing first-attempt accuracy

Medium · window functions · deduplication · fan-out

A client retry bug wrote duplicates into fct_assessment_response(response_id, learner_id, content_item_id, content_version_no, attempt_no, submitted_at, is_correct, max_points, scoring_mode): the same learner, item, version and attempt_no appears two or three times, submitted_at within two seconds, different response_id. Deduplicate keeping the earliest row per logical attempt, then compute 28-day first-attempt accuracy over items whose course has dim_course.uses_adaptive_selection = FALSE and whose (content_item_id, version_no) did not change inside the window. Report how many rows the dedup removed and its share of raw rows.

Approach

Rank with ROW_NUMBER() OVER (PARTITION BY learner_id, content_item_id, content_version_no, attempt_no ORDER BY submitted_at, response_id) and keep rn = 1. Including response_id in the ORDER BY makes the survivor deterministic when submitted_at ties, which it will at second resolution.
Validate the duplicate definition before deleting anything. Count partitions with rn > 1 and inspect MAX(submitted_at) - MIN(submitted_at) inside each. A spread of minutes is a genuine re-attempt the client failed to increment attempt_no for, and dropping it is data loss rather than dedup.
Apply the version restriction by excluding content_item_id values that show more than one distinct content_version_no among responses in the window, or whose dim_content_item.published_at falls inside it. An item edited mid-window mixes two different questions into one accuracy figure.
Filter attempt_no = 1 AND is_correct IS NOT NULL. Treating NULL is_correct as incorrect biases accuracy down by exactly the unscored rubric share, and that share is not stable week to week.
Report accuracy with its denominator, plus removed_rows and removed_rows / raw_rows, so the size of the bug is visible next to the number it corrupted.

Worked solution (25 min)

Run the ROW_NUMBER over the raw table and count rows with rn > 1, grouped by the within-partition submitted_at spread in seconds.
Materialise the deduplicated CTE and record removed_rows.
Build the eligible-item CTE from the version-change rule and the uses_adaptive_selection = FALSE join.
Compute AVG(is_correct::int) over attempt_no = 1 rows on eligible items, and report it twice, once with and once without the version restriction.

Expected result: A deduplicated set where COUNT(*) equals COUNT(DISTINCT (learner_id, content_item_id, content_version_no, attempt_no)), a removed-row count with its share of raw rows, and one accuracy figure with its denominator over surviving first attempts on eligible items.

Verify it yourself

Re-running the ROW_NUMBER over the deduplicated set produces no rn > 1.
Every removed row sits inside a partition whose submitted_at spread is under the two-second threshold. A removal outside it means the partition key is wrong.
If the version restriction moves accuracy by more than a point or two, one edited item carries a large share of the responses. Confirm which item that is before publishing either number.

Follow-up

attempt_no is 1-based within learner and item, not within item version. If an item is edited between a learner's first and second attempt, which row is the first attempt on the new version, and does the metric want it?
The retry bug fires more often on poor connections. If that population differs in accuracy, what does silently dropping their duplicates do to the number?
How would you make this query cheap to leave in place after the bug is fixed, so it costs nothing on a clean table?

Verified mastery events with a delayed retention check

Medium · window functions · self-join · cohort definition

From fct_assessment_response(response_id, learner_id, objective_id, content_item_id, attempt_no, submitted_at, scored_at, score_points, max_points, scoring_mode, mastery_state_after, mastery_prob_after), count verified mastery events for one calendar month. A mastery event is the first time a (learner_id, objective_id) pair reaches mastery_state_after = 'mastered'. It is verified when a later response on the same objective, submitted at least 7 days after the crossing, scores at or above 0.8 of max_points. Return verified events, eligible events, the verified rate, and the share of crossings with no eligible check.

Approach

Find the first crossing with ROW_NUMBER() OVER (PARTITION BY learner_id, objective_id ORDER BY submitted_at, response_id) over rows where mastery_state_after = 'mastered', keeping rn = 1 across all time, then filtering that result to the month. Filtering to the month first would count a re-mastering after a lapse as a fresh event.
Attach the check with a LATERAL or correlated subquery selecting the earliest response on the same (learner_id, objective_id) with submitted_at >= crossing_at + INTERVAL '7 days'. Take the earliest, not the best: best-of turns the metric into did they ever eventually pass, which only rises as time goes by.
Score the check as score_points >= 0.8 * max_points, guarding max_points > 0 and requiring score_points IS NOT NULL. A rubric row still awaiting grading has scored_at IS NULL and must be excluded from eligibility rather than counted as a failure.
Set the denominator to pairs with an eligible check, and report eligible / crossings separately. That coverage share is the honest caveat and it falls for objectives that sit at the end of a course.
State the reporting lag the definition forces: the month cannot close until the last day's crossings have had 7 days plus the rubric scoring lag to produce a check.

Follow-up

A learner who never returns to an objective has no eligible check. Excluding them assumes that missingness is unrelated to retention. How would you test that assumption with the data you have?
If the engine team lowers the mastery threshold, this metric's numerator and denominator both move. Which guardrail catches that, and what would you watch on the item difficulty distribution?
Objectives late in a course get fewer checks. How do you stop that from making late objectives look systematically better or worse?

Explain the difference between correlation and causation in the context of student retention metrics.

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Restate the decision this analysis has to support, and who acts on the answer.
Name one primary metric, then the guardrail that stops it being gamed.

Follow-up

What would you do if the primary metric and the guardrail moved in opposite directions?
How would you detect that the metric is being gamed rather than genuinely improving?

What statistical methods would you use to measure the effectiveness of a new marketing initiative?

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
Say what you would check first and why it is the highest-information step.
State your assumptions explicitly before working the problem.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

How do you ensure your models are interpretable for academic leadership?

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
State your assumptions explicitly before working the problem.
Say what you would check first and why it is the highest-information step.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

Hints raise completion and lower verified mastery

Medium · metric conflict · mastery · clustering

Free hints are added to practice items. In a section-randomised test, unit completion rose from 64% to 71% of enrollments while verified mastery rate among completers fell from 44% to 38%. Fields available: fct_assessment_response.hint_count, attempt_no, is_correct, response_seconds, mastery_state_after, plus the delayed retention check behind verified mastery. Declare a primary metric and a guardrail, state the ship rule you would have written before the test, and compute whether the change is net positive per 1,000 enrollments.

Approach

Notice first that the guardrail as stated is conditioned on completion, which the treatment moves. Conditioning on a post-treatment variable opens a selection path, because both the hint change and learner ability affect who completes, so part of the 44 to 38 drop is composition rather than harm to any individual learner.
Replace the conditional pair with one unconditional composite: verified mastery events per enrollment. It multiplies the two stages, does not condition on anything the treatment touched, and is the quantity the product is sold on.
Compute it. Treatment 0.71 x 0.38 = 0.2698, control 0.64 x 0.44 = 0.2816, a difference of -11.8 verified mastery events per 1,000 enrollments, about -4.2% relative.
State the conflict honestly rather than resolving it by metric choice: hints trade coverage for depth. Which side wins depends on whether the contract is renewed on completion reporting or on mastery evidence, and that is a decision for the person who owns the renewal, made before the test rather than after.
Fix the inference: assignment is at section level, so standard errors must be cluster-robust or come from a mixed model with a section random effect. At section size 25 and intraclass correlation 0.15 the design effect is 1 + 24 x 0.15 = 4.6 on variance, which is enough to flip a naive verdict.
Add a misuse detector so the mechanism is visible: share of correct first attempts with hint_count at or above cap and response_seconds below the item floor, which separates scaffolding from answer reveal.

Worked solution (25 min)

Write the composite definition: verified mastery events per enrollment, with the enrollment denominator fixed at randomisation.
Compute both arms: 0.71 x 0.38 = 0.2698 and 0.64 x 0.44 = 0.2816, giving 269.8 versus 281.6 per 1,000 enrollments.
Write the pre-registered ship rule with its non-inferiority margin on the composite and its positive-direction requirement on completion.
State the inference method (cluster-robust at section level, design effect 4.6 at m=25 and rho=0.15) and note that the point estimate above is not yet an interval.

Expected result: -11.8 verified mastery events per 1,000 enrollments (269.8 versus 281.6), a decision not to ship under a pre-registered non-inferiority rule on the composite, and an explicit statement that the per-completer rate is conditioned on a post-treatment variable and cannot carry the decision alone.

Verify it yourself

0.71 x 0.38 = 0.2698 and 0.64 x 0.44 = 0.2816; the difference is -0.0118 per enrollment.
The standard error method was named before any result was interpreted, and it is clustered at section.
You identified completion as post-treatment and said what conditioning on it does to the comparison.

Follow-up

Suppose the composite came back flat. What would you look at to break the tie?
How would you detect that hints are being used as an answer reveal rather than as scaffolding, using only the response columns listed?
What is the minimum detectable effect on the composite with 40 sections of 25 learners at a baseline of 0.28?

Scored submissions sag in the dashboard's final five days

Easy · reporting-lag · seasonality · right-censoring

A daily chart counts fct_assessment_response rows by DATE(scored_at). The last five days each sit below the one before, and every Saturday and Sunday is roughly half of a weekday. Columns available: response_id, learner_id, submitted_at, scored_at, scoring_mode, max_points, attempt_no. Say whether scored submissions are actually declining, produce the corrected series, and state the reporting lag the correction forces on this dashboard.

Approach

Rebuild the same count on DATE(submitted_at) over sixty days and overlay the two. scored_at lags submitted_at for rubric-scored work, so a scored_at series is right-censored at the recent end: the last days are unfinished, not falling.
Quantify the lag rather than asserting it. Compute percentiles of scored_at minus submitted_at split by scoring_mode; auto sits near zero and rubric_human carries the tail that produces the sag.
Take the rubric_human p95 lag as the close threshold and truncate or grey out the scored_at series at max(date) minus that threshold, so the chart cannot be read as a decline again.
Handle the weekly pattern separately from the lag: compare each day to the same weekday a week earlier, or report a trailing seven-day sum. In an academic product weekend volume is structurally a fraction of a weekday's and is not signal.
Restate the dashboard definition with the lag written on it, and name which of the two series answers which question.

Follow-up

What reporting lag would you publish, and what do you do when a rubric grading backlog makes the lag itself drift?
Which series belongs on an operational grading-capacity dashboard, and why is the other one the right basis for the learning metric?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

Your part, not the team's

Most data work is done by groups, so an interviewer has to work out which piece was yours. An answer that runs on 'we' for several minutes gets interrupted with a question about what you personally did, and by then the answer sounds defensive even when it is true. Mark your own contribution as you go, and name the parts that belonged to someone else instead of leaving them ambiguous. Keep a few specifics back as well, like the name of the metric or who actually objected, so a probe can be answered with something you had not already said.

Describe a time you had to explain a complex data finding to a stakeholder who lacked a technical background.

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
Close with what you would do differently, concretely.
Pick a story where you drove the decision, not one where you observed it.

Follow-up

How did you know the outcome was caused by your change?
What did you decide not to do, and why?

Tell us about a time you encountered a significant roadblock in a data project and how you resolved it.

Medium · behavioural and stakeholder questions

Approach

Name the disagreement or constraint, and how you resolved it with evidence.
Close with what you would do differently, concretely.
State the situation in two sentences and spend the rest on your reasoning.

Follow-up

What would you do differently if you ran that project again?
What did you decide not to do, and why?

How do you maintain data integrity and ethical standards when working with sensitive student information?

Medium · behavioural and stakeholder questions

Approach

Close with what you would do differently, concretely.
Quantify the outcome, including what you would not claim credit for.
State the situation in two sentences and spend the rest on your reasoning.

Follow-up

What would you do differently if you ran that project again?
How did you know the outcome was caused by your change?