Amazon Business Intelligence Analyst Interview Preparation Guide - Senior Level

Direct answer

Build sales orders first, because it touches the most shared dimensions and forces you to get the conformed ones right while the stakes are lowest; conform customer and date starting with that very first mart, since both are used by all three processes and retrofitting them later means re-touching every mart already built. Marketing campaigns and support tickets can follow in either order once those two dimensions exist.

Structured elaboration

A bus matrix lists business processes as rows and the dimensions each one uses as columns, with a mark where a process uses a dimension. It exists to answer exactly this question: given limited capacity to build marts one at a time, which dimensions have to be conformed from day one so that the second and third marts can simply reuse them, instead of each mart inventing its own version that has to be reconciled later.

Reading the matrix: customer and date are marked for all three processes, so they are the dimensions that MUST be conformed starting with the first mart built, whichever one that is. product and campaign and support agent are each used by only one process, so they can be built process-specific for now without creating a future conflict.

Which mart first. Sales orders is the natural first mart here: it uses three dimensions (customer, date, product), the most of the three processes, so building it first forces the team to design customer and date properly (with the attributes and grain that will actually generalize) while there is only one mart depending on them. Building a narrower mart first (support tickets, using only two dimensions) risks designing customer too narrowly for what marketing or sales will later need from it.

Order for the rest. Once customer and date are conformed from the sales-orders build, marketing campaigns and support tickets can be built in either order: each one only needs to reuse the existing customer and date dimensions and add its own process-specific dimension (campaign, support agent). Neither remaining mart's build order affects the other's correctness, because they do not share a dimension with each other that they do not already share with sales orders.

Worked example

Suppose support tickets were built first instead, and its team modeled customer narrowly, keyed only by the email address on the ticket, with no attributes for billing account or acquisition channel. When sales orders is built next and needs customer to support revenue-by-acquisition-channel reporting, the acquisition-channel attribute does not exist on the dimension support tickets already published, so sales either has to extend the existing dimension (risking breaking the support mart's existing reports if the extension is done carelessly) or build a second, incompatible customer dimension (recreating the exact fragmentation the bus matrix exists to prevent). Building the widest-touching mart first avoids this by making the team design the shared dimension for its eventual full set of consumers, not just its first one.

Trade-offs and pitfalls

The most common mistake is treating the bus matrix as a one-time planning document rather than a living contract: once customer is conformed, every future mart's team has to be held to using it as-is (or proposing a reviewed extension), not quietly forking their own version because the shared one is missing one attribute they want. A second mistake is over-conforming too early: campaign and support agent do not need to be designed for hypothetical future reuse before any second process actually needs them, since guessing wrong about a dimension nobody ends up sharing wastes the same design effort the matrix is meant to save.

Direct answer
When a mentee gets defensive and stops acting on feedback, the fastest way to make it worse is to double down with more direct feedback. Slow down, diagnose why the message isn't landing (the content, the delivery, or something the mentee brings into the room), then rebuild the conversation as a two-way one instead of a one-way correction. If the pattern doesn't shift after a genuine attempt at that, it needs to be named and escalated, not quietly tolerated.

Diagnose before you re-deliver

• Separate "defensive because of how I said it" from "defensive because of what's underneath it." Workload, unclear expectations, a confidence hit, or feedback that reads as a character judgment rather than a specific behavior all produce the same surface symptom (pushback, non-action) for different reasons.
• Ask, don't assume: open with a genuinely curious question rather than a repeat of the critique. "Walk me through how that landed for you" gets you information; "you need to stop being defensive" gets you more defensiveness.

Use motivational interviewing instead of more direct pressure

• Motivational interviewing is built for exactly this: someone who may intellectually agree but is resisting behaviorally. Instead of arguing for the change, reflect their own stated goals back to them and let them articulate the gap ("You mentioned you want to lead the next project. How does this pattern affect that?"). People act on reasons they generate themselves far more than reasons handed to them.
• Keep the ratio of affirmation to correction visible. If every interaction is corrective, the mentee starts hearing footsteps before you speak, which is what produces reflexive defensiveness.

Rebuild the mechanism, not just the next conversation

• Shrink the ask: instead of a broad critique, propose one small, concrete, reversible change and a short check-in window.
• Make feedback bidirectional: ask what kind of feedback has landed well for them before, and adjust format (written vs. verbal, immediate vs. batched) accordingly.

Know when coaching has run its course

• If, after two or three honest attempts using the above, the pattern is unchanged (commitments still not acted on, same defensiveness), that's a signal the issue may be outside what coaching alone fixes: a skill gap being misread as attitude, a values or fit mismatch, or a factor you're not positioned to see.
• At that point, loop in the mentee's manager, or HR if the dynamic has become adversarial, rather than continuing to privately absorb it. Frame it factually: what you tried, what changed, what didn't. This isn't giving up on the mentee; it's recognizing some situations need authority or context you don't have.

Worked example
A mentee kept missing agreed follow-ups on code review comments and would get visibly short in Slack whenever it came up. The instinct was to restate the same feedback more firmly. Instead, the better move: open the next 1:1 with "I want to understand how the review feedback has been landing for you, not go through it again," and listen first. It turned out the mentee had inherited a legacy module nobody had explained well, and every review comment felt like it was pointing out someone else's mess. The fix wasn't more feedback, it was pairing on the module once and shrinking the ask to one file at a time. If that hadn't worked, the next honest step would have been raising the pattern with the mentee's manager, not repeating the same conversation a fourth time.

Trade-offs and pitfalls

• The junior mistake is treating defensiveness as a discipline problem and pushing harder; that reliably produces more resistance, not less.
• Over-correcting the other way (going silent on real issues to avoid triggering defensiveness) just delays the same conversation and lets performance drift.
• Escalating too early, before you've tried adjusting your own approach, reads as offloading a coaching problem; escalating too late lets a stalled dynamic damage trust or delivery. The senior move is trying a genuine adaptation first, timeboxing it, and being honest about whether it moved anything.

Direct answer

Bootstrap the target with a consistent point-in-time snapshot of each source table, and hand off to the change data capture (CDC) stream at the exact log position the snapshot was taken from, so the union of "snapshotted rows" and "streamed changes from that position onward" covers every row exactly once, with no gap and no duplicate window. Keep the target schema evolvable by treating additive changes (a new nullable column) as automatic, and gating anything that changes existing meaning, renames, type changes, drops, behind an explicit migration step instead of silently applying whatever the source does next.

Structured elaboration

Snapshot-to-stream handoff. Take the snapshot inside a transaction, or using the database's consistent-snapshot mechanism, and record the exact log position (an LSN, binlog offset, or global transaction identifier) at that instant. Start the CDC stream from that same recorded position. This is the one design decision that determines correctness: pin it wrong and you get either duplicated rows (stream restarts before the snapshot's true cutoff) or silently missing rows (stream starts after it).

