DoorDash Business Intelligence Analyst (Entry Level) Interview Preparation Guide

Direct answer
A time-of-day-versus-day-of-week heatmap reveals when user activity actually concentrates, which can surface insights like a mid-week evening peak, a weekend lull, or an unexpected spike at an odd hour that a simple daily-trend line would completely hide, since a heatmap preserves both time dimensions simultaneously rather than collapsing them into one aggregate.

Structured elaboration

1. Insight 1: identifying the true peak windows: the heatmap can reveal that activity peaks on, say, weekday evenings rather than a naive assumption of "business hours," directly informing when to schedule a notification, promotion, or a feature launch for maximum visibility.
2. Insight 2: spotting day-specific anomalies: a single day-of-week (e.g. every Sunday morning) showing unusually low or high activity relative to its neighbors can prompt investigation into whether that's a genuine behavioral pattern or an artifact (a scheduled job, a reporting/timezone issue).
3. Insight 3: comparing weekday vs. weekend rhythms: the heatmap can reveal that weekday and weekend usage follow fundamentally different daily rhythms (e.g. weekday usage clusters around commute times, weekend usage is flatter and later), which single-line daily trend charts collapse away entirely.
4. Turning insights into product changes: shifting notification send-times to align with the discovered peak window, or adjusting on-call/support staffing to match actual (not assumed) peak load times.

Worked example
A heatmap reveals engagement peaks on weekday evenings between 7-9pm and is notably higher on Sundays than other weekend hours; a product team could shift a weekly digest notification's send-time to align with the observed weekday-evening peak, and consider a Sunday-specific re-engagement push that the prior "weekends are quiet" assumption would have missed.

Trade-offs and pitfalls
A time-of-day heatmap reflects users' current (possibly time-zone-mixed) local or server time; if the user base spans multiple time zones and the heatmap isn't normalized to each user's local time, apparent patterns can be an artifact of the population's geographic mix rather than a genuine behavioral rhythm.

Bucketing with CASE and then counting per bucket is a two-step composition: first classify each row, then treat the classification as an ordinary GROUP BY column.

Structured elaboration

SELECT bucket, COUNT(*) AS product_count FROM (
  SELECT CASE
    WHEN net_revenue < 100 THEN 'low'
    WHEN net_revenue < 2000 THEN 'medium'
    ELSE 'high'
  END AS bucket
  FROM sales
) t
GROUP BY bucket;

Wrapping the CASE expression in a subquery (or a CTE, a Common Table Expression) first, then grouping on its output column, avoids having to repeat the entire CASE expression in the GROUP BY clause; some engines do allow grouping directly on a repeated CASE expression inline, but the subquery form reads more clearly and sidesteps any alias-in-GROUP-BY portability question entirely.

Worked example

Given products with net_revenue 50, 500, 5000, 20: the low bucket (< 100) catches two products (50 and 20), medium (100 to <2000) catches one (500), and high (>= 2000) catches one (5000), giving bucket counts low: 2, medium: 1, high: 1.

Trade-offs and pitfalls

This composition pattern, classify-then-group, generalizes well beyond revenue buckets: cohort labeling, risk tiers, and age brackets all follow the identical shape. Keep the threshold logic in exactly one place (the subquery/CTE) so a later change to the bucket boundaries doesn't need to be hunted down in multiple copies of the same CASE expression.

Objective & scope: determine whether our pricing is competitive across segments, quantify gaps, estimate demand sensitivity where possible, and deliver an actionable recommendation framework (immediate price actions, tests, and monitoring).

1. Clarify requirements & KPIs

• Stakeholders: Revenue/Finance, Product, Sales, Marketing.
• KPIs: price gap (ours vs. median/lowest competitor), share of clicks/units at each price point, margin impact, estimated revenue change, conversion uplift from price moves.

2. Data sources

• Internal: product catalog (SKUs, attributes), sales ledger (price, units, revenue, discounts, channel, date), customer segmentation, returns.
• External: scraped competitor prices (timestamped), product descriptions/images, promotion flags, marketplace reviews/stockouts.
• Enrich: category taxonomy, exchange rates, shipping/tax rules.

3. Normalize product mappings

• Build a product-resolution pipeline: standardize attributes (brand, model, size, color), fingerprint text (TF-IDF + embedding), image similarity (hashing/ML) where available, and use a blocking + fuzzy-match algorithm to link competitor SKUs to our SKUs with match scores. Keep human-in-the-loop review for low-confidence matches and log mapping confidence.

4. Handle sparse/irregular competitor data

