Technical Communication and Explanation Questions

The ability to explain technical concepts, architectures, designs, and implementation details clearly and accurately while preserving necessary technical correctness. Key skills include choosing and defining precise terminology, selecting the appropriate level of detail for the audience, structuring explanations into sequential steps, using concrete examples, analogies, diagrams, and demonstrations, and producing high quality documentation or tutorials. Candidates should demonstrate how they simplify complexity without introducing incorrect statements, scaffold learning with progressive disclosure, document application programming interface behavior and workflows, walk through code or system designs, and defend technical choices with clear rationale and concise language.

EasyTechnical

46 practiced

You observed an A/B test where variant increased clicks by 2% but decreased purchases by 4%. Draft a 150-word executive summary that states the top-line result, proposed next steps, and the key caveats stakeholders should be aware of.

EasyTechnical

48 practiced

Annotate the following stored procedure by adding concise comments for each line explaining purpose and effect. Then describe briefly what you'd include in an external README accompanying this procedure.

Procedure:

sql

CREATE PROCEDURE calc_monthly_revenue()
BEGIN
  INSERT INTO monthly_revenue (month, revenue)
  SELECT DATE_FORMAT(created_at, '%Y-%m-01'), SUM(amount)
  FROM orders
  WHERE status = 'complete' AND created_at >= '2024-01-01'
  GROUP BY DATE_FORMAT(created_at, '%Y-%m-01');
END;

Sample Answer

sql

CREATE PROCEDURE calc_monthly_revenue() -- define a stored procedure named calc_monthly_revenue
BEGIN -- start procedure body
  INSERT INTO monthly_revenue (month, revenue) -- insert results into monthly_revenue table (month, revenue columns)
  SELECT DATE_FORMAT(created_at, '%Y-%m-01'), SUM(amount) -- select month (first day of month) and total revenue for that month
  FROM orders -- source table containing order records
  WHERE status = 'complete' AND created_at >= '2024-01-01' -- only include completed orders from 2024-01-01 onward
  GROUP BY DATE_FORMAT(created_at, '%Y-%m-01'); -- aggregate totals per month
END; -- end procedure

README (brief):- Purpose: populates monthly_revenue with aggregated completed-order revenue by month starting 2024-01-01.- Preconditions: monthly_revenue table exists with compatible schema (month DATE or STRING, revenue DECIMAL). orders has created_at DATETIME, amount NUMERIC, status TEXT.- Usage: CALL calc_monthly_revenue(); schedule via job/cron for periodic refresh.- Idempotency: current implementation appends rows — recommend either TRUNCATE monthly_revenue before run or use INSERT ... ON DUPLICATE KEY UPDATE to upsert.- Permissions: requires INSERT on monthly_revenue and SELECT on orders.- Tests & validation: sample row counts, compare SUM(amount) vs. ad-hoc query for spot-checks.- Notes: consider timezone handling, NULLs, refunded/cancelled order logic, and performance on large orders (add index on created_at,status).

HardTechnical

40 practiced

Walk through and explain, line-by-line, this SQL that computes rolling customer-level attribution using window functions. Explain business meaning of each clause, correctness assumptions, and how you'd document limitations.

SQL:

sql

WITH events AS (
  SELECT customer_id, event_ts, revenue,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_ts) AS rn
  FROM customer_events
), attribution AS (
  SELECT customer_id, event_ts, revenue,
         SUM(revenue) OVER (PARTITION BY customer_id ORDER BY event_ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3
  FROM events
)
SELECT * FROM attribution WHERE rolling_3 IS NOT NULL;

Sample Answer

I'll walk through each part of the query, explain its business meaning, state correctness assumptions, and list limitations / documentation you should include.

WITH events AS ( SELECT customer_id, event_ts, revenue, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_ts) AS rn FROM customer_events)

