Amazon Business Intelligence Analyst - Junior Level Interview Preparation Guide

Direct answer: PERCENT_RANK() reports where a row sits by rank position, scaled to a 0-to-1 range; CUME_DIST() (cumulative distribution) reports what fraction of all rows have a value at or below the current row's, which is the more familiar meaning of "percentile." They diverge specifically on ties: CUME_DIST() gives every row tied at the same value the identical, correct fraction of the whole dataset at or below that value, while PERCENT_RANK() is derived from RANK()'s gapped position, so tied rows also share a value, but that value reflects rank position rather than the true proportion of the dataset at or below it. For "each user's spend percentile within their own country," where the intent is "what fraction of people in my country spend at or below what I spend," CUME_DIST() is the right function; PERCENT_RANK() would answer a related but different question about relative rank position instead.

Structured elaboration

$\text{PERCENT\_RANK} = \frac{\text{rank} - 1}{n - 1}$

$\text{CUME\_DIST} = \frac{\text{number of rows with value} \le \text{current row's value}}{n}$

where rank is the row's RANK() (tied rows share a rank, leaving gaps) and n is the partition's row count.

Worked example

Verified in DuckDB against t(id, score) = (1,50), (2,80), (3,80), ordered ascending:

SELECT id, score,
  PERCENT_RANK() OVER (ORDER BY score) AS pr,
  CUME_DIST()    OVER (ORDER BY score) AS cd
FROM t ORDER BY score;

For score 80 (rank 2 of 3, since RANK() on the ascending order gives 1, 2, 2): $\text{PERCENT\_RANK} = \frac{2-1}{3-1} = 0.5$. CUME_DIST for score 80 is $\frac{3}{3} = 1.0$, since all 3 rows, including both rows tied at 80, have a value at or below 80. PERCENT_RANK's 0.5 reads as "midway between the lowest and highest rank position"; CUME_DIST's 1.0 reads, correctly, as "100% of this dataset is at or below this score," which is the honest answer given that this row's value is in fact the maximum present.

Applying this to spend percentile within country

SELECT user_id, country, spend,
  CUME_DIST() OVER (PARTITION BY country ORDER BY spend) AS spend_percentile
FROM user_spend
ORDER BY country, spend;

Verified in DuckDB against user_spend(user_id, country, spend) with US rows at 50, 100, 200, 200 and FR rows at 100, 300: US spend percentiles come back 0.25, 0.5, 1.0, 1.0 (the two users tied at 200 both correctly show "100% of US users spend at or below me," since together they account for the top of the US distribution), and FR spend percentiles come back 0.5, 1.0.

Why CUME_DIST is the right choice here, not PERCENT_RANK: the business question, "what percentile is this user's spend at within their country," is asking for the true proportion of the country's population at or below that spend level, which is exactly CUME_DIST's definition. PERCENT_RANK would technically run and produce numbers in the same 0-to-1 range, but its value for the two tied US users at 200 would come from their shared, gapped rank rather than directly reflecting that 100% of the US population spends at or below them, making it a less faithful answer to the actual stakeholder question, even though both functions are syntactically interchangeable here.

Trade-offs & pitfalls

Both functions require the same underlying computation, one sort of the partition plus a single pass (no meaningful performance difference between them), so the choice is entirely about which definition matches the business question, never about cost. A common wrong turn is picking whichever function "sounds like percentile" without checking its tie behavior against a dataset that actually has ties (real spend or score data usually does); on data with no ties at all, PERCENT_RANK and CUME_DIST can look deceptively similar in a quick spot check, and the difference only surfaces once duplicate values appear, often well after the query has already shipped to a dashboard.

Situation: While validating monthly executive dashboards, I discovered a formula error in a KPI that feeds directly into executive variable compensation — fixing it would retroactively change payouts and create legal/reputational exposure.

Approach / who I’d involve:

• Immediate: notify my manager and head of BI to escalate.
• Convene a cross-functional incident team within 24 hours: Finance (compensation accounting), HR/Total Rewards, Legal/Compliance, Internal Audit, CFO/CEO or their delegate, and the affected business leader. Include IT/engineering for data lineage and the BI developer who built the metric.

Steps I would lead:

1. Contain and document

• Freeze the dashboard and any automated processes showing the incorrect KPI; snapshot current outputs and data lineage.
• Create an incident log with timestamps, queries, and affected time windows.

2. Quantify impact and options (48–72 hours)

• Recompute corrected KPI across all historical periods and calculate delta per executive and aggregate payout changes.
• Produce scenarios: (A) full retroactive adjustment, (B) prospective correction only, (C) blended remediation (cap adjustments or phased clawbacks), (D) no change with disclosure and governance actions.
• Model financial, tax, accounting (ASC/IFRS), and reputational/legal implications for each option. Use clear numbers: payout deltas, P&L impact, potential clawback amounts, statutory limitations, and materiality thresholds.

3. Risk assessment & recommendation

• With Legal and Finance, assess contractual obligations, employment laws, disclosure requirements (SEC or local regs), and tax/GAAP treatment.
• Recommend the most compliant option balancing legal obligation and business continuity (e.g., if contracts legally require correction, recommend retroactive remediation with a communicated plan).

4. Decision and approvals

• Present concise briefing to the executive sponsor with modeled scenarios, recommended path, controls, and timeline for remediation and communications.
• Seek documented sign-off from Legal, Finance, HR, and the executive sponsor before action.

5. Communication and remediation plan

• If remediation affects payouts: prepare private, factual communications to impacted individuals via HR + legal-led conversations; provide detailed payback or adjustment statements and timelines. Coordinate external disclosure if material.
• Internal disclosure: town hall or leadership memo describing corrective steps and controls, avoiding blame, emphasizing accuracy and remediation.
• Accounting entries: Finance to record adjustments; coordinate payroll/tax adjustments and any clawback mechanics.
• Public/Regulatory: Legal to advise on required filings; Communications to draft messaging for investors and media if material.

6. Remediation execution

• Implement agreed payout corrections with Finance/Payroll; track receipts/repayments and provide statements to impacted parties.
• Run verification with internal audit and document all steps.

7. Governance to prevent recurrence

• Implement immediate BI controls:
  • Unit tests for KPI logic; regression tests run on deployment.
  • Version-controlled analytic code and peer code reviews for any compensation-linked metrics.
  • End-to-end data lineage documentation and automated alerts for large KPI deltas.
  • Quarterly reconciliation between BI KPI and Finance ledger.
  • Change management policy: any change to compensation-related metrics requires sign-off from Finance and HR before deployment.
  • Periodic audits by Internal Audit of top 10 compensation-linked metrics.

What I would emphasize as BI lead:

• Transparency and data: deliver clear, reproducible analyses so stakeholders can trust the numbers.
• Speed with rigor: act quickly to quantify impact but ensure controls and legal compliance.
• Collaboration: rely on cross-functional expertise for legal/accounting judgment while owning the technical diagnosis, corrected code, and long-term controls.

Result expectation:

• Fast containment, legally compliant remediation, preserved trust through transparent communication, and stronger analytics governance reducing risk of recurrence.

Direct answer

Metric monitoring tracks a key performance indicator ("KPI") over time to detect a trend or anomaly; metric segmentation slices that same metric by a dimension to locate where and among whom the change happened. Monitoring tells you a number moved; segmentation is what turns that alert into somewhere specific to investigate, instead of a guess.

Structured elaboration

Worked example

An e-commerce conversion rate falls from 3.20% last week to 3.00% this week, on the same 10,000 visits both weeks, a 0.20-percentage-point aggregate drop:

Segmented by channel (4,000 organic visits, 6,000 paid visits, both weeks): organic converts at 4.0% both weeks (160 conversions, unchanged). Paid converts at 2.67% last week (160 conversions) and 2.33% this week (140 conversions):

The entire net decline, $320 - 300 = 20$ conversions, traces to the paid channel alone ($160-140=20$), while organic contributed zero change. This tells the team to investigate the paid campaign or its landing page specifically, not run a broad, unfocused review of the whole product.

Trade-offs and pitfalls

Segmentation without monitoring has nothing to localize; you need the trend alert first to know a change happened at all. Over-segmenting fragments sample size until no individual slice is statistically reliable enough to act on. A segment that explains where a change happened still doesn't explain why it happened; it narrows the investigation, it doesn't replace it. Picking segments only after a metric moves, rather than predefining a small standard set per metric, biases toward whichever slice happens to look interesting rather than the ones that actually matter for prioritization.

Direct answer

On an extract, load, transform (ELT)-first warehouse where row-by-row upserts are slow or expensive, implement Slowly Changing Dimension (SCD) Type 2 by staging incoming changes, using a change-tracking mechanism native to the platform (Snowflake streams, or an equivalent change-feed on other warehouses) to identify exactly what changed since the last run, and applying the Slowly Changing Dimension Type 2 (SCD2) close-and-insert logic as a single set-based MERGE driven by a scheduled task, rather than per-row updates.

Structured elaboration

• Staging: land the raw incoming batch (or change data capture (CDC) feed) into a staging table as-is, no transformation yet.
• Change detection via a stream: a Snowflake stream (or similar) on the staging table (or on the source table if it's already in-warehouse) gives you exactly the inserted/updated/deleted rows since the stream was last consumed, avoiding a full comparison scan against the whole dimension every run.
• Set-based MERGE, not row-by-row: use one MERGE statement to (a) close out current rows for business keys with a detected attribute change, and (b) insert new versioned rows, driven by a scheduled task that fires on the stream having data. This is a single set-based operation, not a loop, which is what makes it viable at scale on an ELT platform where per-row procedural updates are comparatively expensive.
• Compaction: periodically compact historical rows (for example, consolidating very old, rarely-queried versions, or moving them to cheaper storage) to control the storage growth of a Type 2 dimension over years of history, since ELT-first platforms typically charge for storage scanned even on historical partitions.

Worked example

A customer_dim stream fires when staging receives updated customer records. A scheduled task runs a single MERGE: MERGE INTO customer_dim USING (SELECT * FROM customer_stream) AS src ON customer_dim.business_key = src.business_key AND customer_dim.current_flag = true WHEN MATCHED AND src.attributes_changed THEN UPDATE SET effective_to = CURRENT_DATE, current_flag = false followed by a separate INSERT of new versions for both changed and brand-new business keys, all in one scheduled run rather than a continuously running process.

Trade-offs and pitfalls

The main risk of stream-and-task-based SCD2 is stream staleness: if the task doesn't run frequently enough, or fails silently, the stream's offset can lag or (on some platforms) expire, losing track of changes. Monitor task run history and stream staleness explicitly, and design the MERGE to be idempotent (safe to re-run against the same stream data) so a retried task after a failure doesn't double-apply changes.

Direct answer. The realistic options are an index that already provides the needed order (removing the need for an explicit sort entirely), a materialized or pre-sorted structure maintained ahead of time, or switching the pagination strategy so you never need to re-sort the same data repeatedly from scratch; the right choice depends on how latency-sensitive the read path is and how often the underlying data changes.

Structured elaboration. If an index exists whose column order matches the ORDER BY exactly (and any equality filters precede it, per left-prefix rules), the engine can walk that index in order directly, producing already-sorted output with no separate sort step at all, this is usually the cheapest fix when it's available and doesn't conflict with other query patterns on the same table. When that's not practical (the sort column doesn't suit an index well, or multiple conflicting sort orders are needed across different queries), a materialized or pre-sorted structure, maintained on write rather than recomputed on read, shifts the sort cost from every read to a smaller, amortized cost per write, which is a good trade when reads vastly outnumber writes and the sort order needed is stable and well-known ahead of time. Where the underlying need is really "let a user page through a large, sorted result," switching to keyset pagination (see the pagination discussion elsewhere in this topic) can eliminate most of the sort cost naturally, since each page only needs the next handful of already-ordered rows rather than materializing and sorting the whole result set.

Worked example. A dashboard's "most recent 100 events" query with ORDER BY created_at DESC LIMIT 100 and no matching index has to sort the entire matching set before it can return the top 100; adding an index on created_at (descending, if the engine supports that natively, or just ascending and reading it backward) turns this into an index walk that stops after 100 rows, with no sort operator in the plan at all.

Trade-offs and pitfalls. An index chosen purely to satisfy one query's sort order can conflict with, or add write overhead unhelpful to, other query patterns on the same table; and a maintained pre-sorted structure needs its own update logic and adds operational surface area, which is only worth it once the read-path savings clearly outweigh that maintenance cost.

Direct answer

A retro after a release with repeated design-engineering friction should produce two things: an honest, specific account of where the handoff actually broke down, not a vague 'communication issues,' and a small number of concrete process changes, each with an owner and a way to tell in a quarter whether it worked. Running it well means separating fact-finding from diagnosis, and diagnosis from blame.

Structured elaboration

Design principles for the session

• Facts before diagnosis: start from a timeline of what actually happened (spec dates, handoff dates, bug counts, points where implementation and design diverged), not from opinions about who was at fault.
• Root cause, not the nearest symptom: 'engineering didn't follow the spec' is a symptom; the root cause might be that the spec didn't capture edge-case states, or that both sides were working from different versions of a shared design system mid-migration.
• Few, high-leverage commitments: two or three process changes people will actually do beat ten action items that quietly get dropped.
• Everyone leaves with the same understanding of what changed, not just what went wrong.

A workable structure

One illustrative shape, adaptable to a team's own rhythm:

What 'actually changes how the two teams work' looks like

The output isn't a list of intentions, it's a specific artifact or habit that exists after the meeting and didn't before: a shared checklist embedded in the handoff process, an automated check that catches a class of mismatch before it ships, or a standing short sync during implementation windows. Whatever it is, it needs a way to tell if it worked, not just that it happened.

Worked example

One team's root cause turned out to be that design tokens (colors, spacing values) were maintained in the design tool but hand-copied into code, so drift was inevitable and nobody could tell which side was 'correct' when they disagreed. The concrete fix was an automated export from the design tool into the codebase, checked by both a design reviewer and a frontend reviewer before merge, plus a short recurring sync during active implementation. A quarter later, the team had a real signal that it worked: noticeably fewer visual-mismatch comments on pull requests and less late-stage rework than the release that triggered the retro. The same root-cause pattern shows up in other domains as a hand-copied data contract or config value instead of a design token, so the same fix shape (automate the handoff, add a lightweight check, add a short sync during the risky window) generalizes well beyond design and engineering specifically.

Trade-offs and pitfalls

• A retro that produces ten action items usually produces zero completed ones; prioritizing ruthlessly matters more than being thorough.
• If the room jumps straight to solutions or blame instead of facts first, the real root cause, often structural or tooling-related rather than a person's failure, never surfaces.
• A retro that isn't revisited becomes theater. Put the check-in on the calendar before the room disperses, not as a vague intention afterward.
• Watch for a fix that only addresses this specific release's symptom (a one-off manual double-check) rather than the structural cause; it holds for one cycle and then quietly stops happening.

Direct answer
Design dashboards around a metric hierarchy (a north-star metric, a small set of leading indicators, and supporting health metrics), choose visuals that make the CONNECTION between leading indicators and the north star visible, and wire anomalies directly to playbooks so the dashboard prompts action rather than just displaying numbers nobody acts on.

Structured elaboration

1. Metric hierarchy: one north-star metric that best represents long-term value delivered, 2-4 leading indicators that predict movement in the north star before it happens, and a handful of health/guardrail metrics that catch unintended side effects.
2. Recommended visuals: the north star as a prominent trend line with a longer time horizon; leading indicators as smaller trend tiles placed visually near the north star so their relationship is implied by proximity, not buried in a separate section; health metrics as a compact status row rather than full charts, since they're checked but rarely the primary focus.
3. Alerting integration tied to playbooks: when a leading indicator or health metric crosses a threshold, the alert should link directly to a documented playbook (what to check, who to notify, what remediation steps exist) rather than just notifying that "something changed," so the operations team has an immediate next action.
4. Avoiding vanity metrics: any metric on the dashboard that nobody has ever changed behavior because of should be demoted or removed; the test for every tile is "would a specific person do something differently if this number moved."
5. Measuring whether the dashboard is driving action: track whether alerts and dashboard views correlate with documented follow-up actions (tickets filed, playbooks executed), not just view counts, to validate the dashboard is actually action-oriented rather than passively viewed.

Worked example
A north star of "weekly active paying users," leading indicators of activation rate and 7-day retention, and health metrics of error rate and support-ticket volume; when 7-day retention drops below its rolling baseline, the alert links to a documented playbook ("check for a recent release, check for an infra incident, check for a competitor pricing change") rather than a bare notification.

Trade-offs and pitfalls
A dashboard can look sophisticated (rich metric hierarchy, alerting) and still be a vanity artifact if nobody ever follows through on its alerts; periodically audit alert-to-action follow-through, not just alert volume.

Direct answer
Identify which distinct audiences need to know, choose the channel and level of detail each one actually needs, and sequence the communication so people closer to the change (or who need to prepare others) hear it before the broader audience does.

Structured elaboration

1. Segment the audiences. A single announcement rarely fits everyone; separate, for example, the people directly affected day-to-day, the managers who'll field questions from their teams, and anyone who needs advance notice to prepare (support, a partner team, external users).
2. Match channel to audience and stakes. A high-stakes or sensitive change might warrant a live meeting or a call for the most affected group, supplemented by a written announcement for broader reach and future reference; a low-stakes change might only need the written version.
3. Sequence deliberately. People who need to answer questions from others (managers, support) generally need to hear it before the people who'll be asking them those questions; announcing to everyone simultaneously can leave the people expected to explain it caught flat-footed.
4. Decide what each audience actually needs to know, not just a single message copy-pasted everywhere; a technical team needs the mechanism, an executive audience needs the business impact, and end users need what changes for them specifically.
5. Plan for questions. Include a channel or contact for follow-up questions, and consider pre-briefing a few likely questions so the people fielding them aren't caught off guard.

Worked example
Rolling out mandatory two-factor authentication for all employee accounts: first, brief IT support and team leads a few days ahead with the exact rollout date, the reason, and answers to likely questions, since they'll field employee questions once it's public. Then send the broad announcement to all employees with the what and why in plain language, the exact date it takes effect, and a link to a short setup guide, plus a support contact for anyone who gets stuck. A separate, more detailed technical note goes to the security and IT teams covering enforcement mechanism and rollback plan, which the general employee announcement doesn't need.

Trade-offs and pitfalls

• Announcing to the broadest audience first, before briefing the people who'll need to answer questions, is a common sequencing mistake that leaves support and managers unprepared.
• One-size-fits-all messaging either overwhelms a general audience with irrelevant technical detail or underserves a technical audience that needed the mechanism, not just the headline.
• Too many channels for a low-stakes change can feel like overkill and train people to tune out future announcements; match the weight of the communication plan to the actual stakes of the change.

Calculation:

• Rows = 200,000,000
• Added included bytes per row = 16
• Raw added bytes = 200,000,000 * 16 = 3,200,000,000 bytes ≈ 3.2 GB (decimal) ≈ 2.98 GiB
• If current nonclustered index entry is 40 bytes, new index entry ≈ 56 bytes total (40+16). Total index size = 200M * 56 = 11,200,000,000 bytes ≈ 11.2 GB.
• Additional storage for the new covering columns = 3.2 GB raw. Account for typical index overhead (page fill, leaf/non-leaf structure, fragmentation, metadata) — conservative multiplier 1.25 → ~4.0 GB additional on disk.

How I’d weigh storage vs read-latency gains:

• Benefits: a covering index avoids expensive lookups to the base table (RID/key lookup or bookmark lookup), often turning multi-page access into single-index scans/seek — can reduce read latency dramatically for targeted BI queries (especially high-frequency ones).
• Costs: ~4 GB extra storage (modest for most warehouses), plus increased write/ETL cost (every INSERT/UPDATE/DELETE touches the index) and longer index maintenance/rebuild windows.
• Decision criteria:
  • Query frequency & SLA: If the query is run frequently (dashboard refresh every few minutes or shared by many users) and latency matters, the index is likely worth it.
  • Selectivity & coverage: If the index makes the common predicate + projection fully covered, expect large I/O reductions. If queries still need many columns or scans, benefit is smaller.
  • Write throughput: If ETL is heavy and runtime windows tight, the extra DML cost may outweigh read gains.
  • Alternatives: filtered index, compressed index, columnstore index (for analytics workloads) or materialized view/summary table may give better trade-offs.

Practical next steps (BI approach):

• Create the index in a staging DB (or as ONLINE, with monitoring) and run A/B tests: measure logical reads, execution time, CPU, and ETL impact.
• If storage is constrained, consider index compression or filtered indexes to reduce bytes stored.
  Recommendation: Given ~4 GB extra cost versus potentially large reductions in query I/O for high-frequency dashboards, implement in test and validate; promote to production if measured latency and user impact justify the maintenance cost.

Direct answer. Join the raw table to a subquery that computes, per key, the maximum timestamp, matching back on BOTH the key and that maximum timestamp, so only the single latest row per key survives the join.

Structured elaboration. The join-based approach computes the "what's the latest timestamp per key" question as its own aggregate subquery first (a GROUP BY key with MAX(timestamp)), then joins the raw table back to that result on both the key AND the timestamp matching the computed maximum, which naturally keeps only the winning row(s) per key. This produces the identical logical answer to a ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC) approach filtered to rn = 1, just expressed through aggregation and a join instead of a window function.

Worked example. raw_customers(customer_id, email, created_at): (1, 'a@x.com', t1), (2, 'a@x.com', t2 later), (3, 'b@x.com', t3).

-- join-based: aggregate the max timestamp per key, then join back to it
SELECT rc.customer_id, rc.email, rc.created_at
FROM raw_customers rc
JOIN (
  SELECT email, MAX(created_at) AS max_created
  FROM raw_customers
  GROUP BY email
) latest ON rc.email = latest.email AND rc.created_at = latest.max_created;

-- ROW_NUMBER() alternative, for comparison
SELECT customer_id, email, created_at FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM raw_customers
) t WHERE rn = 1;

Both return the identical result: (2, 'a@x.com', t2), (3, 'b@x.com', t3), correctly keeping the later of the two rows sharing 'a@x.com'.

Trade-offs and pitfalls. The join-based approach has one correctness wrinkle the window-function version doesn't: if two rows for the SAME key happen to share the exact same maximum timestamp (a genuine tie), the join-based version returns BOTH of them, since both match the aggregate's max_created equally, whereas ROW_NUMBER() with a stable ORDER BY tie-break returns exactly one, deterministically. If ties are possible in your data, you'd need to add an explicit secondary tie-breaker (an id column) to the join's matching condition to get single-row-per-key behavior back. Performance-wise, the two approaches often compile to comparably efficient plans on a well-indexed key/timestamp pair, so the choice mostly comes down to whether your engine and team are more comfortable with the aggregate-and-join idiom or the window-function idiom, and whether tie-handling needs to be airtight.