• Aggregate to time windows (daily/weekly) and compute observed price distributions and last-seen price.
• For missing periods: impute using hierarchical smoothing (category-average trend + competitor-specific random effects) or carry-forward with uncertainty bounds; flag high-uncertainty items.
• Use survival/availability models to detect stockouts vs. scraping gaps.
• Weight competitor signals by freshness and match confidence.

5. Estimate price elasticity

• Prefer causal approaches: exploit natural experiments—promotions, temporary competitor price changes, our historical price changes, geo-price differences.
• Model: panel fixed-effects regression at SKU-segment-week level: log(q) = α + βlog(price) + γcontrols (promotions, seasonality, marketing spend, availability) + SKU and time FE. β is elasticity.
• If endogeneity (price correlated with demand shocks), use instruments (cost shocks, supplier price changes, competitor price shifts) or difference-in-differences around exogenous price changes. Where possible run randomized price A/B tests for high-impact SKUs.

6. Recommendation framework

• Compute for each SKU-segment: competitive gap, mapped competitor percentile, margin sensitivity (contribution margin), estimated elasticity and revenue-maximizing price range with confidence intervals.
• Priority tiers:
  • Tier 1 (High impact, low uncertainty): actionable automated adjustments (raise/lower price within guardrails) + A/B test for larger changes.
  • Tier 2 (High impact, high uncertainty): run experiments or targeted promotions.
  • Tier 3 (Low impact): monitor and re-evaluate.
• Create a decision score = f(revenue_at_risk, margin_delta, competitor_gap, elasticity_confidence). Use thresholds to recommend: "match lowest within X%", "maintain premium at Y% with added value", or "test -10% for 2 weeks".

7. Dashboards & monitoring

• Executive dashboard: competitive heatmap by category/segment, top opportunities, confidence bands, experiment results.
• Operational dashboard: mapping confidence, scrape freshness, automated alerts for large competitor moves or stockouts.
• Automation: ETL for nightly scrape ingestion, weekly elasticity re-estimates, and rules engine to push recommended price tags to pricing ops with audit logs.

8. Risks & governance

• Monitor margin erosion, channel conflicts, legal/compliance for price scraping/use.
• Human review for brand-sensitive SKUs.
• Iterate: run experiments, recalibrate models, and embed learnings into the decision score.

Outcome: deliver a prioritized list of recommended price moves with expected revenue/margin impact and confidence, plus dashboards and an experimentation plan to validate and scale changes.

Direct answer

For a daily active users ("DAU") dashboard, choose segments that map to the two questions a product manager actually asks when DAU moves: is this an acquisition-or-retention story, or a platform-or-technical story. Expose enough of them that one view can localize the cause, rather than just confirming that something moved.

Structured elaboration

Worked example

Today's DAU is 50,000, of which 12,000 are new users and 38,000 are returning:

Yesterday's DAU was 48,000, with an 18% new-user share (8,640 new, 39,360 returning). Headline DAU growth:

looks like modest, healthy growth. But the returning-user count actually fell:

All of the net DAU growth came from a spike in new users, likely a campaign, while the returning-user base, the harder metric to move and the one that indicates real product stickiness, shrank by about 3.5%. The aggregate DAU number alone hides this; the new-vs-returning segment is what surfaces it.

Trade-offs and pitfalls