Ongoing incremental changes. Log-based CDC (the same mechanism used for capture generally) streams inserts, updates, and deletes keyed by the source's primary key; apply them to the target as upserts, or soft-deletes for deletions, keyed by that same primary key, so replaying or reordering events within one key stays safe.

Schema evolution policy. Additive changes (a new nullable column) propagate automatically because they cannot break anything already relying on the existing shape. Anything that changes existing meaning, a rename, a type narrowing, a column drop, is not auto-applied: the pipeline diffs the schema on each batch, halts propagation for that table when it detects an incompatible change, and requires an explicit versioned migration mapping old to new before resuming. Silently applying a rename as if it were a new column would quietly corrupt every downstream query relying on the old name.

Consistency during migration. The target should never expose a half-migrated schema to readers. A staged, versioned table swap, build the new shape alongside the old, validate it, then atomically point consumers at it, keeps every query against a single, fully-consistent version at any moment.

flowchart LR
  A[Source OLTP tables] --> B[Snapshot extractor]
  A --> C[Log tailer from pinned position]
  B --> D[Target: bootstrap load]
  C --> E[Change stream: upsert/soft-delete]
  D --> E
  E --> F[Target: steady state]
  A --> G[Schema diff check]
  G -->|additive| E
  G -->|breaking change| H[Halt + versioned migration]
  H --> E

Worked example

Forty OLTP tables need replication; the largest, orders, has 200 million rows averaging 300 bytes each pre-compression:

If the snapshot pull sustains 200 MB/s:

CDC starts buffering changes from the recorded log position the moment the snapshot begins. If the source sustains 50 writes/sec against orders during that 5-minute window:

Those 15,000 buffered events are applied, in log order, once the snapshot lands, closing the gap with nothing missed and nothing duplicated. If orders is being replicated on its own as a narrower, near-real-time single-table copy rather than as one of forty tables, the same arithmetic scopes down directly: a 60 GB bootstrap followed by roughly 15,000 buffered upserts to reach steady state, with the target consistent as soon as that buffered replay finishes.