- Line-by-line: - SELECT customer_id, event_ts, revenue: pick the raw event-level fields. - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_ts) AS rn: assign an ordinal to each event per customer by timestamp.- Business meaning: creates a deterministic ordering of a customer's events (1st, 2nd, 3rd …) so subsequent calculations can reference event position.- Assumptions: event_ts is reliably recorded and comparable (same timezone, no major clock skew). If events share identical timestamps, ROW_NUMBER arbitrarily breaks ties unless you add a tie-breaker (e.g., event_id).- Note: rn is not used later in this SQL but shows intent to rely on event order; it's useful for debugging or filtering.

, attribution AS ( SELECT customer_id, event_ts, revenue, SUM(revenue) OVER (PARTITION BY customer_id ORDER BY event_ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3 FROM events)

- Line-by-line: - SUM(revenue) OVER (...): compute a 3-event rolling sum (current event + 2 previous events) per customer. - PARTITION BY customer_id: calculation restarts per customer (no cross-customer leakage). - ORDER BY event_ts: window respects chronological order. - ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: window defined by row offset (exactly 3 rows max).- Business meaning: attributes revenue to a 3-event lookback. Example: for event #5, rolling_3 = revenue at events 3,4,5 — useful if business defines attribution by last 3 interactions.

SELECT * FROM attribution WHERE rolling_3 IS NOT NULL;

- Line-by-line: - Filters out rows where the rolling window is incomplete (i.e., first and second events per customer will have fewer than 3 rows and SUM over ROWS BETWEEN 2 PRECEDING AND CURRENT ROW returns NULL).- Business meaning: only report customers/events where a full 3-event history exists — e.g., to measure metrics that require 3 prior interactions.

Correctness assumptions (explicit to document)- event_ts provides strict chronological order per customer; if ties exist you must add deterministic tie-breaker (event_id).- revenue is numeric and non-nullable or nulls are handled (NULLs in SUM lead to NULL results unless COALESCE used).- The table customer_events contains all events (no late-arriving or duplicate rows).- ROWS is used deliberately (counts rows), not RANGE (which depends on value ranges of event_ts).

Limitations and how to document them- Excludes customers with fewer than 3 events — state this clearly so stakeholders don't misinterpret as zero revenue.- Ties on event_ts: without tie-breaker the order could be nondeterministic; document tie-break strategy.- Time windows vs. row windows: this uses last 3 events irrespective of time; if business needs last 30 days instead, use RANGE or explicit time predicates.- Null handling: if revenue can be NULL, SUM may be NULL — recommend COALESCE(revenue,0) if intended.- Performance: window over large partitions can be expensive; consider indexing on (customer_id, event_ts) or pre-aggregating.- Data freshness/late events: define how late-arriving events are treated and whether backfills are run.- Attribution semantics: explain business rationale for “3 events” (why 3? any bias introduced?) and alternatives (time windows, weighted decay).

Suggested improvements / alternatives- Add deterministic ORDER BY: ORDER BY event_ts, event_id.- Use COALESCE(revenue,0) if nulls should be treated as zero.- If you want values for early events (partial windows), replace WHERE rolling_3 IS NOT NULL with COALESCE(rolling_3, SUM(...) OVER (PARTITION ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) or compute COUNT window to include partials.- For time-based attribution, use SUM(revenue) OVER (PARTITION BY customer_id ORDER BY event_ts RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW).

Summary for documentation- State purpose (3-event rolling customer revenue).- List assumptions (timestamps, nulls, completeness).- Explain exclusions (customers with <3 events).- Note performance considerations and recommended indexes.- Provide recommended fixes (tie-breaker, COALESCE, time-based windows) and suggested test cases (customers with identical timestamps, null revenue, <3 events, large partitions).

HardTechnical

34 practiced

You're presenting a model that underestimates churn to executives who invested in retention initiatives. Prepare a two-part presentation plan: (A) the one-slide top-level message and risk assessment for executives, and (B) a technical appendix explaining failure modes, diagnostics performed, and concrete remediation steps for engineering and analytics teams.

Sample Answer

A) One-slide top-level message & risk assessment (for executives)Headline (one sentence): Our churn model is systematically underestimating churn by ~X–Y percentage points; if uncorrected, retention program ROI may be over‑stated and budget allocations should be paused pending remediation.Key evidence (3 bullets):- Model vs. observed: cumulative actual churn exceeds predictions by X% over last 3 months (p < 0.01).- Affected cohorts: new customers (0–90 days) and lapsed-reactivated segment show largest gaps.- Business impact: projected retention lift and LTV improvement likely overstated — estimated overstated savings = $Z over next 12 months.Risk assessment (traffic-light):- Strategic risk: High — decisions made on optimistic forecasts could misallocate $ and reduce CAC payback.- Operational risk: Medium — retention scripts/automations may target wrong users.- Urgency & ask: Pause incremental spend on segmented campaigns for the two affected cohorts; authorize cross-functional diagnostic task force (2 weeks) and a $X analytics/engineering fix budget.

B) Technical appendix (for engineering & analytics)1) Observed failure modes- Label leakage/time-shift: training data included features derived from future behavior or delayed events.- Sampling bias & concept drift: training period predated a recent pricing/policy change; population distribution shifted.- Feature degradation: engagement telemetry missing for 12% of users due to instrumentation gaps.- Calibration error: model outputs poorly calibrated for low-propensity tails.

