This loop is closer to an analyst-engineer interview than to a software-engineering one. Candidates describe a role built on organising and cleaning large datasets in R and Excel, fitting statistical models and turning the output into energy savings estimates. Candidates also report that the technical stage is about practical data application, not LeetCode-style algorithm questions. So the useful preparation is hands-on: take a messy interval-meter file, make the timestamps regular, merge it with weather data, fit a regression, and explain the output in plain language.
The reported material has two technical halves. The data half covers how you organise a new dataset in R, how you handle gaps in time-series energy data, merging datasets with different timestamps, correlating outside air temperature with building load, Excel formulas and macros, and regression with R-squared. The domain half covers the refrigeration cycle and HVAC efficiency, the variables that drive a building's energy use, verifying that a new motor runs efficiently, and experience with energy modeling software such as eQuest. Candidates also mention EnergyPlus, lighting retrofits and comparing baseline energy use with post-installation use, which is the core idea of measurement and verification. Know the units cold: kW is power, kWh is energy, and therms measure gas.
The third part of preparation is communication. Candidates report that written reporting is a core deliverable and that the final stage involves senior engineering staff and questions about time pressure, multiple projects, mistakes and explaining findings to non-specialists. Bring a report or write-up you authored and stories that show decisions, not just effort. The 7-day plan below alternates between building small R and Excel exercises, rehearsing the domain answers aloud, and writing the behavioral stories down.
Initial Screening
reportedCandidates report that the opening screen covers their engineering or science background and their hands-on use of R, Excel and energy modeling tools such as eQuest or EnergyPlus. Approach it as a short, evidence-based tour of your background: for every tool you claim, have a specific project, the size of the data and what the work produced. Know which version of the role you are applying to, because the analytical Engineer I posting and the field postings differ on travel and residency.
What to demonstrate
- Candidates report questions on their engineering or physical-science background
- Hands-on experience with R and Excel, best shown through specific tasks rather than a list of tools
- Any exposure to eQuest, EnergyPlus or similar energy simulation software
How to prepare
- Write and rehearse a two-minute background pitch that links your degree to data work and to energy or building systems.
- List three R tasks and three Excel tasks you have actually done (merging, cleaning, regression, pivot tables, macros), each with dataset size and outcome.
- Say plainly which energy modeling tools you have used and which you have not; if eQuest was a course project, be ready to describe the model and what limited it.
- Check whether your posting is the analytical role (Reno, Kansas City or remote) or a field role that requires living in its service territory and travelling.
Technical Interviews
reportedThe reported technical questions cover regression, data quality checks and estimating savings for an energy measure, and candidates contrast this stage with algorithm-style coding interviews. The reported question list spans R data handling, Excel, regression, and HVAC and building-energy fundamentals. Prepare by working real exercises, not by memorising definitions: have a repeatable sequence for a new dataset, a worked weather-normalised regression, and a short, correct explanation of the refrigeration cycle and of how you would check a motor.
What to demonstrate
- Candidates report questions on organising, cleaning and merging data in R and Excel
- Candidates report regression questions, including how to read R-squared
- Candidates report questions on applying code to engineering problems; prepare by knowing what kW, kWh and therms represent
- Preparation target: basic HVAC, motor and lighting knowledge behind the data
How to prepare
- Say aloud, in order, how you organise a new dataset in R: import with explicit types, parse timestamps with a time zone, check structure and missing values, validate units and ranges, then save the cleaned data and the script.
- Rehearse the reported scenarios of merging two large datasets with different timestamps and correlating outside air temperature with building load.
- Build one end-to-end regression of daily kWh on heating and cooling degree days, and practise explaining R-squared and the residual checks.
- Review the refrigeration cycle, the variables behind a building's energy use, a motor efficiency check and a lighting retrofit savings estimate (kW saved times annual operating hours gives kWh).
Behavioral Questions
reportedCandidates describe a last stage of behavioral questions and conversations with senior engineering staff. The reported behavioral questions cover conflicting deadlines, a mistake found after submitting work, and explaining a data finding to a project manager who is not an expert in R. For field postings, candidates also mention questions on safety, instrumentation and willingness to travel. Prepare stories where you made the decision and can say what the result was.
What to demonstrate
- Candidates report questions on juggling several projects with competing deadlines
- Explaining technical findings to non-specialists, in speech and in writing
- Handling a mistake found after submitting work, as in the reported question
- For field postings, candidates mention safety, instruments and travel
How to prepare
- Write three stories (conflicting deadlines, a mistake found after submitting, explaining a finding to a non-expert), each with your decision and a concrete outcome.
- Pick a report, paper or write-up you authored and be ready to say who it was for and what you changed after review.
- Prepare your own checking routine for an analysis (independent recalculation, reconciling totals, peer review) so you can describe it specifically.
- If you are applying for a field role, review electrical measurement basics (volts, amps, power), site safety practice and your travel availability.
PracHub editorial advice for the preparation topics above.
Preparing for an algorithm interview and arriving without a worked R analysis
Candidates describe the technical stage as practical data application. Spend your time building one complete exercise: import an interval-meter file, regularise timestamps, merge with weather, fit a regression and explain it. Practise saying each step aloud.
Saying 'I remove the NAs' when asked about data gaps or outliers
Name the check that finds the problem, the rule you apply and why, and how you record it. Distinguish a missing reading from a true zero, a short gap from a multi-day outage, and say how you show the result does not depend on your fill method.
Quoting a high R-squared as proof the model is good
Pair R-squared with residual checks, adjusted R-squared or out-of-sample error, and a statement of what the model will be used for. For a baseline model, say how you would turn it into a savings estimate and its uncertainty.
Mixing up power and energy, or dropping units from the answer
State kW for demand and kWh for consumption every time, and keep therms for gas. In a lighting or motor example, convert watts to kW first (divide by 1000), then multiply by operating hours for kWh, writing the units at each step.
Telling a behavioral story with no decision of your own, or blaming someone else for the mistake
For the deadline and the mistake-after-submission questions, state what you chose, what you gave up, what you told others and what check you added afterward. Use 'I' for your own actions and be specific about the outcome.
Choose a category, try a prompt, then open its approach, worked solution or follow-up when you need it.
Find overlapping job attempts and peak concurrency from lease records
A day of job_run history yields about 50,000,000 attempt records: (job_run_id, job_type, attempt, started_at, finished_at which is NULL when the worker died, lease_expires_at). Leases expire on a clock, so a job that outran its lease ran twice. Produce (a) every job_run_id whose attempts overlapped in wall-clock time and (b) the peak number of simultaneously running attempts per job_type with the minute it occurred. Target O(n log n). State how you treat a NULL finished_at and what clock skew does to your answer.
Approach
- Define the interval before sorting anything: an attempt occupies [started_at, COALESCE(finished_at, lease_expires_at)). finished_at is observed and lease_expires_at is only a promise, so every attempt without a finish contributes an estimate and the whole result is a lower bound on overlap rather than an exact count.
- For peak concurrency, sweep: emit 2n endpoints, sort by (timestamp, kind) with ends ordered before starts at equal timestamps, then walk the sequence maintaining a counter per job_type and record each type's maximum with its timestamp. O(n log n) dominated by the sort, O(n) space, or O(1) extra if the sort is external and the walk streams.
- For overlap detection, do not compare attempts pairwise. A single global sort by (job_run_id, started_at) gives both the grouping and the order; within a group, keep the maximum end seen so far and report an overlap exactly when the next start is less than that running maximum, which is one linear pass after the sort.
- Half-open intervals matter and are easy to get wrong: with closed intervals an attempt ending at the same millisecond another begins reads as concurrency two, and across 50,000,000 records that artefact swamps the real signal.
Follow-up
- A handler is not idempotent and you have found 400 overlapping jobs. Which of them actually caused damage, and what would you query to find out?
- Peak concurrency for one job_type is 4 against a configured cap of 4. Is the cap working, or is the data hiding attempts that never started?
Diff a projection against the primary without per-row point reads
The listing projection has drifted and some rows show a stale version. The primary holds 40,000,000 resource rows across 12,000 tenants while serving 1,200 writes and 14,000 reads per second. The obvious repair, reading each resource row and comparing its version against the projection, is correct and would eventually finish. Explain precisely why it is unacceptable here, then give a diff that finds the differing rows, state its complexity, and make it safe to run against a live primary. Replication lag is usually under 100 ms and is not bounded.
Approach
- Quantify the naive cost rather than calling it slow: 40,000,000 point reads at even 0.5 ms each is over five hours serialised, and the only lever is concurrency, which is exactly what you cannot spend. The primary's pool is sized for the write path, and 40,000,000 random reads evict the buffer cache that sustains the 85 percent cache hit rate, so the audit degrades the system it is auditing.
- Replace random access with one ordered pass per side. Both sides can be read in (tenant_id, resource_id) order, which is a sequential scan on each and a merge join in O(n) time and O(1) memory. For a dense diff that is the whole answer, and it reads the primary once instead of 40,000,000 times.
- For the expected sparse case, compare range hashes instead of rows: partition the key space, compute per range an order-independent aggregate over hash(resource_id, version), compare aggregates, and descend only into ranges that differ. With d differing rows and branching factor B, at most d ranges mismatch per level, so the drill-down examines O(d log_B(n/d)) ranges and reads full rows only in mismatching leaves.
- Aggregate with a sum modulo 2^64 or a multiset hash, never XOR. XOR is order-independent but self-cancelling, so two rows wrong in the same way, or a row duplicated on one side, leave the range aggregate matching and the range is declared clean.
Follow-up
- The diff reports 900 stale rows. How do you decide between patching those rows and rebuilding the projection from resource_revision?
- Same job, but the projection lives in a search index that cannot be scanned in key order. What changes?
Collapse a redelivered event batch into per-aggregate high-water marks
You drain a batch of up to 5,000,000 events, each (aggregate_id BIGINT, aggregate_version INT, event_type, payload). The log guarantees order within one aggregate only; the batch merges 64 partitions, and a relay failover has redelivered a range, so an older version for an aggregate can appear after a newer one. Given a map of last_applied_version per aggregate, produce the events worth applying, at most one per (aggregate_id, version), plus the count discarded. Target O(n) time. State the memory for 2,000,000 distinct aggregates and what you do when it does not fit.
Approach
- One pass, one hash map from aggregate_id to the highest version kept, and a discard counter. An event whose version is at or below last_applied_version for its aggregate is dropped without further work, which is the whole reason the event carries its version rather than a delta. O(n) expected time, O(d) space in distinct aggregates.
- Keep the maximum, never the last occurrence. The redelivered range means the final appearance of an aggregate in the batch can be an older version than one seen earlier in the same batch, so last-wins applies stale state over newer state and the projection regresses with no error anywhere.
- Cost the memory instead of calling it large: an 8-byte key plus a 4-byte version is 12 bytes of payload, and an open-addressed table held at a 0.7 load factor costs roughly 17 bytes per entry before per-slot metadata, so 2,000,000 aggregates is tens of megabytes in a native layout and several times that in a runtime that boxes both key and value.
- If the distinct set exceeds memory, partition on hash(aggregate_id) mod P and reduce each partition independently. Every event for one aggregate hashes to the same partition, so the per-partition result is exact and the merge is concatenation rather than a second reduction.
Follow-up
- The payload is a patch rather than a snapshot, so applying only the highest version loses the intermediate changes. What changes in your reduction?
- How do you detect that version 7 arrived while version 6 was never delivered, and what should the consumer do about the gap?
Denormalise tenant onto revisions and backfill it live
resource_revision (revision_id, resource_id, version, actor_user_id, change_kind, patch, request_id, created_at) has 400M rows and no tenant column; tenant_id lives only on resource. Two reads need it: a tenant-scoped audit feed ordered by created_at DESC, and an offboarding purge. Both join back to resource today. Justify adding tenant_id to resource_revision against those two reads, name the anomaly the copy introduces and the constraint that prevents it, then give the ordered migration for a live table taking 1.2k writes/second — the lock each step takes, how the backfill is batched, and where each step stops being reversible. PostgreSQL 16.
Approach
- Justify from the access path rather than from taste. Without the column, the audit feed either scans resource_revision by created_at and discards other tenants' rows, or resolves the tenant's resource_ids first and probes with them — both proportional to the tenant's whole history rather than to one page. With (tenant_id, created_at DESC, revision_id DESC) it is a seek that stops at 50 rows, and the purge becomes a ranged delete instead of a join.
- Name the cost exactly: a second copy of a fact can disagree with the first. Make the disagreement unwritable rather than documented — add UNIQUE (resource_id, tenant_id) on resource so it can serve as a foreign-key target, then FOREIGN KEY (resource_id, tenant_id) REFERENCES resource (resource_id, tenant_id) on the revision table. A revision can then only ever carry its parent's tenant.
- Step one, expand: ALTER TABLE resource_revision ADD COLUMN tenant_id BIGINT NULL, with no default, so it is a catalogue change and no rewrite. It still needs ACCESS EXCLUSIVE for an instant, and that instant queues behind the longest open transaction on the table while every later query queues behind it — set lock_timeout to 2s and retry rather than wait.
- Step two, dual-write: deploy the writer that populates tenant_id on every new revision while reads still use the join. Reversible by redeploying the previous build, because nothing reads the column yet.
Follow-up
- The backfill is half finished and a rollback is required. What state is the table in, and what does the previous build do with a half-populated column?
- How do you verify the backfill actually finished, given rows are still being inserted while it runs?
Explain why the owner filter ignores the listing index
The only index on resource is (tenant_id, status, updated_at DESC, resource_id DESC). A new endpoint returns one user's resources across all statuses, newest created first: WHERE tenant_id = $1 AND owner_user_id = $2 ORDER BY created_at DESC LIMIT 20. On a tenant with 2M rows it takes 900 ms and EXPLAIN shows a sort above a large scan. Explain precisely why the existing index cannot serve it, give the index that can, and state which of these the new index still will not help: owner_user_id alone across tenants; the same query ordered by updated_at. PostgreSQL 16.
Approach
- Separate the two jobs an index does. For filtering, a composite btree is seekable only on a left prefix, so with no predicate on status the scan can at best range over tenant_id and test owner_user_id per row; PostgreSQL 16 has no btree skip scan to jump the unconstrained column.
- For ordering, the index is sorted by (status, updated_at) within a tenant and not by created_at, so the LIMIT cannot stop early: every matching row is read and then sorted. That is the 'Sort Method: top-N heapsort' line, and it is why the plan reads 2M rows to answer with 20.
- Derive the replacement from the access path — equality, equality, then the ordering column: CREATE INDEX CONCURRENTLY ON resource (tenant_id, owner_user_id, created_at DESC). The scan seeks to the (tenant, owner) range and walks 20 entries in order, so the Sort node disappears along with the row-read.
- Treat INCLUDE (title, status) as conditional, not free. An index-only scan still visits the heap for any row whose page is not marked all-visible, so on a table taking 1.2k writes/second the win depends on autovacuum keeping the visibility map current, and the wider index costs more on every insert.
Follow-up
- 90% of rows are status='active'. Would a partial index WHERE status = 'active' change your answer, and for which of the three queries?
- A dashboard runs this for 40 owners in one page load. What changes about the design?
"Walk me through how you organize a new dataset in R before you begin analysis."
"Walk me through how you organize a new dataset in R before you begin analysis."
Approach
- Load with explicit types instead of guessing: readr::read_csv(col_types = ...) or data.table::fread, then run str()/dplyr::glimpse(), summary() and dim() to confirm columns, units (kW vs kWh, therms) and row counts match what the data owner described.
- Parse timestamps with lubridate (ymd_hms with an explicit tz). Without one, daylight-saving changes leave a missing hour in spring and a repeated hour in autumn in local-time meter data.
- Reshape to a tidy layout, one row per meter per interval: janitor::clean_names() for consistent names, tidyr::pivot_longer() when the file has one column per day or per meter.
- Run integrity checks before any analysis: duplicate keys (count(meter_id, timestamp) |> filter(n > 1)), colSums(is.na(df)) per column, range checks for negative or impossible readings, and anti_join() against site metadata to find meters with no matching building.
- Never edit the raw file. Every cleaning step lives in a script, I log row counts after each step, and I save the processed table separately (.rds or parquet) so a reviewer can rerun it end to end.
Follow-up
- How would you merge 15-minute interval data with hourly weather data: aggregate to a common interval, or a data.table rolling join (roll = TRUE carries the last observation forward)?
- The file is too large to fit in memory. What changes: data.table, arrow::open_dataset, or chunked reads?
- How do you record what you removed or changed so a reviewer can audit the cleaning?
"Describe a complex formula or macro you created in Excel to solve a problem."
"Describe a complex formula or macro you created in Excel to solve a problem."
Approach
- Pick one real workbook and tell it in order: the problem, why a pivot table or simple SUM was not enough, the formula or macro itself, and how I checked the result.
- Show you know the trade-offs between lookup functions. VLOOKUP needs the key in the leftmost column and defaults to approximate match unless the last argument is FALSE. MATCH also defaults to approximate (match_type 1), so INDEX/MATCH needs MATCH(key, range, 0) for an exact match. XLOOKUP is exact match by default. Both INDEX/MATCH and XLOOKUP keep working when columns are inserted.
- A typical energy example: SUMIFS of kWh by site ID and by a month range (start date >= and end date <) to roll interval data up to billing periods. Use LET to name intermediate values so the formula stays readable.
- For a VBA macro: loop over the workbooks in a folder and consolidate one sheet from each into one table. Write ranges without .Select, set ScreenUpdating to False for speed, and stop with a logged error rather than skipping a bad file silently.
- Edge cases: numbers or dates stored as text (SUMIFS silently ignores them), blanks counting as zero, and IFERROR hiding real lookup misses. I reconciled totals against a pivot table to catch these.
Follow-up
- How would a colleague maintain or extend this workbook after you leave the project?
- At what point would you move this logic out of Excel and into an R script?
- How did you confirm the output was correct, rather than just free of errors?
"Explain the basic refrigeration cycle and how it relates to HVAC efficiency."
"Explain the basic refrigeration cycle and how it relates to HVAC efficiency."
Approach
- Walk the vapor-compression loop in order. The compressor raises low-pressure refrigerant vapor to a high pressure and temperature. The condenser rejects heat to outdoor air or water and the refrigerant condenses. The expansion valve throttles it to low pressure and temperature. The evaporator absorbs heat from the space as the refrigerant boils.
- Define the efficiency metrics and how they convert: COP = cooling delivered / electrical input (same units); EER = Btu/h of cooling per watt, so COP = EER / 3.412; chillers are rated in kW/ton, where 1 ton = 12,000 Btu/h and kW/ton = 12 / EER; SEER is a seasonal version of EER.
- Explain why lift matters. The ideal cooling COP is T_cold / (T_hot - T_cold) in kelvin, so a larger gap between condensing and evaporating temperatures lowers efficiency. That is why performance falls on hot days, with dirty condenser coils or with the wrong refrigerant charge.
- Connect it to efficiency measures and data: higher-SEER replacements, variable-speed compressors and fans, economizers and condenser maintenance. In metered data, cooling kWh rises faster than linearly as outdoor temperature climbs.
Follow-up
- How does a heat pump use the same cycle for heating, and why can its heating COP exceed 1?
- How would you estimate annual savings from replacing a SEER 10 unit with SEER 16, e.g. capacity (Btu/h) x equivalent full-load hours x (1/SEER_old - 1/SEER_new) / 1000 for kWh?
- What would you look for in interval data to tell whether a unit is short-cycling?
"What are the primary variables you would need to model a building's energy usage?"
"What are the primary variables you would need to model a building's energy usage?"
Approach
- Group the variables. Weather: outdoor dry-bulb temperature, humidity, solar. Use and schedule: occupancy, operating hours, setpoints. Physical building: floor area, envelope R-values, window U-factor and SHGC, infiltration. Systems: HVAC type and efficiency, lighting power density (W/ft2), plug and process loads, fuel types.
- For a data-driven model, regress energy on degree days: HDD = max(0, base - daily mean temp) and CDD = max(0, daily mean temp - base), summed per billing period. Normalize by days in the period, because utility bills cover different numbers of days.
- Choose the balance-point temperature by searching candidate bases and keeping the one that fits best. Use a change-point model (flat baseload plus a heating and/or cooling slope) instead of forcing a straight line. Judge fit with CV(RMSE) and NMBE as well as R-squared, against the whole-building regression criteria in ASHRAE Guideline 14 or the project's M&V plan; those differ from the calibrated-simulation thresholds.
- Place the model in the IPMVP framework: Option A (retrofit isolation, key parameters measured), Option B (retrofit isolation, all parameters measured), Option C (whole facility, typically a billing or interval regression like this one) and Option D (calibrated simulation). Say which option fits the measure and why.
- Keep demand (kW) and energy (kWh) separate. For non-routine events, such as an occupancy shift or an added wing, that a weather-only model cannot explain, make a documented non-routine baseline adjustment rather than letting the change show up as savings or losses.
Follow-up
- The building's occupancy changed during the post-retrofit period. How do you keep the savings estimate fair?
- When would you use a physics simulation (Option D) instead of a regression on bills (Option C)?
- HDD and CDD are both seasonal. How do you handle their correlation with each other and with time?
"How would you verify that a newly installed motor is operating efficiently?"
"How would you verify that a newly installed motor is operating efficiently?"
Approach
- Start from the nameplate: rated hp, nominal efficiency (e.g. NEMA Premium), full-load amps, rated rpm and voltage. Those give the expected input: rated kW in = hp x 0.746 / efficiency.
- Measure with a true-RMS power meter or logger: voltage and current on each phase, power factor and real power. For a balanced three-phase load, P = sqrt(3) x V_line-line x I x PF.
- Estimate load fraction from measured kW against rated input, or with the slip method: load is roughly (synchronous rpm - measured rpm) / (synchronous rpm - nameplate rpm), where synchronous rpm = 120 x frequency / poles (1800 for a 4-pole 60 Hz motor). Read efficiency at that load from the manufacturer's part-load curve.
- Check what erodes efficiency in the field: voltage unbalance between phases, an oversized motor running at low load (efficiency and power factor both drop), misalignment and vibration.
- Log over a representative period to get operating hours and load profile, then compute savings: hp x 0.746 x load factor x hours x (1/eff_baseline - 1/eff_new).
Follow-up
- The motor runs on a VFD. What changes about the measurement, and why do harmonics require a true-RMS meter?
- You can only log for two weeks. How do you annualize the result defensibly?
- Measured kW is well above what the nameplate predicts. What would you check first?
"Have you ever used energy modeling software like eQuest? What were the limitations?"
"Have you ever used energy modeling software like eQuest? What were the limitations?"
Approach
- Be accurate about experience: if I have used eQuest, I name the building type, the wizard level (schematic or detailed design development) and what the model was for. If I have not, I say so and describe EnergyPlus or another tool I have used, mapped to the same concepts.
- Explain what eQuest is: a front end for the DOE-2.2 engine that simulates a year hour by hour from a weather file (typically TMY) and calculates loads, then systems, then plant.
- Name real limitations. DOE-2 runs those calculations in sequence with limited feedback, so zone loads do not respond when a system cannot meet them. HVAC choices are fixed system templates, and some newer systems such as VRF have no native type and need workarounds. Thermal zoning is often simplified.
- Compare with EnergyPlus: heat-balance method, sub-hourly time steps and flexible HVAC loops. In exchange it runs slower and takes longer to set up.
- Describe calibration: compare simulated energy and demand with utility bills or interval data, adjust uncertain inputs (schedules, infiltration, plug loads) rather than forcing a fit, and report CV(RMSE) and NMBE. ASHRAE Guideline 14 targets for a calibrated simulation are CV(RMSE) <= 15% and NMBE within +/-5% against monthly data, and CV(RMSE) <= 30% and NMBE within +/-10% against hourly data.
Follow-up
- How did you calibrate your model against metered data, and which inputs did you adjust?
- For estimating savings from one measure, when would you use simulation and when a regression on billing data?
- How would you run many parametric variations efficiently, inside the tool or by scripting input files?
Every query on one table stalls for forty seconds mid-deploy
During a release on PostgreSQL, every query touching resource times out for about 40 seconds and then recovers with no intervention. The release ran one migration, ALTER TABLE resource ADD COLUMN archived_reason TEXT, and the migration log shows it completing in 6 ms. Unrelated tables showed no change in error rate. Explain how a 6 ms statement caused a 40-second stall, give the ordered checks you would run on a live system to confirm it, and give the migration procedure that prevents a repeat.
Approach
- Separate the statement's duration from the lock's duration. ADD COLUMN with no default is a catalogue-only change and genuinely runs in milliseconds, but it requires ACCESS EXCLUSIVE, and it cannot acquire that until every transaction already touching the table has finished.
- Account for the queueing, which is the part that surprises people. A lock request that is waiting blocks later requests for conflicting modes behind it rather than letting them overtake, so one long-open transaction holds the DDL and the DDL holds all the traffic. The stall length is set by the longest open transaction, not by the size of the change.
- Confirm on a live system in this order: pg_stat_activity for that table ordered by xact_start, looking for the oldest transaction and specifically for state = idle in transaction; then pg_locks where granted = false to find the waiter; then join them on pid to name blocker and blocked. pg_blocking_pids() does that join for you and is the fastest single call.
- Prevent rather than merely time it better. Set lock_timeout to a second or two on the migration session so the DDL abandons the queue after a bounded wait and is retried, instead of holding it for as long as the oldest transaction lives. Be exact about what that buys: queries arriving during the wait still queue behind the pending ACCESS EXCLUSIVE request, so each attempt costs them up to one lock_timeout of added latency. The outage goes from 40 seconds to about one second per attempt, not to zero. Also run migrations away from deploy-time peaks, and put a statement timeout and an idle-in-transaction timeout on the analytics role that opens the long transactions.
Follow-up
- The same release also wants NOT NULL on that column. What is the sequence that gets there without a long lock?
- Your lock_timeout retry fails ten times in a row because the analytics transaction is always open. What do you change?
The plan alternates between hands-on R and Excel exercises, spoken practice of the HVAC and energy-modeling answers, and written behavioral stories, following the three reported stages and the reported question list.
Prepare, practise & reflect
One practical outcome each day. Spend longer where you need it.
0 / 7 done01Map the loop and your evidence
- Write the three reported stages (Initial Screening, Technical Interviews, Behavioral Questions) and note which posting you are applying to: analytical Engineer I or a field role.
- List three R tasks and three Excel tasks you have done, each with the dataset size and outcome, and note whether you have used eQuest or EnergyPlus.
- Write and say aloud a two-minute pitch linking your degree to data and energy work.
Deliverable: A one-page loop map, an evidence list of R, Excel and modeling tasks, and a written background pitch.
02R data handling and time-series gaps
- Take a public or personal interval-meter file and write an R script that sets explicit column types, parses timestamps with a time zone and checks structure, missing values and value ranges.
- Build the full regular timestamp sequence, find the gaps, and apply a written rule for each kind (short gap, multi-day outage, true zero, daylight-saving duplicate).
- Merge the meter data with hourly or daily weather data recorded at different timestamps and check row counts before and after the join.
Deliverable: A runnable R script plus a one-paragraph gap-handling policy and a description of how you organise a new dataset.
Practice prompt ↗Practice prompt ↗03Weather-normalised regression
- Compute heating and cooling degree days and fit daily kWh against them with lm(); read the summary and plot the residuals over time.
- Write a two-sentence explanation of R-squared, a note on adjusted R-squared and CV(RMSE), and one example of a misleading high R-squared.
- List the variables you would collect to model a building's energy use, mark which you could measure and which you would estimate, and note which IPMVP option (A, B, C or D) your model represents.
Deliverable: A one-page model write-up with the regression output, residual plot, plain-language R-squared explanation and variable list.
Practice prompt ↗Practice prompt ↗04Excel formulas, macros and a lighting retrofit estimate
- Build a workbook that turns interval data into monthly kWh with SUMIFS or a pivot table, and add one exact-match lookup (XLOOKUP, or INDEX with MATCH(key, range, 0)) or a small VBA macro that removes a repetitive manual step.
- Write a lighting retrofit savings estimate: kW saved = fixtures x (W_old - W_new) / 1000; kWh saved = kW saved x annual operating hours. Optionally add an HVAC interactive-effects factor, and a coincidence factor for peak-demand savings. List every assumption.
- Practise describing the workbook as a story: problem, formula or macro, what it replaced, and how you checked it.
Deliverable: A working workbook, a lighting savings calculation with units shown at every step, and a spoken description of the formula or macro.
Practice prompt ↗05HVAC, motors and energy modeling
- Explain the refrigeration cycle aloud (compressor, condenser, expansion device, evaporator) and link it to efficiency, for example how condenser fouling or low refrigerant charge raises energy use for the same cooling.
- Write how you would verify a newly installed motor: measured volts, amps and power compared with the nameplate, the loading level, and a before-and-after comparison.
- Write down what you built or would build in eQuest, two limitations you would name from your own experience, and how you would calibrate it against bills.
Deliverable: A one-page cheat sheet covering the refrigeration cycle, motor verification steps and eQuest experience with limitations.
Practice prompt ↗Practice prompt ↗Practice prompt ↗06Behavioral stories and the explanation to a non-expert
- Write bullet outlines for conflicting deadlines, a mistake found after submitting, and a data error you identified and fixed, each with your decision, who you told and the outcome.
- Rehearse explaining your day 3 regression result to a project manager in plain units, one chart and one stated limitation.
- Write down your personal checking routine for an analysis (independent recalculation, reconciling totals, peer review).
Deliverable: Three story outlines, a plain-language summary of your regression, and a written checking routine.
Practice prompt ↗Practice prompt ↗Practice prompt ↗07Full mock run
- Run the sequence aloud as one session: background pitch, organising a dataset in R, handling data gaps, regression and R-squared, one HVAC or motor question, then two behavioral stories.
- Record it, listen back, and mark every place you left out units, skipped the reason for a choice or lacked a concrete example.
- Fix the three weakest answers and say them again.
Deliverable: A recorded mock session with a list of three revisions applied.
Practice prompt ↗Practice prompt ↗Practice prompt ↗Practice prompt ↗Practice prompt ↗Practice prompt ↗Expand any day for tasks and deliverables. Your progress is saved on this device.
Candidates describe behavioral questions about time pressure, juggling several projects, correcting a mistake after submitting work, and explaining technical findings to people who do not work in R. Prepare stories where you made a decision and can state the outcome, and be ready to describe your own checking routine.
"How do you handle data gaps when analyzing time-series energy data?"
"How do you handle data gaps when analyzing time-series energy data?"
Approach
- Characterise before fixing. Build the complete regular timestamp sequence for the meter interval (for example every 15 minutes) and anti-join it to the data to count missing intervals, then look at where they cluster: one long outage, every night, one meter, or around daylight-saving changes. Also look for readings that are present but not real, such as repeated values or stuck zeros.
- Separate the cause. A communication dropout (consumption unknown), a logger or meter outage, a genuine zero because equipment was off, and a timestamp problem (a duplicated or missing hour at daylight-saving change, mixed time zones) each need a different treatment, so say how you tell them apart.
- Match treatment to gap length and purpose. Short gaps in a smooth variable such as outdoor temperature can be filled by linear interpolation with a maximum gap length (zoo::na.approx with maxgap in R). For interval energy, do not interpolate across long gaps; if the meter reports a cumulative register, difference the reads so totals stay correct; for gaps of a day or more, drop those days from the model or flag them as estimated.
- Protect the result. Do not fill gaps in the period whose savings you are measuring using the same model that produces the savings. Apply a completeness rule when aggregating to days or months, record every rule and the number of affected intervals in the report, and rerun with and without filled values to show the conclusion does not hinge on them.
- Anchor it in an example: name the dataset, the share missing, the rule you chose and what changed in the result. A rule with no example sounds recited.
Follow-up
- A meter was offline for three weeks of your baseline year. What do you do with that period?
- How do you tell a true zero from a missing value?
- How would you handle the duplicated hour at the end of daylight-saving time when merging with hourly weather data?
"What is your experience with regression analysis? How do you interpret an R-squared value?"
"What is your experience with regression analysis? How do you interpret an R-squared value?"
Approach
- Lead with one concrete use. For example, regress daily or monthly kWh on heating and cooling degree days (plus occupancy or production if available) to build a baseline, then predict what the building would have used after a retrofit and compare that to metered use. State the response, the predictors, the number of observations and how you validated it.
- Define R-squared correctly: the share of variance in the response that the fitted model accounts for, between 0 and 1 for ordinary least squares with an intercept. An R-squared of 0.85 means 85% of the variation around the mean is explained, not that 85% of predictions are right.
- Give its limits. R-squared never falls when you add a predictor, so compare adjusted R-squared or out-of-sample error. It says nothing about bias or causation, and two trending or seasonal series can give a high value for a spurious relationship. A modest R-squared with a clear, stable slope can still be useful.
- Name what you check beside it: residual plots for curvature, non-constant variance and autocorrelation (common in time series), coefficient confidence intervals, and error measures such as CV(RMSE) and NMBE for baseline models. In R this is lm(), summary(), plot(model) and predict() with intervals.
- If your experience is limited to coursework or one project, say so and describe exactly what you fitted and how you would apply it to building data.
Follow-up
- Your model has an R-squared of 0.95 but the residuals drift over time. What do you conclude?
- Why not simply choose the model with the highest R-squared?
- How would you turn the baseline model into a savings estimate, and how would you express its uncertainty?
"Tell me about a time you had to manage conflicting deadlines for multiple projects."
"Tell me about a time you had to manage conflicting deadlines for multiple projects."
Approach
- Choose a case with a real collision: at least two deliverables with different owners and dates, and say what was at stake on each (for instance a client report and an internal quality review).
- Show the triage as reasoning, not effort. List each task with its due date, remaining work, dependencies and the cost of slipping, note which pieces could run in parallel (a long script) and which blocked others, then explain the order you chose.
- Show early communication with the people affected. Bring options rather than a problem: for example, deliver one item on Tuesday and the other on Thursday, or ask a project lead to move a date or trim scope, instead of quietly missing one.
- Say what you protected and what you cut. Name the quality check you kept, and anything you automated or reused (such as a script for a repeated analysis) to save time.
- Close with the outcome and a change you kept, such as a shared task list or flagging conflicts earlier. The story that sinks this answer is 'I worked late and got it all done', with no prioritisation visible.
Follow-up
- What did you decide not to do, and did anyone disagree?
- What if two project leads each said their deadline came first?
- How did you keep time for checking your work inside that schedule?
"Describe a situation where you found a mistake in your own work after submitting it. How did you handle it?"
"Describe a situation where you found a mistake in your own work after submitting it. How did you handle it?"
Approach
- Choose a real error that reached another person, in an analysis, spreadsheet or report: not a typo, and not something you cannot discuss. Say how you found it: a re-check, a reviewer, or a total that failed to reconcile.
- Describe the first move: you told the recipient promptly, before you had the fix. State what was wrong, which numbers or conclusions it affected, and who might already have used them.
- Describe the repair: recompute, quantify how much the result changed, and send a corrected version with a clear note of what changed. If a decision depended on the old numbers, flag that directly.
- Explain the root cause and the safeguard. A unit mix-up between kW and kWh, a join on misaligned timestamps or a hard-coded cell range each point to a specific check, such as an independent recalculation, assertions in the R script, or reconciling totals back to the source.
- Keep it in first person with no blame on data or colleagues. Do not tell a story where the mistake was caught by luck and nothing changed afterward.
Follow-up
- How did the recipient react, and what would you do differently in the first hour?
- What check do you run now that would have caught it?
- When is a mistake small enough to correct without a formal note, and when must it be escalated?
"How do you approach explaining a technical data finding to a project manager who isn't an expert in R?"
"How do you approach explaining a technical data finding to a project manager who isn't an expert in R?"
Approach
- Start from the audience: what decision does the project manager face? Lead with the conclusion in plain terms and the units they use (for example, the measure saved about X kWh a year and how confident you are), before any method.
- Translate the statistics. Instead of R-squared, say that weather explains most of why usage moves from month to month; instead of a p-value, give a range with a lower and upper estimate. Use one chart, actual versus expected usage over time, and leave out code and console output.
- State the one or two assumptions or data limits the number depends on (a short baseline, filled gaps, an unusual occupancy period) and what would change it.
- Check understanding: ask them to say back what they would tell the client, invite questions, then follow up with a short written summary and keep the method in a technical appendix so a reviewer can reproduce it.
- Use a real example with the situation, what the manager needed, and what you changed after their feedback. Reciting a framework without an example is the common failure.
Follow-up
- The project manager thinks the savings look too high. How do you respond?
- How do you present a result that contradicts what the client expected?
- What belongs in the written summary and what in the technical appendix?
- 01
"Tell me about a time you had to manage conflicting deadlines for multiple projects."
- 02
"Describe a situation where you found a mistake in your own work after submitting it. How did you handle it?"
- 03
"How do you approach explaining a technical data finding to a project manager who isn't an expert in R?"
- 04
"Describe a time you found an error in a dataset. How did you identify it and fix it?"
- 05
"How do you ensure your analysis is error-free before submitting it?"
- 06
"Describe a complex technical finding you had to explain to a non-technical audience."
Is the Software Engineer role remote?
Candidates describe the Engineer I posting as listed for Reno, Kansas City or remote. Field-focused postings, such as the Field Engineer role in New Jersey, are described as requiring residence in the service territory because of regular travel to client sites. Confirm which posting you are in the loop for.
ADM Associates Software Engineer candidate reports ↗How much travel should I expect?
For the analytical Software Engineer or Engineer I role, candidates describe travel as minimal, mostly occasional site assessments. The Field Engineer role is described very differently: a large share of the schedule is spent travelling, mostly on day trips inside the service territory and sometimes into neighbouring states.
ADM Associates Software Engineer candidate reports ↗Which software tools should I be ready to discuss?
R and Excel are the data tools candidates mention most, including pivot tables, advanced functions and possibly VBA. For energy modeling, candidates mention eQuest and EnergyPlus as the tools to know or at least understand. If you have used none of the modeling tools, be honest and be ready to explain the underlying baseline-versus-post comparison.
ADM Associates Software Engineer candidate reports ↗Do I need a Master's degree?
The requirements candidates describe list a Bachelor's degree in Mechanical or Electrical Engineering (or a physical science for field roles) as required, with a Master's degree preferred for analytical roles. If you do not have one, make up the difference with concrete statistical work you can discuss, such as a regression you built and validated.
ADM Associates Software Engineer candidate reports ↗How many rounds are there in the ADM Associates Software Engineer loop?
Candidates report three stages over roughly 3-5 weeks: Initial Screening, Technical Interviews and Behavioral Questions. The loop section of this guide describes what candidates say about each and how to prepare.
ADM Associates Software Engineer candidate reports ↗Will there be LeetCode-style coding questions?
Candidates say no: they mention regression, data checks and savings estimates, not algorithm puzzles. Spend preparation time on R data handling, statistics, Excel and the energy fundamentals.
PracHub Software Engineer practice ↗How do I prepare if I have a software background but little energy knowledge?
Learn the vocabulary and physics that the reported questions rely on: kW versus kWh, therms, heating and cooling degree days, the refrigeration cycle, and how baseline energy use is compared with post-installation use. Then work one lighting retrofit estimate and one weather-normalised regression by hand so you can explain both without notes.
PracHub Software Engineer practice ↗Sources & methodology 3 sources ↗
No official company page is cited. Rounds and questions come from candidate reports and PracHub editorial material; each source shows the date it was read.
- 01ADM Associates Software Engineer candidate reports ↗
Company-reported rounds, questions and FAQ.
Candidate reports · Accessed 2026-09-22 - 02PracHub Software Engineer practice ↗
PracHub practice material, not company-reported.
PracHub page · Accessed 2026-09-22 - 03PracHub preparation framework ↗
PracHub preparation guidance.
PracHub page · Accessed 2026-09-22
Explore related engineering environments and compare the preparation focus.
Cognition Software Engineer Interview Guide
What Cognition asks Software Engineer candidates, from reported rounds and questions.
Vercel Software Engineer Interview Guide
Debugging practice for Vercel software engineer prep built on a reproduction you trust, narrowing to the smallest failing input, and changing one variable per run so the result stays attributable.
Together Ai Software Engineer Interview Guide
Failure-handling practice for Together Ai software engineer candidates: requests whose outcome a timeout leaves unknown, where a retry is safe, and deduplicating at the receiver instead of assuming exactly-once delivery.
Spotify Software Engineer Interview Guide
Timed practice for Spotify software engineer candidates who lose time to the clock: short problems taken end to end, then reviewed against a written rubric rather than a passing test run.