Trade-offs & pitfalls

The single most common corruption source is taking the snapshot without pinning the exact log position, or using an isolation level that doesn't guarantee one consistent read point across the whole table, either duplicating rows if the stream restarts before the true cutoff, or dropping rows if it starts after. Auto-applying every schema change, including renames, is a shortcut that eventually corrupts a column's meaning silently, by the time it's noticed, downstream queries have been reading the wrong thing for a while. Very large tables make a single-transaction snapshot expensive and lock-heavy on the source; a chunked, parallel snapshot by primary-key range trades a slightly more complex bootstrap for not holding a long-lived lock.

Direct answer. The most plausible causes are that the two connections are actually hitting different underlying data (a lagging or differently-tuned replica), that the replica's statistics are stale relative to the primary's, or that a configuration difference between the two (memory settings, cost parameters) leads the same query to a genuinely different plan; isolate which one by comparing statistics freshness, configuration, and data currency between the two connections directly, rather than assuming the query itself is the variable.

Structured elaboration. Start by confirming the two connections are even hitting the same DATA: replication lag means a replica can be seconds, minutes, or more behind the primary, and while that usually doesn't change PLAN shape by itself, it's worth ruling out as a confounding factor before you go further, since a stale replica muddies any comparison you make. Next, compare statistics freshness on the specific tables involved: if the replica's statistics were refreshed at a different time (or on a different schedule) than the primary's, or if statistics don't replicate identically depending on your engine's replication mechanism, the two connections can genuinely be planning from different information even though the underlying schema and data are notionally "the same." Finally, compare configuration directly: a replica provisioned with different memory settings, different cost-model parameters, or even a different engine version can lead an otherwise-identical query to a genuinely different, and genuinely differently-optimal, plan.

Worked example. A replica whose statistics job runs on a different, less frequent schedule than the primary's is a very plausible explanation on its own: if the primary was recently ANALYZE'd after a data shift but the replica hasn't caught up yet, the replica's optimizer is working from an older picture of the data than the primary's, which can easily produce a different (and worse) plan for the identical query text.

Trade-offs and pitfalls. Resist jumping straight to "the replica's hardware must just be slower," which is possible but is a much less common actual cause than a genuine statistics or configuration difference, and treating it as the default explanation without checking the more likely causes first can send you chasing an infrastructure upgrade that wouldn't have fixed the real problem.

Direct answer

I would build the program backward from the decisions it must change: a small set of sources, one shared tagging scheme across product lines and languages, a named owner for each theme (the person accountable for one recurring customer problem), and a quarterly planning rule that every top roadmap item cites customer evidence or states why not. I would roll it out in about 90 days, starting with one product line, and judge it by whether decisions changed, not by report volume. If I could do only one thing first, it would be to agree the decisions with leadership and the rule that top roadmap items cite customer evidence, then pilot the tagging scheme on a single product line.

1. Decide the decisions first

Agree with product leadership which decisions it feeds: the quarterly roadmap, bug-versus-feature priority and sunsetting (retiring a feature). If no decision uses a theme, drop the theme.

2. Sources (capture)

Each record carries: account ID, segment (SMB or enterprise), annual recurring revenue (ARR), product line, language, source and date. This is what lets you weight by revenue and still show SMB voices. Integrate with the customer relationship management (CRM) system (where sales and account teams log customer contacts) and the support desk, so teams tag feedback in tools they already use.

3. Multilingual and multi-product handling

Keep the original text, add a machine translation (automatic software translation), and have native speakers spot-check a sample of each language. Tag with one shared taxonomy (the agreed list of tags every feedback item is labelled with) across the three product lines, so cross-product themes (such as slow exports) surface once rather than three times. An excerpt (illustrative):

Area (level 1)      Theme (level 2)        Example tags (level 3)
Data and exports    Export reliability     export-fails, export-missing-rows
                    Export formats         export-xlsx, export-api
Performance         Speed                  slow-search, slow-load
Onboarding          Setup                  sso-setup, import-data
Access              Sign-in                sso-missing, mfa-reset
On every item also: product line, language, severity (1 to 3)

Rules: tags are always in English whatever the original language, an item gets at most two tags, and new tags are added only at weekly triage so the list does not sprawl.

4. Triage and ownership