Putting too many segments on one dashboard turns it into noise; prioritize the two or three most decision-relevant ones for an executive view and push the rest to a drill-down. Small segments, a minor platform or a small region, can show large percentage swings purely from small sample sizes, so alerts on those segments need a minimum-volume threshold before firing. Segments can also interact (a channel's users may cluster heavily on one platform), so a single-dimension view can still mislead if the combination isn't checked. Finally, segmentation only localizes a change; it doesn't by itself explain the cause, so it should be the first step of an investigation, not the conclusion of one.

Clarify goals: grow micromobility adoption sustainably, safe operations, regulatory compliance, profitable by year 2. Roadmap (12 months) prioritized by impact/feasibility: Months 0–3 (Pilot & setup): secure permits, ops hub locations, hire local ops team; deploy 500–1000 vehicles in core neighborhoods; build operator and rider app flows. Months 3–6 (Scale & data): optimize rebalancing logistics with demand heatmaps; dynamic pricing to smooth supply; safety campaign (helmets, signage); integrate rentals into Lyft app. Months 6–9 (Optimize & partnerships): integrate transit partners for first/last-mile bundles; employer and university partnerships; introduce subscription passes. Months 9–12 (Mature ops): expand fleet, add electrified bikes, introduce maintenance SLAs and telematics-based preventive maintenance, city engagement for designated parking/corrals. Supply logistics: micro-depots, nightly charging/redistribution partnerships, predictive rebalancing using time-series demand models. Pricing: dynamic per-minute + unlock fee; subscription tiers for commuters. Safety: geofencing speed limits in sensitive areas, mandatory in-app safety tutorial, incident reporting. Regulatory engagement: weekly meetings with city, transparent data sharing, pilot KPIs. Integration: in-app multi-modal trip planning, combined payments and receipts. Success metrics: trips per vehicle/day, utilization rate, cost per ride, incident rate, regulatory compliance milestones. Trade-offs: fast scale risks enforcement friction; prioritize high-impact low-friction neighborhoods first.

Direct answer

Constructive skepticism means treating a colleague's analysis as something to verify before it reaches people who will make a decision on it, not something to trust blindly or attack. What keeps it collaborative rather than adversarial is that the questions are aimed at the work, in service of the same goal the analyst has (a correct, defensible result), not aimed at their competence.

Structured elaboration

What to actually check

• Data provenance and cleaning: were there filters, joins, or exclusions applied that could bias the result?
• Assumptions and their sensitivity: does the conclusion hold under a slightly different time window, cohort definition, or parameter choice?
• Confounders and alternative explanations: could something else, like seasonality or a cohort mix shift, explain the pattern as well as the stated cause?
• Reproducibility: can someone else rerun the analysis and get the same numbers, and are the metric definitions written down anywhere?

How to raise it without it turning into a credibility fight

The framing matters more than the content. Raise it privately and early, before it's in front of stakeholders, not during the stakeholder meeting itself. Ask it as a question about the data or method ('what date range did you use for this cohort?'), not as a verdict about the person or their competence. Where possible, offer to help verify rather than only pointing out a gap; that keeps the interaction collaborative instead of adversarial. The deeper mechanics of de-escalating a tense disagreement are their own skill; the key move here is simply getting the framing and the timing right before it escalates into one.

Worked example

A colleague's dashboard shows a conversion metric trending in a direction that conflicts with what other data would suggest. Before it goes in front of stakeholders, a private message asks what date range and cohort definition were used, and whether a known seasonal effect was accounted for. It turns out the shift came from a change in how the cohort was defined that week, not a real change in behavior. The colleague fixes the definition before the meeting, and the stakeholder presentation goes out correct, with no public correction needed.

Trade-offs and pitfalls

• Raising a concern only after it's already in front of stakeholders turns a technical question into a public correction, which is exactly where it tends to become a credibility fight.
• Flagging every minor doubt in a public forum regardless of the stakes wears down trust and slows the team; reserve escalation for cases where a private check didn't resolve it and the decision at stake actually matters.
• Being right about a caught issue is not the same as handling it well; how the concern was raised often matters more to the relationship than the fact that it was correct.

Approach (brief): implement a small DAX measure that returns a hex color (or named color) based on revenue vs target and a variance parameter. Use that measure in the visual’s conditional formatting (Format by > Field value). For large tables consider performance trade-offs: measures are evaluated in context per cell, so prefer simple arithmetic, minimize context transitions, and use calculated columns when values are static.

DAX measure example:

RevenueColor =
VAR Rev = SUM('Fact'[Revenue])
VAR Tgt = SUM('Fact'[Target])
VAR VarPct = SELECTEDVALUE('Params'[Variance], 0.05)  -- e.g. 0.05 = 5%
VAR Lower = Tgt * (1 - VarPct)
VAR Upper = Tgt * (1 + VarPct)
RETURN
SWITCH(
  TRUE(),
  ISBLANK(Rev) || ISBLANK(Tgt), "#FFFFFF",          -- white for missing data
  Rev < Lower, "#FF4C4C",                           -- red
  Rev <= Upper && Rev >= Lower, "#FFD766",          -- yellow
  Rev > Upper, "#4CAF50",                           -- green
  "#FFFFFF"
)

How to apply:

• Add the measure to the model.
• In the table/matrix visual select the Revenue column → Conditional formatting → Background color (or Font color) → Format by: Field value → Choose RevenueColor measure.

Performance considerations:

• Per-row measures run for every visual cell; aggregated SUMs are cheap but expensive calculations (e.g., LOOKUPVALUE, FILTER over large tables) will slow rendering.
• If variance is constant per row (not user-driven), compute a calculated column or precompute color in ETL to avoid repeated evaluation.
• If variance is a slicer/what-if parameter, caching helps but changing slicer forces recompute for visible cells.
• Use variables, avoid row-by-row iterators (e.g., FILTER on large tables), and keep logic in native aggregations.
• For very large tables, prefer conditional formatting rules (built-in Rules dialog) on aggregated measures where possible, or use color scales instead of per-row field-value measures.

This balances correctness, maintainability, and responsiveness for interactive reports.

Direct answer. UNION ALL simply stacks the rows from both queries, including duplicates. UNION does the same stacking and then performs an implicit dedup pass over the combined result, which costs extra work and is only worth paying for when you actually need it.

Structured elaboration. Reach for UNION ALL by default. Only add the dedup pass (plain UNION) when duplicate rows would genuinely be wrong for what you're building, for example combining exports from two systems that are known to sometimes report the exact same event twice. The cost of the dedup isn't just conceptual: on a large combined result, UNION effectively does a sort or hash pass over every column to find duplicates, whereas UNION ALL does none of that.

Worked example. jan_sales(customer_id, amount): (1,100), (2,50). feb_sales(customer_id, amount): (2,50), (3,80). Note customer 2 appears in both months with the identical amount (say, a reporting overlap).

SELECT * FROM jan_sales
UNION ALL
SELECT * FROM feb_sales
ORDER BY customer_id;
-- returns 4 rows: (1,100), (2,50), (2,50), (3,80). Customer 2 appears twice.

SELECT * FROM jan_sales
UNION
SELECT * FROM feb_sales
ORDER BY customer_id;
-- returns 3 rows: (1,100), (2,50), (3,80). The duplicate (2,50) row got collapsed to one.

Trade-offs and pitfalls. The risk with defaulting to UNION ALL is that if the two overlapping sources genuinely CAN produce the exact same logical row twice (as in this example), you'll silently double-count it in any downstream SUM. The risk with defaulting to UNION is the performance cost at scale, and a subtler correctness trap: UNION's dedup works on the WHOLE row, so two rows that are conceptually the same event but differ in even one column (a slightly different timestamp, an extra trailing space) won't be deduplicated at all, giving a false sense of safety. If you need dedup on a specific business key rather than the whole row, UNION ALL followed by an explicit GROUP BY or ROW_NUMBER() is usually the more precise tool.

Requirements & constraints:

• Move BI from batch reporting to near-real-time analytics and ML-driven insights in 24 months.
• Scope: BI analysts (12), data engineers, product-aligned features (streaming events, feature store, model inference), low-latency SLAs (<5 min).

High-level roadmap (quarters):

• Q1–Q2 (Foundations): skills audit, hire 2 data-engineer/ETL contractors, implement streaming PoC, set baseline KPIs.
• Q3–Q4 (Realtime + Automation): roll out streaming pipelines, train analysts on SQL-on-stream, OBS dashboards, automated alerts.
• Q5–Q6 (ML Adoption): build feature store, deploy first ML-driven insights (churn scoring), upskill analysts in model interpretation and MLOps basics.
• Q7–Q8 (Scale & Governance): embed ML insights into dashboards, run cross-functional adoption, refine governance and performance SLAs.

Skills taxonomy:

• Data ingestion & streaming (Kafka, kinesis)
• Real-time SQL & analytics (Materialized views, ksql, dbt)
• BI tooling (Looker/PowerBI advanced, embedded analytics)
• ML fundamentals for BI (feature engineering, model evaluation, explainability)
• MLOps basics (feature store, CI/CD for models)
• Data governance & observability

Hiring vs Upskilling criteria:

• Hire: gaps in core infra skills (senior data engineer, ML engineer) or urgent capacity.
• Upskill: existing BI analysts with strong SQL/visualization aptitude for realtime/ML interpretation.

Curriculum outline (12-week modules repeated):

• Module A: Streaming fundamentals + lab
• Module B: Real-time SQL, dbt for streaming, ELT patterns
• Module C: ML for analysts — metrics, model outputs, SHAP/LIME
• Module D: Productionizing insights, alerts, and governance

External partnerships:

• Cloud provider training (AWS/GCP), vendor workshops (Looker/PowerBI), hire consultancy for initial streaming and feature store setup.

Milestones & KPIs:

• Month 6: PoC streaming dashboards; KPI: pipeline latency <10 min
• Month 12: 50% dashboards realtime; KPI: decision latency reduced 30%
• Month 18: 1 production ML insight; KPI: model lift >10% on target metric
• Month 24: 80% relevant dashboards real-time + ML; KPI: adoption rate 75%, ROI >1.5x

Budget estimate (24 months, assumptions):

• Training & partners: $120k
• New hires (2 FTEs): $400k total comp
• Tools & infra (streaming, feature store, monitoring): $200k
• Contingency & misc: $80k
  Total ≈ $800k (assumes existing cloud credits, 12 BI analysts retained, phased hiring).

Direct answer: Leaving off the frame clause does not leave the frame unspecified: SQL Server, Postgres, and every other standard-conforming engine default it to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE is value-based, so "CURRENT ROW" really means "every row tied with the current row's ORDER BY value," not just the one physical row. With two rows sharing the same event_date, both tied rows get the identical running total: the sum through the end of that whole tied group. Switching to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW makes the frame positional, so each physical row gets its own distinct total based on row order, even among ties.

Structured elaboration

• A window function's frame is the sub-set of the partition that a given row's calculation actually reaches into. ROWS BETWEEN a PRECEDING AND b FOLLOWING counts physical rows. RANGE BETWEEN a PRECEDING AND b FOLLOWING counts by the value of the ORDER BY expression.
• Default frame rule: given PARTITION BY / ORDER BY with no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. (Without any ORDER BY at all, the default frame spans the entire partition.)
• Tie mechanics: rows sharing the same ORDER BY value are peers. RANGE's upper bound of "CURRENT ROW" is defined per peer group, not per physical row, so RANGE never splits a peer group: it always includes the whole group or none of it. Every row in a tied group therefore reports the sum through the end of that entire group, and they all report the same number.
• ROWS ignores value equality entirely. It walks physical rows one at a time in whatever order the sort produced, so tied rows still get distinct, strictly increasing totals from each other, in an order that is only deterministic if you break the tie with a secondary ORDER BY column.

The same trap is not specific to dates. It fires on any tied ORDER BY column. A products table ordered by priority (an integer with duplicates) shows the identical behavior: two rows both at priority = 2 will get the same RANGE-based running total, because RANGE groups them as peers regardless of whether the tied value is a date, an integer, or anything else orderable.

Worked example

-- t(customer_id, event_date, amount); two rows tie on 2025-01-02
-- (1, '2025-01-01', 100), (1, '2025-01-02', 50), (1, '2025-01-02', 30), (1, '2025-01-03', 20)

SELECT customer_id, event_date, amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY event_date) AS default_frame_total,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY event_date
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY event_date
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total
FROM t
ORDER BY event_date;

