Skew & distributions — speak whale, decile, Pareto, P99.

User archetypes — whales, dolphins, minnows.

Any consumer platform with a long-tail engagement distribution has three named cohorts. The names come from gaming/casino analytics in the 2000s; they're now standard at Netflix, Spotify, YouTube, Meta, Stripe, and every B2C SaaS. The interview signal isn't knowing the words — it's knowing what to do when you've classified a user as each.

PERCENT_RANK() OVER (ORDER BY total_hours DESC) <= 0.05
-- or: NTILE(20) ... WHERE bucket = 1   (top 5%)
-- or: NTILE(100) ... WHERE bucket = 1  (top 1%)

PERCENT_RANK() OVER (ORDER BY total_hours DESC) BETWEEN 0.15 AND 0.75
-- or: NTILE(10) ... WHERE bucket BETWEEN 3 AND 8  (deciles 3-8)

PERCENT_RANK() OVER (ORDER BY total_hours DESC) >= 0.80
-- or: NTILE(10) ... WHERE bucket IN (9, 10)  (bottom 20%)

Quantiles — decile, quartile, quintile, percentile.

"Dividing the population into equal-sized groups based on a metric" is a single concept with five names depending on how many buckets you want. Interviewers expect you to use the right one without thinking.

Top decile vs top 1st decile — the ambiguity that trips juniors

Top decile = decile 1 in DESC order = top 10% of users. But some teams number deciles ascending (decile 1 = bottom 10%, decile 10 = top 10%). Always confirm the convention before writing SQL — and prefer naming the threshold ("top 10%") over the bucket number ("decile 1") when the audience is non-technical.

-- Convention A: DESC — decile 1 = whales
SELECT user_id, NTILE(10) OVER (ORDER BY hours DESC) AS decile FROM user_metrics;
-- decile 1 = top 10% (whales), decile 10 = bottom 10% (minnows)

-- Convention B: ASC — decile 10 = whales
SELECT user_id, NTILE(10) OVER (ORDER BY hours ASC) AS decile FROM user_metrics;
-- decile 1 = bottom 10% (minnows), decile 10 = top 10% (whales)

PERCENTILE_CONT vs PERCENTILE_DISC

Distribution shapes — name the curve, not just the number.

Pareto distribution — the 80/20 rule

Vilfredo Pareto, 1896, observing that 80% of Italian land was owned by 20% of the population. The same shape shows up in every consumer platform: 80% of watch hours come from 20% of users; 80% of revenue from 20% of customers; 80% of GitHub commits from 20% of contributors.

Netflix context: 20% of your users (the whales) likely account for 80% of total watch hours. SQL strategy: use cumulative sums (running totals) to show where the "bulge" is — order users DESC by hours, run the cumulative sum, the threshold where it crosses 80% of the global total is your "vital few" cutoff. See the 80/20 Pareto question for the full pattern.

Long tail (power law) — Pareto's stretched cousin

A distribution where a high frequency of "minnows" trails off into a very long, thin tail of "whales". Visually: a huge spike on the left (lots of people watching 0-1 hours) and a line that stretches far to the right (one viewer watching 400 hours). The "tail" can stretch to extremes — one user at 400 hours/month while the bulk of users sit at 0-2 hours.

Two-word interview test: if the interviewer says "long tail", you should immediately reach for NTILE(10) + decile rollups rather than AVG. The average is meaningless on a long tail because the extremes pull it sideways.

Normal distribution (bell curve) — the one tech data rarely has

This is what most people assume data looks like, but in tech it's rarely true for engagement. Definition: most users are in the middle, with very few at the extreme high or low ends. Symmetric around the mean, ~68% within 1σ, ~95% within 2σ, ~99.7% within 3σ. Useful for measurement error, manufacturing tolerances, and IQ scores. Almost never useful for user engagement.

Interview tip — the standard-deviation trap. If an interviewer asks why you aren't using simple STDDEV for outlier detection, the answer is: "because engagement data is skewed, and standard deviation assumes a normal distribution." The whales pull the mean way above the median, σ is dominated by outliers, and STDDEV-based outlier detection silently flags half your dolphins as anomalies while missing the actual whales.

Skewness — the asymmetry that breaks naive analytics