A rotating weekly triage (product manager, support lead, engineer) tags new items, links duplicates into one theme record (a single entry grouping all feedback about the same problem), and assigns a theme owner per product line. Propose a triage time target (for example, new items tagged within two working days) as a service-level agreement (SLA, a promised response time) the group commits to.

5. Quantify impact, then convert to roadmap

For each theme record: number of distinct accounts, ARR affected, frequency and severity. Example (illustrative): "Bulk export fails: 37 tickets from 14 accounts, 9 of them enterprise, no workaround." Each quarter the theme owner turns the top themes into one-page opportunity briefs (problem, evidence, size, options and a recommendation). In planning, each roadmap item lists its evidence, and every top theme gets a recorded decision: build, backlog, or decline with a reason.

6. Close the loop

Notify affected customers and the support team when a theme ships or is declined. That visible result is what keeps people logging feedback.

7. Rollout in 90 days

• Days 1 to 30: audit current sources, agree the taxonomy, pilot on one product line with one planning cycle in view.
• Days 31 to 60: connect the CRM and support desk, name owners, start weekly triage.
• Days 61 to 90: use it in a real planning cycle, review what changed, then extend to the other two product lines.

Stakeholders: an executive sponsor, a program owner, one theme owner per product line, and a contact in Support, Sales and Customer Success each.

8. How I would know it works (program-health indicators)

• Share of top roadmap items that cite customer evidence.
• Median days from first signal to a recorded decision.
• Share of themes with a decision within the quarter.
• Loop-closure rate (the share of customers who logged feedback and were told what happened to it).
• Coverage: feedback share per segment and language compared to the customer base.

Trade-offs

