Lyft Data Analyst Interview Preparation Guide (Entry Level)

Each of these three snippets fails for a different reason, and the fix generalizes into one robust pattern for exclusion lists supplied by an application.

Structured elaboration

1. WHERE status IN () is a syntax error in most engines (or, in dialects that allow it, matches nothing) because an empty list has no values to compare against. There is no single "corrected" form independent of intent: if the empty list means "no restriction was requested", drop the IN clause entirely before the query is built; if it means "this filter should legitimately match nothing" (e.g. a UI multi-select with every option unchecked), the honest, portable equivalent is WHERE FALSE, valid everywhere and behaving consistently instead of relying on how a particular engine happens to parse an empty list.
2. WHERE discount = NULL is not a syntax error but always evaluates to UNKNOWN (never TRUE), because NULL represents "unknown value", and no comparison operator, including =, can determine two unknowns are equal. Correct form: WHERE discount IS NULL.
3. WHERE order_date BETWEEN '2024-03-01' AND is an incomplete statement (missing the upper bound) and is a syntax error. Correct form, supplying the missing bound: WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31'.

Generalizing to a parameterized exclusion list: an application variable :excluded_statuses might arrive as NULL (no filter intended) or as an empty array (filter to nothing). Two safe patterns:

-- NOT IN, guarded against NULL and empty
WHERE (:excluded_statuses IS NULL OR status NOT IN (SELECT UNNEST(:excluded_statuses)))
-- NOT EXISTS, the more robust default (see Trade-offs)
WHERE NOT EXISTS (
  SELECT 1 FROM UNNEST(:excluded_statuses) AS excl(status_val)
  WHERE excl.status_val = status
)

(UNNEST expands an array value into one row per element, so an array parameter can be compared against, or joined to, row by row, the same way a real table would be.) Checking for NULL up front in the NOT IN version means "no exclusions" doesn't accidentally collapse to NOT IN (), which some engines error on and others silently treat as "exclude nothing" or "exclude everything" depending on the engine, an inconsistency worth not relying on. The NOT EXISTS version doesn't need that guard at all: if :excluded_statuses is an empty array, UNNEST produces zero rows, NOT EXISTS over zero rows is always TRUE, and every order is correctly kept, no special-casing required.

The reason NOT EXISTS doesn't need the guard, and NOT IN does, is the NULL-poisoning problem itself: if :excluded_statuses contains a NULL element (say, a bad value slipped in from the application), status NOT IN (SELECT UNNEST(:excluded_statuses)) compares status against that NULL for every row, and status <> NULL evaluates to UNKNOWN. NOT IN's OR-chain of comparisons needs every single comparison to be definitively TRUE to include a row; one UNKNOWN in the chain poisons the whole thing to UNKNOWN (never TRUE), so the query silently returns ZERO rows, for every row, not just ones that would have matched the NULL. NOT EXISTS never has this problem, because it only ever asks "does at least one matching row exist", a question a NULL row can fail to answer TRUE to without poisoning anything else in the chain.

Worked example

Given orders(1,'shipped'), (2,'cancelled'), (3,'pending') and an exclusion list of ('cancelled'): the NOT EXISTS-based exclusion, WHERE NOT EXISTS (SELECT 1 FROM UNNEST(:excluded_statuses) AS excl(status_val) WHERE excl.status_val = status), correctly returns orders 1 and 3, excluding order 2 (status 'cancelled' matches the one excluded value).

With an EMPTY exclusion list (:excluded_statuses = []): UNNEST produces zero rows, so NOT EXISTS is TRUE for every order with no special-case needed, and the query correctly returns all three orders, 1, 2, and 3.

With a NULL slipped into the exclusion list (:excluded_statuses = ['cancelled', NULL]): NOT EXISTS still correctly returns orders 1 and 3, unaffected by the NULL element, since the NULL element simply never matches any row's status, it doesn't need to disprove anything for the other rows. Contrast this with the NOT IN version on the same NULL-containing list: status NOT IN ('cancelled', NULL) returns ZERO rows, silently dropping orders 1 and 3 too, not just excluding order 2, exactly the NULL-poisoning failure this whole pattern exists to avoid.

Trade-offs and pitfalls

The single most reliable fix across engines is NOT EXISTS rather than NOT IN, because NOT EXISTS never has the NULL-poisoning problem NOT IN has, demonstrated above: reach for it by default in exclusion-list logic. The NOT IN plus explicit NULL/empty-list guard shown above is still a legitimate pattern when a team already has NOT IN-based logic and wants the smallest possible diff, it just needs the guard NOT EXISTS gets for free.

Layout (sheet names: Inputs, P&L, Calculations, Sensitivity, Charts)

