Run a mock round

Window functions compute a value across a set of rows related to the current row without collapsing them, unlike GROUP BY. They preserve row granularity.

SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_seq,
  LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_amount
FROM orders;

Common uses: running totals, moving averages, ranking within groups, deduplication with ROW_NUMBER, and comparing to previous rows with LAG and LEAD. PARTITION BY defines the group, ORDER BY the sequence, and the frame which rows are included. They are powerful but can be expensive on very large tables, so filter early.

Averages hide the shape of a distribution. Percentiles describe typical and extreme behaviour, which matters for user experience and capacity planning.

For example, average page load time may look fine while the 95th percentile is terrible for a meaningful share of users. Latency objectives are usually expressed as p95 or p99. Revenue per user is heavy-tailed, so the median is a better typical value than the mean, and the top 1 percent of customers often drive a large share of revenue.

SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY load_ms) AS p50,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY load_ms) AS p95
FROM page_loads;

Use histograms and box plots to inspect skew and outliers, and always state which percentile you mean. Segment percentiles by device, region or plan to find users having the worst experience.

A funnel measures how many users progress through an ordered sequence of steps, such as view, add to cart, checkout and purchase. The conversion rate at each step shows where users drop off.

WITH steps AS (
  SELECT user_id,
    MAX(CASE WHEN event = 'view' THEN 1 ELSE 0 END) AS s1,
    MAX(CASE WHEN event = 'add_cart' THEN 1 ELSE 0 END) AS s2,
    MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS s3
  FROM events
  GROUP BY user_id
)
SELECT SUM(s1) AS views, SUM(s2) AS carts, SUM(s3) AS purchases
FROM steps;

Key decisions: define the completion window, decide whether steps must occur in order, and choose whether to count sessions or users. Break results down by channel, device or cohort to find the worst segment. Segmenting the funnel usually yields more actionable insight than the overall rate.

Cohort analysis groups users by a shared start event, usually their first activity period, and tracks behaviour over subsequent periods. Retention is the share of a cohort still active in period n.

SELECT
  date_trunc('week', first_seen) AS cohort,
  week_number,
  COUNT(DISTINCT user_id) AS active
FROM user_activity
GROUP BY 1, 2;

A retention triangle or heatmap shows cohorts as rows and periods as columns, making it easy to compare whether newer cohorts retain better. Common metrics are day-1, day-7 and day-30 retention, and the flattening point of the curve indicates a stable core of users.

Pitfalls: define active precisely, avoid mixing calendar time with cohort age, and watch for survivorship bias. Comparing retention curves before and after a product change is one of the most reliable ways to judge whether the change improved engagement.

First understand why data is missing, because the mechanism determines the right approach.

• MCAR: missing completely at random, safe to drop rows with a small loss of power.
• MAR: missing at random given observed variables, so imputation using other features is reasonable.
• MNAR: missing not at random, where the missingness itself carries information, such as high earners refusing to report income. Simple imputation then biases results.

Options: drop rows or columns, mean or median imputation, model-based imputation such as MICE or k-NN, or flag missingness with an indicator variable and let the model use it. For time series, forward-fill carefully and never leak future values backward.

df["income"] = df["income"].fillna(df["income"].median())
df["income_missing"] = df["income"].isna().astype(int)

Always report how much data was missing and test sensitivity across methods.

A metric tree decomposes a top-level business goal into the drivers that influence it. For example, revenue equals active customers times purchase frequency times average order value. Each node can be broken down further, linking strategy to measurable inputs.

Benefits: it shows where a team can have leverage, prevents optimizing a proxy that harms the top goal, and connects daily work to outcomes.

Metric governance is the discipline of defining, documenting and owning metrics. Without it, different teams compute active user or churn differently, meetings devolve into debates about numbers, and dashboards contradict each other. Practices include a metrics layer with version-controlled SQL, a data dictionary, a single owner per metric, and change review.

metric: weekly_active_users
definition: distinct users with a qualifying event in a 7-day window
owner: growth-analytics

Governance turns metrics into an asset rather than a source of confusion.

Start with the decision the audience needs to make, then structure the narrative around it: situation, complication, question and answer. Lead with the recommendation and supporting evidence, and keep details in an appendix.

Guidelines:

• Know the audience. Executives want the headline, impact and ask; analysts want methodology.
• Use one clear message per chart, with a descriptive title that states the takeaway.
• Quantify impact in business units such as revenue, churn or hours saved.
• Address the strongest counterargument and state limitations honestly.
• Provide a confidence level and range, not false precision.
• End with concrete next steps and owners.

Avoid jargon, avoid dumping every query, and never overwhelm with a wall of numbers. Rehearse the first 30 seconds, when attention is highest. Good storytelling makes the insight memorable and the decision easy.

Start from decisions, not charts. Ask what action each viewer takes and which questions they need answered.

Principles:

• Lead with a small number of headline KPIs, then allow drill-down.
• Show trends and comparisons, not just current values, and include targets.
• Make the time grain and definitions visible.
• Put the most important metric top-left where the eye lands.
• Use the right chart: lines for trends, bars for comparisons, tables for precision.
• Avoid pie charts for many categories and never truncate a bar axis.
• Highlight anomalies and add period-over-period change.

Agree on metric definitions and a single source of truth so teams do not argue about numbers. Design for the audience: executives want outcomes, operators want granular, actionable detail. Validate with a small group, iterate, and retire unused tiles. A dashboard is a product, not a one-off export.

DAU is daily active users, the number of unique users who perform a meaningful action in a day. MAU is the same over a month. The action must be defined consistently: a login may not be meaningful for a content app, while a read or post is.

Stickiness is DAU divided by MAU, the share of monthly users who return on any given day. A high ratio means frequent habitual use. Social and messaging apps often exceed 50 percent, while e-commerce is typically much lower.

SELECT COUNT(DISTINCT CASE WHEN day = current_date THEN user_id END) AS dau,
       COUNT(DISTINCT user_id) AS mau
FROM activity
WHERE day >= current_date - INTERVAL '30 days';

Watch for definitional drift and time zones, which can distort daily boundaries. Pair these with retention curves: DAU/MAU shows frequency, retention shows whether users return at all.