Central taxonomy with federated ownership (one shared tag scheme, with each product line's owner applying it and owning its themes) beats a central team that owns everything (it becomes a bottleneck) or fully independent teams (themes never connect).

Show both an ARR-weighted and an account-count view so enterprise does not drown out the SMB majority. For the bulk-export theme (14 accounts, 9 enterprise and 5 SMB), the account-count view says 5 of 14 accounts, 36%, are SMB. The ARR view, at an illustrative $60,000 per enterprise account and $4,000 per SMB account, is 9 x 60,000 + 5 x 4,000 = $560,000, of which enterprise is 96%. Shown alone, ARR would hide that more than a third of the affected accounts are small ones. A new channel later (for example a partner forum) slots into the same sources table and the same tags, because every source writes the same record fields.

Major-severity BI incident (definition)
A BI incident is "major" when it threatens critical business operations, creates material financial/regulatory risk, or prevents senior decision‑making. Concretely, classify as major if any ONE of these thresholds is met:

• User impact: >25% of intended dashboard/report users or any/all C-suite/stakeholders cannot access or get actionable data.
• Financial risk: estimated revenue/MTD/quarterly impact ≥ $100k (or ≥5% of monthly revenue), or decisions leading to >$250k risk if acted on.
• Decision latency: outage spans a critical window (e.g., quarter close, board meeting, earnings call) or causes missed regulatory deadlines.
• Data integrity: core KPIs (revenue, bookings, inventory, safety metrics) are incorrect by >5% (absolute) or show systematic bias (e.g., missing partition) affecting automated downstream processes.
• SLA/time-to-restore: incident unresolved after 4 hours during business hours (escalate) or 1 hour for executive-facing systems.

Rules of thumb

• Executive-facing or regulatory = lower threshold for major (any outage or accuracy failure → major).
• Bread-and-butter operational dashboards = apply %‑user + $ thresholds above.
• Always consider compounding effects (e.g., inaccurate KPI + automated triggers).

Two BI-specific examples

1. During month-end close, the Sales Executive Dashboard shows total bookings 12% lower than actual due to a broken ETL partition. All 20 sales leaders and CFO rely on it for commission/forecasting decisions. Estimated revenue impact >$500k if decisions proceed. Classification: Major (data integrity >5%, executive impact, critical window).
2. A regulatory safety report automatically generated from BI pipelines is delayed by 2 days past the filing deadline because of failed joins and missing records. Missed regulatory deadline and potential fines make this a Major incident (regulatory risk + data integrity + SLA breach).

Direct answer

Add new attributes as nullable columns (backfilling historical rows where feasible, leaving them NULL for older rows where not), change a data type only through an explicit, tested migration step (never an in-place silent type change), and retire columns by first marking them deprecated and hiding them from new queries via a view, before physically dropping them once confirmed unused, keeping every step backward-compatible for existing reports throughout.

Structured elaboration

• Adding new attributes: add as a nullable column; for a Type 2 dimension, decide whether the new attribute should be tracked historically (Type 2) or is only meaningful going forward (fine to backfill as NULL for historical rows and only populate for new versions).
• Changing a column's data type: never change a live column's type in place without a tested migration; instead, add a new column with the target type, backfill and validate it against the old column, switch downstream consumers over, then retire the old column, mirroring the same phased approach as retiring a column outright.
• Retiring columns: mark deprecated (documented, communicated to known consumers) before removing; expose a compatibility view that still presents the old column (perhaps computed from its replacement, or simply frozen) for a transition window, so existing reports don't break the moment the underlying column is dropped.
• Migration planning and testing: for any of these changes, test against a realistic subset of production data first, and validate that existing reports produce identical output before and after the change, the same validate-before-cutover discipline used in any dimensional-model migration.

Worked example

Adding a loyalty_tier attribute to customer_dim: added as a new nullable column, backfilled where source data allows, left NULL for historical rows predating loyalty-tier tracking, and downstream reports referencing it are built to handle NULLs gracefully (treating them as "unknown" rather than crashing or silently defaulting to a specific tier). Retiring a legacy old_segment_code column: first replaced by a segment column with a compatibility view mapping the two for existing reports, given a defined deprecation window, and only physically dropped after confirming (via query logs) that nothing still references it directly.

Trade-offs and pitfalls

The riskiest version of any of these changes is an in-place type change or a column drop with no transition period, which can silently break downstream reports the moment it ships, discovered only when a stakeholder notices a dashboard error or, worse, doesn't notice a silently wrong number. The phased, backward-compatible approach costs more calendar time and requires tracking column usage across consumers, but avoids breaking changes shipping without warning.

Direct answer: Compute the metric with small-segment counts suppressed or noised (via a minimum-cell-size threshold and/or differential-privacy noise) rather than shown exactly, and choose the specific technique based on how small the segments realistically get and how much accuracy loss is acceptable for the metric's actual use case.

Structured elaboration, techniques and their accuracy/privacy trade-off:

• Minimum-cell-size suppression: any segment with fewer than a threshold number of users (e.g., 10) is not shown at all, or is aggregated into a broader "other" bucket. Simple and easy to explain, but a hard cutoff can be gamed or can hide genuinely interesting outlier segments entirely, and provides no formal privacy guarantee (a persistent, sophisticated adversary combining multiple slightly-different queries could still sometimes infer a small segment's true value).
• Differential privacy (DP) noise: add calibrated random noise to a count before it's exposed, providing a formal, mathematically provable privacy guarantee (bounding how much any single individual's presence/absence can affect the reported number) regardless of how the data is subsequently sliced or combined; the cost is a small, quantifiable accuracy loss on every reported number, including large segments, not just small ones, and it requires actual expertise to calibrate correctly (the "privacy budget" and noise scale).
• Aggregation-only reporting: never expose the raw segment breakdown at all, only a coarser rollup, sacrificing drill-down capability entirely in exchange for the simplest possible protection.
• k-anonymity style generalization: replace a fine-grained segment definition with a coarser one until each group has at least k members (e.g., collapsing a rare country into a regional bucket), preserving some analytical value while eliminating the smallest, most re-identifiable groups.

Worked example: a "unique users per country" metric, where several countries have very few users, computed with HLL-based approximate counting (S52) PLUS a small amount of differential-privacy noise added to each country's count before reporting, protects against re-identifying a specific individual in a tiny-population country while keeping large-country counts nearly exact (the DP noise's relative impact shrinks as the true count grows); a purely suppression-based alternative would instead simply hide any country below a threshold entirely, losing all visibility into those markets rather than providing an approximate, privacy-safe number.