Run against that data (verified in DuckDB):

default_frame_total matches range_total exactly, confirming the default frame is RANGE. Both 01-02 rows show 180 under RANGE (the sum through the whole tied group: 100+50+30). Under ROWS, the two tied rows show different, physical-order-dependent numbers (150 then 180 here); without a tiebreaker in ORDER BY, which row gets which number is not guaranteed to be stable across re-runs or engines.

Key points

• The default frame is RANGE, not ROWS: this is the single fact behind the whole trap.
• RANGE treats tied ORDER BY values as one indivisible peer group for framing purposes.
• ROWS is purely positional and needs a deterministic sort (a tiebreaker column) to be reproducible.

Complexity
Computing this window requires sorting each partition by the ORDER BY key, O(n log n) per partition, then a single pass to accumulate the running sum, O(n). Both ROWS and RANGE cumulative frames can be computed incrementally in that one pass (each new row just adds its value to a running accumulator), so the frame type itself doesn't change the asymptotic cost here; the difference is purely in what value each row reports, not how expensive the query is.

Edge cases

• No tiebreaker: ROWS-based totals become dependent on physical insertion order or engine-internal sort stability, which is a correctness risk for a report that's supposed to be reproducible.
• A NULL event_date: NULLs sort together as their own peer group (position depends on NULLS FIRST/NULLS LAST), so they get the same tie-grouping behavior as any other duplicate value.
• Single-row partitions: both frame types trivially return that row's own value; the distinction only shows up once a partition has 2+ rows.

Trade-offs & pitfalls

The trap in practice: a developer who wants a per-row running total but writes only ORDER BY event_date (no ROWS clause) silently gets RANGE behavior. If event_date repeats per customer, which is extremely common at day granularity, every row for that day shows the identical "running total." It looks like a duplicate-value bug on the dashboard, but the query is doing exactly what the standard specifies.

Fix: be explicit. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for strictly per-row totals, and add a deterministic tiebreaker to ORDER BY (a surrogate id, or a finer-grained timestamp) so which tied row lands where is reproducible. If the actual intent is "one total per calendar day," RANGE's tie-grouping is correct as-is, though it is usually clearer to pre-aggregate to one row per day before windowing rather than relying on the frame default to do it implicitly.