• Inputs: clean, single source of assumptions (Base price, Base volume, COGS/unit, Fixed costs, Tax rate, Proposed price change + range, elasticity).
• P&L: structured rows (Revenue, COGS, Gross Profit, OpEx, EBIT, Taxes, Net Income) and columns for Base Case, Scenario (10% increase), and Variants.
• Calculations: intermediate steps (price per unit, adjusted volume, revenue by product, variable costs).
• Sensitivity: data table outputs for multiple price/elasticity combos.
• Charts: waterfall (gross profit bridge) and tornado or 2D heatmap.

Key assumptions (explicit, in Inputs):

• Base price = $P0; Base volume = Q0 (period/year)
• COGS per unit (variable) and total fixed costs
• Tax rate and any phasing/timing effects
• Price elasticity of demand (own-price elasticity, e.g., -0.8) with rationale (historical % volume change vs % price change or benchmark)
• Implementation timing (full-year vs partial-year)

Linking price change to volume (example formula):

• %ΔPrice = (P1 - P0)/P0 ; P1 = P0 * 1.10
• Use elasticity ε (negative): %ΔVolume = ε * %ΔPrice
• New volume Q1 = Q0 * (1 + %ΔVolume)
• Revenue = P1 * Q1
• Gross Profit = Revenue - (COGS/unit * Q1)
• Net Income = Gross Profit - Fixed OpEx - Taxes

Excel implementation tips:

• Keep Inputs as named cells (P0, Q0, EPS, COGS).
• Use direct formulas: =P0*(1+price_change) ; =Q0*(1+elasticity*price_change)
• Add data validation and comments for sources.

Sensitivity presentation (one recommended approach):

• Two-way data table (price change on rows, elasticity on columns) showing Net Income or %ΔNet Income; visualize as heatmap in Charts sheet. Complement with a tornado chart ranking drivers (price change, elasticity, COGS/unit, fixed costs) showing impact on Net Income for +/- plausible ranges. This makes risk and upside explicit to stakeholders.

Direct answer: Compute the weighted conversion rate as the weight-adjusted sum of conversions divided by the weight-adjusted sum of totals, and approximate its standard error using a weighted-variance formula (a design-effect-adjusted version of the standard binomial proportion standard error), since unequal weights inflate variance beyond what an unweighted formula would suggest.

Structured elaboration and SQL/pseudocode:

WITH weighted AS (
  SELECT
    user_id, converted, weight,
    weight * converted AS w_conv,
    weight * weight AS w_sq
  FROM users
),
agg AS (
  SELECT
    SUM(w_conv) / SUM(weight) AS weighted_conversion_rate,
    SUM(weight) AS sum_w,
    SUM(weight*weight) AS sum_w_sq,
    COUNT(*) AS n
  FROM weighted
)
SELECT weighted_conversion_rate,
  -- approximate standard error via an effective-sample-size adjustment (design effect)
  SQRT( weighted_conversion_rate * (1 - weighted_conversion_rate)
        / (sum_w * sum_w / sum_w_sq) ) AS approx_std_error
FROM agg;