Trade-offs & pitfalls: DP noise is the most rigorous option but is also the easiest to get wrong (miscalibrated noise either fails to protect privacy meaningfully or destroys the metric's usefulness); minimum-cell suppression is easier to implement correctly but provides a weaker, non-formal guarantee and can create confusing gaps in a report ("why is this segment just... missing"). Choose based on the actual sensitivity of the data and the sophistication of the threat model: internal-only reporting with a trusted, small audience may reasonably use simple suppression, while any externally-shared or highly sensitive breakdown warrants the stronger DP guarantee despite its added complexity.

Direct answer: Both problems are solved by staging a chain of CTEs (common table expressions, named, reusable subqueries introduced with WITH) so each stage does one job and hands a smaller, cleaner input to the next: filter first to shrink the data early, deduplicate before any expensive step touches it, then run the expensive recursive or join logic last. For the ancestor-count problem, that means filter-to-30-days, then dedupe, then a recursive CTE that walks up to 3 parent hops per event, then aggregate and rank. For the attribution problem, it means finding all clicks eligible for a conversion (same user, within the 7-day lookback, before the conversion), then picking exactly one (the earliest) per conversion with ROW_NUMBER(), then aggregating by campaign, so a single conversion can never be credited to more than one campaign.

Structured elaboration

Query 1: recent, deduplicated, ancestor-counted, ranked (written for and executed against DuckDB)

WITH RECURSIVE
recent AS (
  SELECT * FROM raw_events WHERE ts >= now() - INTERVAL '30 days'
),
dedup AS (
  SELECT event_id, user_id, parent_event_id, ts
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, parent_event_id ORDER BY ts) AS rn
    FROM recent
  ) x
  WHERE rn = 1
),
ancestor_walk AS (
  -- anchor: each deduped event's immediate parent, depth 1
  SELECT d.event_id AS root_event, d.parent_event_id AS ancestor_id, 1 AS depth,
         ARRAY[d.parent_event_id] AS path
  FROM dedup d WHERE d.parent_event_id IS NOT NULL

  UNION ALL

  -- recursive: walk one more hop up, stop at depth 3, refuse to revisit a node already in the path
  SELECT aw.root_event, e.parent_event_id, aw.depth + 1, list_append(aw.path, e.parent_event_id)
  FROM ancestor_walk aw
  JOIN dedup e ON e.event_id = aw.ancestor_id
  WHERE aw.depth < 3
    AND e.parent_event_id IS NOT NULL
    AND NOT list_contains(aw.path, e.parent_event_id)
),
ancestor_count AS (
  SELECT root_event, COUNT(DISTINCT ancestor_id) AS ancestor_distinct_count
  FROM ancestor_walk GROUP BY root_event
),
event_with_ancestor AS (
  SELECT d.event_id, d.user_id, COALESCE(a.ancestor_distinct_count, 0) AS ancestor_count
  FROM dedup d LEFT JOIN ancestor_count a ON d.event_id = a.root_event
),
user_metrics AS (
  SELECT user_id, COUNT(*) AS event_count, AVG(ancestor_count) AS avg_ancestor_count
  FROM event_with_ancestor GROUP BY user_id
)
SELECT user_id, event_count, avg_ancestor_count
FROM (
  SELECT *, RANK() OVER (ORDER BY event_count DESC, avg_ancestor_count DESC) AS rk
  FROM user_metrics
) ranked
WHERE rk <= 10
ORDER BY rk;

Why the stages have to be in this order: recent shrinks the row count before anything else touches the table, cutting the input to every later stage. dedup has to run before the recursive walk, not after, because a duplicate un-deduped row would let the recursion walk the same parent chain twice and double-count ancestors. ancestor_walk is a genuine recursive CTE: the anchor term picks up each event's direct parent (depth 1), the recursive term joins back to dedup to climb one more hop, and the WHERE aw.depth < 3 clause caps recursion at 3 hops so it terminates even on a hierarchy taller than 3. Carrying an accumulated path array and checking NOT list_contains(aw.path, e.parent_event_id) protects against a cycle in the parent_event_id chain (a data-quality bug where an event's ancestor chain loops back on itself), which would otherwise recurse until the depth cap kicks in, wasting work, or forever on an engine where the depth guard were dropped by mistake. list_append and list_contains are DuckDB's list functions for growing the path and checking membership; PostgreSQL expresses the same two operations natively as path || value and value = ANY(path), but that PostgreSQL spelling does not run on DuckDB, which is what this query is written for. One dialect note worth stating out loud: PostgreSQL, MySQL, and DuckDB all require the RECURSIVE keyword immediately after WITH for a self-referencing CTE to be legal syntax, as written above, while SQL Server's WITH clause has no RECURSIVE keyword at all, a CTE there is simply allowed to reference itself and the engine detects the recursion on its own.

Query 2: first-click attribution with no double-counting

