ADM Associates Software Engineer Interview Guide

The 60-second brief

Candidates describe ADM Associates as an energy consultancy that evaluates energy-efficiency programs funded by utilities and governments, a discipline known as Evaluation, Measurement & Verification (EM&V). The posting titled Software Engineer or Engineer I (Data & Modeling focus) is described as data and modeling work in R and Excel: estimating the energy savings of efficiency measures and reporting the results to project leads. Postings are listed in Reno, Kansas City or remote. Field postings, such as the New Jersey Field Engineer role, add on-site work with measurement equipment.

This guide is for candidates interviewing for the analytical Software Engineer or Engineer I (Data & Modeling) posting at ADM Associates. It covers the three reported stages, the technical and domain questions candidates report (R, Excel, regression, HVAC, motors, energy modeling software), the behavioral questions, and a 7-day plan. Where a field posting changes the picture (safety, instrumentation, travel), the relevant rounds flag it.

Candidates report three stages spread over roughly 3-5 weeks: Initial Screening, Technical Interviews and Behavioral Questions. The technical stage is described as practical data application rather than algorithm puzzles.

Focus areas

• Energy Efficiency
• Data Analysis
• Statistical Modeling

Overview

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.

The ADM Associates interview process

Candidates describe three stages over roughly 3-5 weeks. Treat the order and format as a typical flow rather than a fixed script, and confirm the details with the recruiter for your posting.

Initial Screening

Candidates 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

The 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

Candidates 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.

Where candidates lose points

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.

Questions & practice

Find overlapping job attempts and peak concurrency from lease records

Medium · sweep line · intervals · leases

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

1. 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.
2. 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.
3. 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.
4. 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

Hard · reconciliation · range hashing · throttling

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

1. 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.
2. 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.
3. 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.
4. 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

Easy · hashing · at-least-once · aggregation

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

1. 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.
2. 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.
3. 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.
4. 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

Hard · denormalisation · online migration · backfill

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

1. 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.
2. 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.
3. 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.
4. 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

Medium · composite index · query plans · sort elimination

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

1. 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.
2. 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.
3. 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.
4. 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."

Medium · Technical & Data Proficiency

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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."

Medium · Technical & Data Proficiency

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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."

Medium · Domain Knowledge & Engineering

Approach

1. 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.
2. 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.
3. 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.
4. 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?"

Medium · Domain Knowledge & Engineering

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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?"

Medium · Domain Knowledge & Engineering

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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?"

Medium · Domain Knowledge & Engineering

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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

Medium · lock contention · ddl · migrations

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

1. 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.
2. 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.
3. 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.
4. 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?

Suggested practice checklist

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.

Day 1 — Map 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.

Day 2 — R 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.

Day 3 — Weather-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.

Day 4 — Excel 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.

Day 5 — HVAC, 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.

Day 6 — Behavioral 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.

Day 7 — Full 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.

Behavioural preparation

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.

1. "Describe a time you found an error in a dataset. How did you identify it and fix it?"
2. "How do you ensure your analysis is error-free before submitting it?"
3. "Describe a complex technical finding you had to explain to a non-technical audience."

"How do you handle data gaps when analyzing time-series energy data?"

Medium · Technical & Data Proficiency

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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?"

Medium · Technical & Data Proficiency

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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."

Medium · Behavioral & Situational

Approach

1. 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).
2. 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.
3. 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.
4. 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.
5. 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?"

Medium · Behavioral & Situational

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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?"

Medium · Behavioral & Situational

Approach

1. 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.
2. 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.
3. 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.
4. 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.
5. 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?