2) Diagnostics performed- Backtest vs. live: rolling-window backtests showed rising prediction error post-change date.- PSI & KS tests: population stability index > 0.2 for key features; KS divergence on recency features.- Feature importance drift: permutation importance and SHAP over time flagged top features changing rank.- Data lineage audit: traced missing events to a logging rollout on 2025-09-10.- Calibration plot & reliability diagram: uncovered ~30% underconfidence in predicted probabilities for 0–0.3 range.

3) Concrete remediation steps (prioritized, owner, ETA)- Short term (1–2 weeks) - Freeze campaign triggers relying on model for affected cohorts (Owner: PM/Marketing). - Retrain model excluding any features with potential leakage; use pre/post-change holdout for validation (Owner: Analytics; ETA 1 week). - Recalibrate outputs using isotonic regression or Platt scaling on recent holdout (Owner: Data Scientist; ETA 3 days).- Medium term (2–6 weeks) - Fix instrumentation gaps and backfill missing telemetry where possible; add monitoring alert for telemetry drop (Owner: Engineering; ETA 2–4 weeks). - Implement time-aware features and retrain with sliding window to address concept drift; add automated model performance pipeline (Owner: ML/Analytics; ETA 4 weeks).- Long term (6–12 weeks) - Deploy online A/B test comparing current model vs. corrected model with real churn as KPI; run until statistically powered (Owner: Analytics/Product; ETA 8–12 weeks). - Establish governance: monthly model health dashboard (PSI, calibration, actual vs predicted) and runbook for rapid rollback (Owner: Analytics/BI; ETA 6 weeks).

4) Validation & success metrics- Primary: reduction in prediction bias (mean error → near zero), calibration Brier score improvement, and model AUC/precision-stability on fresh data.- Business: alignment of predicted vs. observed churn within ±1–2 percentage points over next quarter; validated ROI on resumed retention spend.

Appendix notes: include SQL queries used for backtests, list of features flagged for leakage, and sample calibration plots — available on request.

EasyTechnical

48 practiced

Explain, step-by-step, how the Excel formula below computes a weighted moving average and how you would document it for a non-technical product manager.

Formula: =SUMPRODUCT(B2:B13,C2:C13)/SUM(C2:C13)

Define each term, common pitfalls (e.g., mismatched ranges, zeros), and an example that illustrates why weighting matters.

Unlock Full Question Bank

Get access to hundreds of Technical Communication and Explanation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.