WITH eligible_clicks AS (
  SELECT c.conversion_id, c.user_id, c.conv_ts, k.campaign_id, k.click_ts
  FROM conversions c
  JOIN clicks k
    ON k.user_id = c.user_id
   AND k.click_ts <= c.conv_ts
   AND k.click_ts >= c.conv_ts - INTERVAL '7 days'
),
first_click AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY click_ts ASC) AS rn
  FROM eligible_clicks
),
attributed AS (
  SELECT conversion_id, campaign_id
  FROM first_click WHERE rn = 1
)
SELECT
  cc.campaign_id,
  cc.total_clicks,
  COALESCE(cv.attributed_conversions, 0) AS attributed_conversions,
  ROUND(COALESCE(cv.attributed_conversions, 0)::numeric / cc.total_clicks, 4) AS conversion_rate
FROM (SELECT campaign_id, COUNT(*) AS total_clicks FROM clicks GROUP BY campaign_id) cc
LEFT JOIN (SELECT campaign_id, COUNT(*) AS attributed_conversions FROM attributed GROUP BY campaign_id) cv
  ON cv.campaign_id = cc.campaign_id
ORDER BY cc.campaign_id;

eligible_clicks is a plain join, every click within the 7-day lookback of a conversion for the same user, which can (and should) produce multiple rows per conversion if the user clicked more than one campaign in that window. first_click is where the "no double-attribution" guarantee actually lives: ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY click_ts ASC) numbers every eligible click 1, 2, 3... per conversion in time order, and attributed keeps only rn = 1, exactly one row per conversion_id, so a conversion can never appear twice in the final aggregation no matter how many qualifying clicks it had. This is the same rank-then-filter pattern as top-N-per-group; a window function alone cannot filter in the WHERE clause of the query that defines it (it evaluates after WHERE), which is why the ranking has to live in its own CTE stage before the filter can reference it.

Worked example