The term sum_w * sum_w / sum_w_sq is the EFFECTIVE sample size under unequal weighting (Kish's effective sample size), which is always less than or equal to the raw count n when weights vary; using the raw n in a standard proportion standard-error formula instead of this effective sample size UNDERSTATES the true standard error whenever weights are unequal, since a few high-weight observations effectively carry more influence (and thus more sampling variability) than an equal-weighted average of the same count would.

Worked example: with weights ranging from 1 to 5 (some users representing 5x the population weight of others) and a raw sample size of 1,000, the effective sample size after this adjustment might be closer to 700-800 depending on the weight distribution's spread, meaning the true standard error is LARGER than a naive unweighted calculation on 1,000 observations would suggest; ignoring this adjustment would produce an artificially narrow, overconfident confidence interval.

Trade-offs & pitfalls: this design-effect approximation is itself an approximation (a more rigorous approach for a genuinely complex sampling design would use full survey-statistics methods, e.g., a Taylor-series linearization or replication-based variance estimator), but it is a substantial improvement over ignoring the weighting altogether, which is the far more common and more damaging mistake in practice; always confirm the weight column's actual distribution (a few extreme weights can dominate the effective-sample-size calculation) before trusting the resulting interval at face value.

Direct answer

In the first 60 seconds on an unfamiliar schema, scan for primary and foreign keys to understand the entity graph, check table row counts to spot which tables are the "big" transactional ones versus small reference tables, and note any obviously suspicious column names (timestamps, status/type enums, anything that looks like a business identifier) before writing a single query.

Structured elaboration

1. Keys first: list every table's primary key and foreign keys. This alone tells you the shape of the entity graph (which tables are "hubs," which are junction tables, which are lookups) faster than reading column-by-column.
2. Row counts and table sizes: a quick SELECT COUNT(*) (or checking catalog statistics if counting is expensive) per table tells you which tables are the high-volume transactional core versus small, slowly-changing reference or lookup tables; the big tables are where performance and data-quality issues concentrate.
3. Timestamps: note every created_at/updated_at/*_date column, since these tell you whether a table is append-only, mutable, or soft-deletable, and are usually the first thing you'll filter by in any real query.
4. Immediate questions to note down: which columns are nullable and what NULL means there (unknown? not-applicable? not-yet-happened?); whether any column that looks like a natural key (email, order_number) is actually enforced unique; and whether any table name or column name hints at multiple business concepts crammed into one table (a red flag for hidden complexity).
5. Prioritization: start inspecting the tables with the most foreign-key connections to others (the "hub" tables) and the largest row counts first, since those are both the most likely to contain data-quality issues and the most central to understanding the domain.

Worked example

On an unfamiliar orders/customers/order_items/payments/refunds schema, the 60-second scan would surface: orders and order_items are almost certainly the largest tables (one row per transaction, and multiple rows per order respectively) and the central hub of the graph via foreign keys; customers and payments are the next tier; and refunds referencing orders (not order_items) would immediately raise the question "does a refund apply to a whole order or can it be partial per line item," which is exactly the kind of ambiguity worth resolving before writing any analysis that assumes one or the other.

Trade-offs and pitfalls

• The temptation under time pressure is to start writing queries immediately; without the 60-second key-and-cardinality scan first, it's easy to write a join that silently multiplies rows (joining orders to order_items when you actually wanted one row per order) and never notice, because the query still "runs" and returns plausible-looking numbers.
• Row counts matter as much as the relationships themselves: a schema that looks simple on paper can have a 200-million-row order_items table sitting behind an innocuous-looking foreign key, and knowing that upfront changes how carefully you'd write any exploratory query against it.
• The single highest-value question to resolve early is usually about the grain of the biggest table (what does one row actually represent), because every subsequent aggregation's correctness depends on getting that right first.

Direct answer

I would not decide from either metric alone. I would translate both into one comparable number (revenue per user over a horizon, adjusted for retention), test with a controlled experiment, and only then ship. A feature that raises engagement and lowers revenue per session can still be good if sessions rise by more than revenue per session falls, and bad if the extra engagement cannibalises revenue (replaces sessions that would have earned more) or shortens users' lifetime with the product.

The identity that settles most of the argument

Revenue per user = sessions per user x revenue per session.

Illustrative result from an A/B test (users randomly assigned to old or new experience), per week:

Break-even: revenue per session can fall by up to 1 - 4.0/4.5 = 11.1% before revenue per user drops. Check this is not only first-week novelty (people trying a feature because it is new): engagement bumps often fade.

Time horizon

• Short term (1 to 4 weeks): revenue per user and guardrails (metrics that must not get worse, such as errors, support contacts and refunds).
• Medium term (8 to 12 weeks): retention of the treated cohort, since engagement without retention is not a gain. Keep a small long-term holdout (for example 5% of users deliberately stay on the old experience for a quarter, as a comparison) to measure the delayed effect.
• Long term: lifetime value (LTV, total margin one user is expected to bring). A simple steady-state estimate is monthly ARPU (average revenue per user) x gross margin (the share of revenue left after the direct cost of serving it) / monthly churn (share of users lost each month). Dividing by churn works because 1 / 5% = 20: with 5% leaving each month, the average user stays about 20 months, so lifetime margin is 20 months x ($20 x 70% = $14 a month) = $280.

Choosing between a short-term revenue lift and a retention gain

Illustrative base: ARPU $20 a month, 70% gross margin, 5% monthly churn, so LTV = 20 x 0.7 / 0.05 = $280.

A loses despite the revenue gain: with +3% ARPU, churn can rise only to 5% x 1.03 = 5.15% before LTV falls. Segment the result (new vs established users, plan, region); a feature that helps new users and hurts loyal ones needs a different decision from one that moves everyone slightly.

Reconciling teams that optimise different goals

Suppose one team owns weekly active users and another revenue per user. Do not split the difference by negotiation. Agree on a shared decision rule before the test:

1. A shared top-line, such as revenue per user and retained users together (revenue = users x revenue per user).
2. Each team's metric becomes a guardrail on the other: the engagement team may not ship if revenue per user falls by more than an agreed amount; the revenue team may not ship if retention falls.
3. When the metrics conflict, the decision is made by whoever owns the shared top-line, using the LTV estimate and its stated assumptions.
4. Record the assumptions (horizon, margin, churn) so the decision can be revisited when results arrive.

Decision and pitfalls

Ship if treatment revenue per user is up by more than the noise (the confidence interval, the plausible range for the true effect, sits entirely above zero, so the gain is unlikely to be chance), retention is flat or better at the holdout checkpoint, and no key segment is harmed. Otherwise iterate on the design. Pitfalls: judging on weeks of novelty; mixing gross and net revenue (if a discount pushes gross revenue per session to $1.00 but refunds and discounts hand back $0.08, net is $0.92, so compare like with like); adding the two teams' percentage gains instead of multiplying (+12.5% sessions and -8% revenue per session do not make +4.5%; 1.125 x 0.92 = 1.035, which is +3.5%); ignoring cost to serve the extra sessions (support, servers and payment fees that each session adds).

Direct answer. For any newly launched feature, I would examine segments chosen to test specific hypotheses about who the feature helps, hurts, or is neutral for, not an arbitrary list. Six defensible starting segments: new versus returning users, mobile versus desktop, geography (especially regions with different baseline behavior or connectivity), high-value versus low-value users, power users versus casual users, and users acquired through different channels.

Structured elaboration. Each segment earns its place because it maps to a concrete reason the effect could differ:

• New vs. returning users. New users have no prior mental model of the product, so a feature that changes a familiar workflow can help new users (less to unlearn) while confusing returning users (or vice versa).
• Mobile vs. desktop. Different input methods, screen real estate, and often different underlying codebases mean the same feature can be implemented, discovered, or used very differently across platforms.
• Geography. Connectivity, language, payment methods, and cultural expectations vary enough that a feature's effect can be real in one region and non-existent, or actively negative, in another.
• High-value vs. low-value users. A feature aimed at monetization or engagement often has a ceiling effect: users who are already highly engaged have less room to move, so the effect concentrates in the mid-tier.
• Power users vs. casual users. Power users may be annoyed by a change that removes a workflow they had already optimized around, even when the same change genuinely helps casual users.
• Acquisition channel. Users acquired through different channels arrive with different intent and expectations, so a change tuned for one channel's typical user can look flat or negative in aggregate if it helps one channel and hurts another.

Worked example. Suppose the feature is a simplified checkout flow. I would expect the effect to be largest for new users (who benefit most from fewer steps) and smallest, or even negative, for power users on desktop who had memorized the old flow's keyboard shortcuts. If a segment represents a small share of overall traffic, for example a single acquisition channel that is only 4% of sessions, I would flag upfront that any segment-level effect will need a much larger relative lift to reach statistical significance than the overall population does, since the sample size available for that slice is a small fraction of the total, and I would decide in advance whether that segment is important enough to power the experiment for directly or whether it will only ever support a directional, not a confirmatory, read.

Trade-offs and pitfalls. The temptation is to list every dimension the data warehouse happens to have and check all of them for a significant split; that turns segment selection into an uncontrolled multiple-comparisons exercise where you will find "significant" differences by chance. The discipline is to pre-specify a short list of segments tied to a real mechanism for why the effect could differ, treat any other segment split discovered after the fact as hypothesis-generating rather than confirmatory, and size the analysis (or the underlying experiment) with the smallest segment you actually care about in mind, not just the overall population.

Direct answer
The Pyramid Principle (and the closely related SCQA framework: Situation, Complication, Question, Answer) says to lead with your conclusion or recommendation first, then follow with the supporting reasons, and only then the detailed evidence. It is the opposite of building up to a conclusion at the end.

Structured elaboration

• Top of the pyramid: the answer. One sentence stating your conclusion, decision, or recommendation. A reader who stops here still knows what you think and what you want them to do.
• Middle: the key supporting reasons. Three or fewer grouped arguments (not a flat list of every fact you have) that justify the top line. Each should be able to stand on its own as a reason.
• Base: the detail. Data, examples, and caveats that back up each reason, available for a reader who wants to go deeper but not required to follow the main point.
• SCQA as the "how to open" variant: state the Situation (shared context, one line), the Complication (what changed or what's wrong), the Question this raises for the reader, and then the Answer, which is your conclusion. It is a way to earn the right to state the conclusion first by briefly reminding the reader why it matters.
• Pyramid, SCQA, and BLUF are three names for the same underlying habit, not three separate frameworks to memorize. The Pyramid Principle is the general shape (conclusion at the top, reasons and detail underneath). SCQA is one common way to earn the right to open with that conclusion by briefly reminding the reader why it matters. BLUF (Bottom-Line-Up-Front, a term that originated in military and government writing and has since spread into business writing generally) is simply the practice of stating the conclusion first, the same core move as the top of the pyramid. If you only remember one thing from all three, remember: say the answer first, then the reasons.

Worked example
Bottom-up (what most people write first): "We looked at checkout drop-off across three device types. Mobile Safari showed a 40% higher abandonment rate than Chrome. We also noticed session length was shorter on Safari. After investigating, we found the issue was a payment form rendering bug specific to Safari's autofill behavior. We recommend fixing the autofill handling this sprint."

Pyramid/BLUF (Bottom-Line-Up-Front) version of the same content: "Recommendation: fix a Safari-specific autofill bug in checkout this sprint; it is driving a 40% higher abandonment rate on that browser. We found this by comparing abandonment across device types, where Safari stood out, and traced it to autofill breaking the payment form. Full data and repro steps below."

Notice the facts are identical. Only the order changed: conclusion first, then the one or two reasons that support it, then the detail.

Trade-offs and pitfalls

• BLUF is not "skip the reasoning." A bare conclusion with no support reads as unsubstantiated; the pyramid still requires the reasons and evidence, just underneath the headline instead of before it.
• It fits most business and technical updates, but a narrative, chronological structure can be better when the sequence of events itself is the point (a postmortem timeline, a story where the reveal matters). The distinction is not seniority; it is whether the reader needs the conclusion to act, or the sequence to understand.
• A common mistake is putting three or four ungrouped reasons at the middle layer instead of grouping them into two or three real arguments; a reader cannot hold seven flat bullet points in their head, but they can hold three grouped ones.

Situation: I completed the "Advanced SQL for Data Analysts" certification on Coursera over the past six months to close a skills gap I noticed while maintaining slow dashboards and complex ad-hoc queries.

Task: I wanted practical techniques for query performance, advanced window functions, CTEs, query refactoring, and data modeling so I could deliver faster, more maintainable reporting.

Action:

• Invested ~60 hours (12 weeks × 5 hrs/week): video lectures, graded assignments, and two capstone projects.
• Covered topics: indexing and execution plans, joins and set operations, CTEs and recursive queries, window functions (ROW_NUMBER, LAG/LEAD, SUM OVER), query optimization techniques, basic data modeling and normalization.
• Applied learning immediately: refactored a slow revenue dashboard query that used multiple correlated subqueries. I rewrote it using CTEs and window functions, added appropriate indexes to the fact table, and replaced a Cartesian join with a proper join on surrogate keys.

Result: Dashboard refresh time dropped from ~8 minutes to 50 seconds (85% improvement). Stakeholders received near-real-time daily reports; I also documented the new query patterns and ran a 30-minute lunch-and-learn to share best practices with the analytics team.

This structured learning gave me concrete tools to improve performance and reproducibility, and I now include query plan checks and index considerations as part of every dashboard optimization.

Direct answer
Before presenting a chart with an unusual point or high day-to-day variance, run both statistical checks (is this point/pattern outside normal variation, or attributable to a known one-off cause) and business checks (does a known event explain it) to decide whether to smooth it, annotate it in place, or leave it as-is, since silently smoothing away a real event, or silently ignoring a genuine anomaly, are both misleading choices.

Structured elaboration

1. Statistical check: compare the point (or the recent volatility) against the metric's historical variation (e.g. is it beyond 2-3 standard deviations from a rolling baseline, or does it stay within the normal range once weekday/seasonal effects are accounted for).
2. Business check: cross-reference known events (a release, a marketing campaign, an outage, a data-pipeline change) that could explain the point; a statistically unusual point with a known, real cause should generally be annotated, not smoothed away.
3. Decision: annotate (keep the point visible with an explanation) when there's a real, known cause worth communicating; smooth (show a moving average or similar) when the audience's task is understanding the underlying trend and a single noisy point would distract without being individually meaningful; remove only in the rare case the point is a confirmed data error, and even then, disclose that a point was removed and why, rather than silently deleting it.
4. Smoothing methods and the raw-vs-smoothed trade-off: a simple moving average is easy to explain but lags real changes and can average across low-density periods misleadingly; LOESS (locally weighted smoothing) adapts better to local structure but is less transparent to a non-technical audience; exponential smoothing weights recent points more heavily, responding faster to genuine shifts than a simple moving average. Whichever method is used, show BOTH the raw series (lightly) and the smoothed overlay, so the audience can still see the underlying noise the smoothing is averaging over, rather than only ever seeing the smoothed version.

Worked example
A revenue chart averaging about $1.2M per month, with a trailing-12-month band of roughly $1.0M-$1.4M, shows one unusually high month at $2.1M, about $700K, or roughly 75%, above the trailing average and clearly outside the normal band, well past what typical month-to-month noise would produce. Checking against the marketing calendar reveals a single $700K bulk order landed that month, accounting for essentially the entire spike; because it's a real, known event (not noise or an error), the point stays on the chart with an annotation ("one-time $700K bulk order, not representative of ongoing trend") rather than being smoothed away or silently left unexplained.

Trade-offs and pitfalls
Smoothing or removing a data point without disclosing that you did so, and why, is one of the more damaging integrity issues in dashboard design, since it can make a real, actionable signal (or a real problem) invisible to the audience who needed to see it.

Hierarchical clustering builds a tree of groups instead of one flat set. The common bottom-up version (agglomerative) starts with every customer as its own group and repeatedly merges the two closest groups until one group remains. The tree of merges is the dendrogram. To get segments you cut the tree at a height: every branch the cut crosses becomes a segment. Compared with k-means, you do not fix the number of segments in advance and one run gives all of them, but it does not scale to very large tables and it never revisits an early merge.

How it builds groups

1. Compute the distance between every pair of customers (an n by n table).
2. Treat each customer as a cluster of size 1.
3. Merge the two closest clusters. Record the distance at which they merged: this is the merge height.
4. Recompute the distance from the new cluster to the others using a linkage rule, then go back to step 3 until one cluster is left.

The linkage rule defines "distance between two groups of customers":

Reading a dendrogram: a computed example

Eight customers described by annual spend (hundreds of dollars) and orders per month, in three obvious bands. The code prints the merge table (ids 0 to 7 are the customers; each merge creates the next id, 8 for step 1, 9 for step 2 and so on), the gaps between merge heights, and the cluster labels when the tree is cut into 2, 3 and 4. A second table compares linkages with k-means on two other datasets.

import numpy as np
from scipy.cluster.hierarchy import linkage, fcluster
from sklearn.cluster import KMeans
from sklearn.datasets import make_blobs, make_moons
from sklearn.metrics import adjusted_rand_score

# eight customers: (annual spend in hundreds of dollars, orders per month)
C = np.array([[1.0, 1.0], [1.5, 1.2], [1.2, 0.8],      # customers 0-2: low spend, few orders
              [5.0, 5.0], [5.4, 4.8],                  # customers 3-4: mid spend, mid orders
              [9.0, 9.0], [9.5, 9.3], [8.8, 9.6]])     # customers 5-7: high spend, many orders
Z = linkage(C, method="ward")                          # Ward linkage on Euclidean distance
print("step  merges clusters  at height  new size   (ids 0-7 are customers; 8 and up are merged clusters)")
for i, (a, b, h, n) in enumerate(Z):
    print(f"{i + 1:<5d} {int(a):>2d} + {int(b):<2d}        {h:7.2f}    {int(n)}")
heights = Z[:, 2]
print("gaps between consecutive merge heights:", np.round(np.diff(heights), 2).tolist())
for k in (2, 3, 4):
    print(f"cut into {k} clusters:", fcluster(Z, t=k, criterion="maxclust").tolist())

# linkage choice matters: agreement with the true groups (ARI) on two datasets
Xb, yb = make_blobs(n_samples=300, centers=[[0, 0], [6, 0], [3, 5]], cluster_std=1.0, random_state=0)
Xm, ym = make_moons(n_samples=300, noise=0.05, random_state=0)
print("\nARI vs truth        3 blobs   2 crescents")
for method in ["single", "complete", "average", "ward"]:
    row = []
    for X, y, k in [(Xb, yb, 3), (Xm, ym, 2)]:
        lab = fcluster(linkage(X, method=method), t=k, criterion="maxclust")
        row.append(adjusted_rand_score(y, lab))
    print(f"{method:<18s}  {row[0]:.3f}     {row[1]:.3f}")
rowk = [adjusted_rand_score(yb, KMeans(3, n_init=10, random_state=0).fit_predict(Xb)),
        adjusted_rand_score(ym, KMeans(2, n_init=10, random_state=0).fit_predict(Xm))]
print(f"{'k-means':<18s}  {rowk[0]:.3f}     {rowk[1]:.3f}")
sizes = np.bincount(fcluster(linkage(Xb, method="single"), t=3, criterion="maxclust"))[1:]
print("single linkage, 3 blobs, cluster sizes at the 3-cluster cut:", sorted(sizes.tolist()))

Output:

step  merges clusters  at height  new size   (ids 0-7 are customers; 8 and up are merged clusters)
1      0 + 2            0.28    2
2      3 + 4            0.45    2
3      1 + 8            0.58    3
4      5 + 6            0.58    2
5      7 + 11           0.73    3
6      9 + 10           8.62    5
7     12 + 13          17.84    8
gaps between consecutive merge heights: [0.16, 0.13, 0.01, 0.15, 7.88, 9.22]
cut into 2 clusters: [2, 2, 2, 2, 2, 1, 1, 1]
cut into 3 clusters: [3, 3, 3, 2, 2, 1, 1, 1]
cut into 4 clusters: [4, 4, 4, 3, 3, 1, 1, 2]

ARI vs truth        3 blobs   2 crescents
single              0.000     1.000
complete            0.990     0.297
average             0.941     0.425
ward                0.941     0.241
k-means             1.000     0.241
single linkage, 3 blobs, cluster sizes at the 3-cluster cut: [1, 1, 298]

How to read it:

• The height is the cost of the merge: for Ward it is $\sqrt{2 n_A n_B / (n_A + n_B)}$ times the distance between the two group centres, so it grows with how far apart the groups are and with their sizes; the next section recomputes steps 1, 3 and 6 by hand. Merges at low heights join customers that are alike; a tall merge joins groups that are not.
• In the merge table, steps 1 to 5 all happen at heights of 0.73 or lower and put the eight customers into the three bands (steps 1 and 3 build the band of customers 0, 1 and 2; step 2 builds the band of 3 and 4; steps 4 and 5 build the band of 5, 6 and 7). Step 6 (height 8.62) joins the low and middle bands and step 7 (height 17.84) joins the high band to those.
• To pick segments, look for a tall gap and cut a horizontal line through it. The gaps are 7.88 (between steps 5 and 6) and 9.22 (between steps 6 and 7). A cut through the first gap gives three segments, a cut through the second gives two. Both are legitimate; the data say there are three tight bands and the bands themselves are not equally far apart, so the final number is a business decision (do we want the two lower bands treated alike?), informed by the gap, not dictated by it.
• Cutting the tree at any height gives clusters that are nested: the 3-cluster solution is the 2-cluster solution with one cluster split. k-means with k=3 and k=2 need not be nested.

Recomputing a merge height, a bar picture of the tree, and the k-means shortcut

Euclidean distance is the straight-line distance between two customers' feature vectors. Standardizing a feature means subtracting its mean and dividing by its standard deviation, so dollars and orders per month count on the same scale. For Ward linkage the merge height of groups A and B is $\sqrt{\frac{2 n_A n_B}{n_A + n_B}} \times \lVert \bar{x}_A - \bar{x}_B \rVert$, where $n_A$ and $n_B$ are the group sizes and $\bar{x}$ is a group's centre. Its square divided by 2 is exactly how much the total within-group squared distance (the quantity k-means also minimises) rises when the two groups merge. Two single customers therefore merge at their plain distance. The code recomputes three merges of the table above by hand, draws the heights as bars (the dendrogram's vertical axis turned sideways), and tests the k-means shortcut on 3,000 customers.

import numpy as np
from scipy.cluster.hierarchy import linkage, fcluster
from sklearn.cluster import KMeans
from sklearn.datasets import make_blobs
from sklearn.metrics import adjusted_rand_score

C = np.array([[1.0, 1.0], [1.5, 1.2], [1.2, 0.8], [5.0, 5.0], [5.4, 4.8], [9.0, 9.0], [9.5, 9.3], [8.8, 9.6]])
Z = linkage(C, method="ward")

def ward_height(A, B):
    """Ward merge height: sqrt(2*nA*nB/(nA+nB)) times the straight-line distance between the two group centres."""
    na, nb = len(A), len(B)
    return np.sqrt(2 * na * nb / (na + nb)) * np.linalg.norm(A.mean(0) - B.mean(0))

print("step 1, customers 0 and 2:       by hand", round(ward_height(C[[0]], C[[2]]), 2), " scipy", round(Z[0, 2], 2))
print("step 3, customer 1 joins {0,2}:  by hand", round(ward_height(C[[0, 2]], C[[1]]), 2), " scipy", round(Z[2, 2], 2))
print("step 6, {0,1,2} and {3,4}:       by hand", round(ward_height(C[[0, 1, 2]], C[[3, 4]]), 2), " scipy", round(Z[5, 2], 2))
inc = ward_height(C[[0]], C[[2]]) ** 2 / 2
sse = lambda P: ((P - P.mean(0)) ** 2).sum()
print("rise in total within-group squared distance at step 1:", round(sse(C[[0, 2]]) - sse(C[[0]]) - sse(C[[2]]), 3), " height squared / 2:", round(inc, 3))

print("\nmerge heights as bars (one # per 0.5 of height); a cut through either empty stretch is legitimate")
for i, h in enumerate(Z[:, 2]):
    print(f"step {i + 1}  {h:6.2f}  " + "#" * int(round(h / 0.5)))

# tree over k-means centroids versus tree over every customer: 3000 customers, 3 true groups
X, y = make_blobs(n_samples=3000, centers=[[0, 0], [6, 0], [3, 5]], cluster_std=1.0, random_state=0)
km = KMeans(60, n_init=3, random_state=0).fit(X)
tree_c = fcluster(linkage(km.cluster_centers_, method="ward"), t=3, criterion="maxclust")
lab_centroid_tree = tree_c[km.labels_]                 # every customer takes the group of its centroid
lab_full_tree = fcluster(linkage(X, method="ward"), t=3, criterion="maxclust")
print("\nARI between the two 3-group answers:", round(adjusted_rand_score(lab_full_tree, lab_centroid_tree), 3))
print("ARI vs truth, tree over all 3000 customers:", round(adjusted_rand_score(y, lab_full_tree), 3), " tree over 60 centroids:", round(adjusted_rand_score(y, lab_centroid_tree), 3))

Output:

step 1, customers 0 and 2:       by hand 0.28  scipy 0.28
step 3, customer 1 joins {0,2}:  by hand 0.58  scipy 0.58
step 6, {0,1,2} and {3,4}:       by hand 8.62  scipy 8.62
rise in total within-group squared distance at step 1: 0.04  height squared / 2: 0.04

merge heights as bars (one # per 0.5 of height); a cut through either empty stretch is legitimate
step 1    0.28  #
step 2    0.45  #
step 3    0.58  #
step 4    0.58  #
step 5    0.73  #
step 6    8.62  #################
step 7   17.84  ####################################

ARI between the two 3-group answers: 0.987
ARI vs truth, tree over all 3000 customers: 0.984  tree over 60 centroids: 0.977

Reading it:

• Step 1 joins customers 0 and 2 at $(1.0, 1.0)$ and $(1.2, 0.8)$: the distance is $\sqrt{0.2^2 + 0.2^2} = 0.28$, and with two singletons the Ward factor is 1. Step 3 adds customer 1 to that pair: the factor is $\sqrt{2 \times 2 \times 1 / 3} = 1.155$ and the centre gap is 0.5, giving 0.58. Step 6 joins a group of three and a group of two: 8.62. The hand and scipy values agree, and the squared-height check shows the cost of merging is the rise in within-group squared distance.
• The five short bars are the merges that build the three bands, and the vertical drop between the 0.73 bar and the 8.62 bar is empty space, so a horizontal cut anywhere inside it crosses three branches. A cut between 8.62 and 17.84 crosses two. Leaf order along the bottom of a real dendrogram carries no meaning.
• The shortcut works because a k-means centroid summarises a tight local group, so building the tree over 60 centroids instead of 3,000 customers keeps the large-scale nesting: the two 3-group answers agree (ARI 0.987) and both recover the truth about equally well (0.984 and 0.977). What it loses is detail inside a centroid: customers who share a centroid can never be separated by the tree, so choose a k-means count much larger than the number of segments you want, and note that plain Ward on centroids gives every centroid equal weight whatever its size.

Compared with k-means

Linkage changes the answer, and single linkage is the one that differs most. In the second table of the output, on three well-separated blobs, k-means scores ARI 1.000 (adjusted Rand index, agreement with the true groups; 1.0 perfect, about 0 chance), complete 0.990, and average and Ward 0.941. The gaps among those four are small and shuffle from one random draw of the blobs to the next, so treat them as equivalent here. Single linkage scores 0.000 in this draw: at the 3-cluster cut it peels off two lone points and leaves one cluster of 298. In most other draws it also splits off a lone point or two, though a few draws separate the blobs cleanly. On two crescents the order reverses: single scores 1.000 in every draw tried because it follows the chain, while k-means scores about 0.2 to 0.3 (0.241 here, as does Ward in this draw) and Ward, complete and average land well below 1 in every draw. No linkage is best everywhere.

What to recommend

Use hierarchical clustering on a few thousand customers when you want to see how segments nest and explain it to stakeholders, with Ward linkage on standardized features as the starting point. For a large table, run k-means with a few hundred clusters first and build the tree over the centroids, or cluster a random sample.

Common mistakes

• Cutting the tree at the height that gives a number the business likes, without looking at the gaps.
• Using Ward or average linkage on unscaled features.
• Using single linkage on noisy data and being surprised by one giant cluster.
• Reading the left-right order of leaves as similarity: only the merge heights carry meaning.