Positive (right) skew means the "tail" is on the right side. This is the Netflix example — most users are on the low end, but the whales pull the average to the right. Interview insight: when data is positively skewed, the mean (average) will be much higher than the median. Always mention the median to show you aren't being fooled by the whales.

Mean vs median — the L5 tell.

The single most-tested signal in senior-IC data interviews: which one do you use, and why?

The L5 sentence. When asked "what's the average watch time per user", the senior answer is: "Average watch time per user is X hours. The median is Y hours. The gap tells you the platform is heavily skewed toward whales — for any operational decision, use the median; for capacity planning, look at P99."

Skew ratio — quantifying how skewed

A simple, defensible metric for "how skewed":

SELECT
  AVG(hours)                                           AS mean_hours,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours)   AS median_hours,
  AVG(hours) / NULLIF(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours), 0)
                                                       AS skew_ratio_mean_over_median,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY hours) /
    NULLIF(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours), 0)
                                                       AS p99_over_p50
FROM user_metrics;

Healthy uniform: skew_ratio ≈ 1.0. Healthy long-tail (Pareto): skew_ratio between 2 and 4. Extreme power-law (one viral product, gaming whales): skew_ratio > 10. The p99_over_p50 ratio is the more interviewer-friendly variant — "how many times more does the top 1% consume than the median?"

LAD gap — when whales get miscategorised as minnows.

Late-Arriving Dimensions (LAD): the user just watched a brand-new show, but the content metadata hasn't propagated to the global catalog yet. Their event row is correct, but joins to dim_content return NULL, and the user's hours look artificially low for a brief window. If your decile-classification job runs during that window, a whale gets stamped as a minnow. Worse: they appear in churn-risk campaigns and you spam your best customers with retention offers.

The defensive pattern — Lag-Tolerant View

Emit an is_metadata_complete flag on every row. Power-user / VIP / classification reports filter to = TRUE; ops and SRE reports run unfiltered (they want to see the LAD-pending rows).

-- The LAD-tolerant decile classification
WITH user_metrics AS (
    SELECT user_id, SUM(watch_seconds) / 3600.0 AS hours
    FROM viewing_activity
    WHERE is_metadata_complete = TRUE   -- ← the LAD guard
    GROUP BY user_id
),
ranked AS (
    SELECT user_id, hours,
        NTILE(10) OVER (ORDER BY hours DESC) AS decile,
        CASE
            WHEN NTILE(20) OVER (ORDER BY hours DESC) = 1  THEN 'whale'
            WHEN NTILE(10) OVER (ORDER BY hours DESC) <= 2 THEN 'heavy_dolphin'
            WHEN NTILE(10) OVER (ORDER BY hours DESC) <= 8 THEN 'dolphin'
            ELSE                                                'minnow'
        END AS archetype
    FROM user_metrics
)
SELECT * FROM ranked ORDER BY hours DESC;

When NOT to use the LAD guard

• Real-time SLA dashboards — you want to see incomplete rows so you can spot the propagation backlog.
• SRE incident drills — the LAD-pending count is itself the metric.
• Financial close / regulatory reporting — never; everything must be complete by close.

The rule of thumb: any report that classifies a user's behaviour (decile, archetype, churn risk, cohort) needs the LAD guard. Any report that observes the system itself wants no guard.

Interview summary table + the one-sentence script.

The Final L5 Tip — the one sentence that proves senior-level thinking

The 90-second expanded form

That one paragraph signals: (1) you know the distribution is skewed, (2) you know which functions are appropriate, (3) you know the standard-deviation trap, (4) you know the LAD trap, (5) you've shipped this in production. Senior-IC bar cleared in 90 seconds.

Run the SQL — practice questions

• customer_growth-0031 — Power-User Skew Analysis · NTILE decile rollup + PERCENTILE_CONT P50/P99 + LAD-tolerant view
• community_solved-0082 — 80/20 Pareto cumulative contribution (Netflix-flavoured probes)
• customer_growth-0032 — Whale / Dolphin / Minnow archetype classification with skewness signals
• customer_growth-0026 — NTILE decile velocity cohorts
• customer_growth-0028 — DAU/MAU with HyperLogLog (non-additivity + HLL probes)
• customer_growth-0033 — Dynamic Pivot (the trap that has no clean answer) · JSON aggregation + three-approach probe