Executed against a small raw_events(event_id, user_id, parent_event_id, ts) table: a chain 1 -> 2 -> 3 -> 4 for user 100 (event 4's parent is 3, 3's parent is 2, 2's parent is 1), plus a shorter chain 5 -> 6 for user 101, plus a duplicate row for (user 100, parent_event_id = 2) inserted an hour later than the real one. dedup correctly kept the earlier of the two duplicate rows. ancestor_walk then produced, for root_event = 4: ancestor 3 at depth 1, ancestor 2 at depth 2, ancestor 1 at depth 3 (the walk correctly stopped at 3 hops and found all 3 real ancestors); for root_event = 2: just ancestor 1 at depth 1. Rolling that up, user 100 had event_count = 4, avg_ancestor_count = 1.5; user 101 had event_count = 2, avg_ancestor_count = 0.5.

For attribution, a clicks and conversions table with two users: user 200 clicked campaign 10 on day 1 and campaign 20 on day 3, then converted on day 5 (both clicks are within the 7-day lookback); user 201 clicked campaign 10 a month before their conversion (outside the lookback) and campaign 30 one day before it (inside the lookback). The executed query attributed user 200's conversion to campaign 10 (the earlier of their two eligible clicks, campaign 20 got none of the credit despite also being eligible), and user 201's conversion to campaign 30 (their only click actually inside the 7-day window; the stale campaign-10 click a month earlier was correctly excluded by the lookback filter, not just outranked). The resulting per-campaign table showed campaign 10 with 2 total clicks and 1 attributed conversion (rate 0.5), campaign 20 with 1 total click and 0 attributed conversions (rate 0), and campaign 30 with 1 total click and 1 attributed conversion (rate 1.0), each conversion counted exactly once across the whole result set.

Key points

• Dedup and time-filtering are cheap, do them before the expensive recursive or ranking step, not after.
• The recursive term's WHERE aw.depth < 3 is what makes the recursion terminate on its own; a path/visited-set check is a second, independent safety net against cycles in the data, not a substitute for the depth cap.
• ROW_NUMBER() ... PARTITION BY conversion_id is the mechanism that turns "many eligible clicks" into "exactly one attributed click"; without it a naive join would double-count every conversion once per eligible click.

Complexity

The recursive CTE's cost is roughly O(rows x average fan-in per hop x max depth): each of the 3 allowed hops re-joins the current frontier back to dedup, so a wide, bushy ancestor graph costs more per hop than a narrow chain, capped by the depth-3 limit regardless of how tall the true hierarchy is. The attribution query's cost is dominated by the eligible_clicks join, which is a range join on click_ts against a 7-day window per user; an index on clicks(user_id, click_ts) turns that from a full scan into an index range lookup per conversion.

Edge cases

• An event whose parent chain is shorter than 3 hops (e.g. depth 1 only): the recursive term's join simply produces no more rows for that branch, ancestor_count correctly reports fewer than 3 ancestors, no special-casing needed.
• A cyclic parent_event_id chain (bad data: event A's ancestor chain loops back to A): the path-array check stops the recursion from re-walking the same node, independent of the depth cap.
• A conversion with zero eligible clicks in the 7-day lookback: it simply does not appear in attributed, and does not inflate any campaign's attributed_conversions, which is the correct "unattributed" outcome, not a bug to work around.
• Two clicks for the same campaign both eligible for the same conversion: ROW_NUMBER() still resolves to exactly one attributed row per conversion, deduplication happens at the conversion level, not the campaign level, so this cannot inflate attributed_conversions for that campaign.

Trade-offs & pitfalls

The array/path-tracking overhead in the recursive term is only necessary because the data can contain cycles, if parent_event_id were guaranteed to never loop back on itself by a constraint enforced upstream, the depth cap alone would be sufficient and the path check could be dropped for a leaner query; carrying it unconditionally is defensive but not free. For attribution, first-click is one of several valid attribution models (last-click, linear, time-decay), the ROW_NUMBER() ... ORDER BY click_ts ASC line is the single place that encodes the choice, changing ASC to DESC switches the whole query to last-click attribution with no other changes, worth calling out explicitly so a reviewer or stakeholder knows which model is running.

Direct answer
Additive seasonality assumes the seasonal swing is a roughly constant absolute amount regardless of the series' level; multiplicative seasonality assumes it scales proportionally with the level. Choosing the wrong one distorts both baselines (an additive model under-predicts the seasonal swing at a high level, or over-predicts at a low one) and anomaly thresholds (a fixed-width band around an additive baseline will be too tight during high-level periods and too loose during low-level ones).

Structured elaboration

• Additive: $y_t = \text{trend}_t + \text{seasonal}_t + \text{residual}_t$ - the seasonal swing stays roughly the same size in absolute units no matter what the trend level is doing.
• Multiplicative: $y_t = \text{trend}_t \times \text{seasonal}_t \times \text{residual}_t$ - the seasonal swing grows or shrinks proportionally with the trend level; a multiplicative model can be fit as an additive one on the LOG of the series, since $\log(y_t) = \log(\text{trend}_t) + \log(\text{seasonal}_t) + \log(\text{residual}_t)$.
• Which metrics favor which: revenue-style metrics that grow substantially over time, where the seasonal spike naturally grows alongside the business (e.g. a December bump that's proportionally consistent but has grown from $10K to $50K as the business scaled), are usually multiplicative. Metrics with a roughly stable base level and a genuinely fixed-size seasonal effect (e.g. a fixed number of extra support tickets every Monday, regardless of overall ticket volume trend) are more often additive.
• Testing which model fits better: the simplest visual test is whether the peak-to-trough seasonal amplitude visibly GROWS alongside the trend on a raw plot - if it does, additive is the wrong assumption. More formally, fit both, compare residual variance (an additive fit on a genuinely multiplicative series will show residual variance that itself grows with the level, a clear diagnostic on a residual-vs-fitted plot), or simply compare backtested forecast error between the two.
• Why the choice matters for baselines/anomaly detection: an anomaly-detection threshold built on additive-model residuals (e.g. "flag anything more than 3 residual-standard-deviations away") implicitly assumes constant residual variance across the whole series; if the true process is multiplicative, that fixed threshold will be too sensitive (false positives) during low-level periods and too insensitive (missed real anomalies) during high-level periods.

Worked example
Two metrics side by side: daily active users growing from 10,000 to 100,000 over two years, with a weekend dip that's always been roughly 15% below the weekday average - that's multiplicative (the absolute size of the dip has grown 10x alongside the user base, but the RATIO has stayed constant). A metric like "number of scheduled maintenance windows per week," with a fixed operational cadence unrelated to overall traffic growth, is more likely additive.

Trade-offs & pitfalls
Log-transforming to convert a multiplicative problem into an additive one is convenient (lets you reuse additive-model tooling) but changes how you have to interpret and back-transform prediction intervals - a symmetric interval in log-space becomes an ASYMMETRIC interval once exponentiated back to the original scale, which is correct behavior (uncertainty genuinely should be asymmetric on a multiplicative series) but is easy to get wrong if you forget to back-transform properly.