Essential Data Analytics Terms Every Beginner Should Know

-- Same "orders" table, different grain — very different answers

-- GRAIN: one row per order
SELECT COUNT(*) AS orders
FROM orders;
-- → 50,000

-- GRAIN: one row per user (using group by)
SELECT COUNT(DISTINCT user_id) AS buyers
FROM orders;
-- → 12,000

-- GRAIN: one row per user per day
SELECT DATE(created_at) AS d, COUNT(DISTINCT user_id) AS buyers
FROM orders
GROUP BY d;
-- → 3,000 on day 1, 2,800 on day 2, etc.

-- Why this matters:
-- "How many buyers did we have?" depends entirely
-- on grain. If you don't know the grain, you're
-- answering a different question than you think.

-- The most common bug in analytics:
-- joining two tables at different grains
-- produces a row explosion. You count 50,000
-- orders, then join users, get 200,000 rows.
-- Why? Because a user with 4 orders becomes
-- 4 rows. Now COUNT(*) says 200,000.
-- The number is wrong and nobody notices until
-- a stakeholder asks "why does the dashboard say
-- 4x more orders?".

-- Rule: always ask "what does one row represent?"
-- before writing any query.

-- Metric vs dimension — the shape of every query

-- Metric: revenue (a number you measure)
-- Dimension: region (a way to slice that number)

SELECT
  region,                          -- dimension
  SUM(amount) AS revenue,          -- metric
  COUNT(*) AS orders               -- metric
FROM orders
GROUP BY region
ORDER BY revenue DESC;

-- Output:
-- region     revenue    orders
-- North      4,50,000   1,200
-- South      3,20,000     900
-- West       2,80,000     800

-- If you're confused about which is which:
-- - Metrics are numbers you SUM, AVG, COUNT
-- - Dimensions are things you GROUP BY
-- - A dimension can be a date (GROUP BY DATE(col))

-- Common interview question:
-- "What are the top 3 dimensions for slicing revenue?"
-- Answer: region, product category, month.
-- If you can name dimensions and metrics on demand,
-- interviewers relax. It sounds basic, but most
-- candidates can't do it under pressure.

-- The two terms you'll use every single day

-- JOIN: combine orders with user attributes
-- (find total revenue per city)

SELECT
  u.city,
  SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.city
ORDER BY revenue DESC;

-- LEFT JOIN: users with OR without orders
-- (find users who never ordered)

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS orders_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
HAVING COUNT(o.id) = 0;

-- CTE (WITH): break a complex query into steps
-- (find top customers by revenue per region)

WITH customer_revenue AS (
  SELECT
    user_id,
    SUM(amount) AS revenue
  FROM orders
  WHERE created_at >= '2026-01-01'
  GROUP BY user_id
),
ranked AS (
  SELECT
    u.city,
    u.name,
    cr.revenue,
    RANK() OVER (
      PARTITION BY u.city
      ORDER BY cr.revenue DESC
    ) AS rnk
  FROM customer_revenue cr
  JOIN users u ON u.id = cr.user_id
)
SELECT city, name, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY city, rnk;

-- Why CTEs matter:
-- A 40-line query with CTEs is readable.
-- The same query nested is a debugging nightmare.
-- Interviewers notice when you use CTEs — it's
-- a signal of real production experience.

-- Window functions — the term that separates
-- "knows SQL" from "actually works in analytics"

-- 1. Running total (cumulative revenue by day)
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total
FROM daily_revenue;

-- 2. Rank within a group (top products per category)
SELECT
  category,
  product,
  revenue,
  RANK() OVER (
    PARTITION BY category
    ORDER BY revenue DESC
  ) AS rank_in_category
FROM product_revenue;

-- 3. Previous row comparison (day-over-day change)
SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY date) AS change
FROM daily_revenue;

-- 4. Moving average (7-day rolling revenue)
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d
FROM daily_revenue;

-- Why interviewers love this:
-- - Tests if you understand the difference
--   between aggregate (collapses) and window
--   (keeps rows)
-- - Running totals, ranks and LAG/LEAD appear
--   in almost every real analytics task
-- - Candidate who can write a window function
--   unprompted gets hired faster than one who
--   can recite every SQL keyword

-- Churn rate (monthly, for a subscription product)
-- Interpretation: what % of last month's users
-- are NOT active this month

WITH monthly_active AS (
  SELECT DISTINCT
    DATE_TRUNC('month', event_at)::date AS month,
    user_id
  FROM events
  WHERE event_at >= CURRENT_DATE - INTERVAL '3 months'
),
last_month AS (
  SELECT user_id FROM monthly_active
  WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
),
this_month AS (
  SELECT user_id FROM monthly_active
  WHERE month = DATE_TRUNC('month', CURRENT_DATE)
)
SELECT
  COUNT(*) FILTER (WHERE lm.user_id IS NULL) AS churned,
  COUNT(*) AS starting_users,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE lm.user_id IS NULL)
    / COUNT(*),
    2
  ) AS churn_rate_pct
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id;

-- What to watch for:
-- - "Churn" must be defined. Churned = no events
--   in the month? Or explicitly cancelled?
-- - Both are valid; both give different numbers.
-- - Always state your definition in the report.

-- LTV (simple version)
SELECT
  ROUND(AVG(revenue), 2) AS arpu,
  ROUND(AVG(lifetime_months), 1) AS avg_lifetime_months,
  ROUND(AVG(revenue) * AVG(lifetime_months), 2) AS ltv
FROM user_summary;

-- Retention cohort: the query every product
-- analyst must know how to write

WITH first_visit AS (
  SELECT
    user_id,
    MIN(DATE_TRUNC('month', event_at))::date AS cohort_month
  FROM events
  GROUP BY user_id
),
user_activity AS (
  SELECT
    user_id,
    DATE_TRUNC('month', event_at)::date AS activity_month
  FROM events
  GROUP BY user_id, activity_month
),
cohort_data AS (
  SELECT
    fv.cohort_month,
    ua.activity_month,
    DATE_PART('month',
      AGE(ua.activity_month, fv.cohort_month)
    )::int AS month_number,
    COUNT(DISTINCT fv.user_id) AS users
  FROM first_visit fv
  JOIN user_activity ua ON ua.user_id = fv.user_id
  GROUP BY 1, 2, 3
)
SELECT
  cohort_month,
  month_number,
  users,
  ROUND(
    100.0 * users
      / FIRST_VALUE(users) OVER (
          PARTITION BY cohort_month
          ORDER BY month_number
        ),
    1
  ) AS retention_pct
FROM cohort_data
ORDER BY cohort_month, month_number;

-- Output looks like:
-- cohort_month | month_number | users | retention_pct
-- 2026-01-01   | 0            | 1000  | 100.0
-- 2026-01-01   | 1            | 420   | 42.0
-- 2026-01-01   | 2            | 320   | 32.0
-- 2026-01-01   | 3            | 280   | 28.0

-- This is the single most-asked query in
-- product analytics interviews.
-- If you can write this from memory, you're
-- hireable.

Cohort vs Segment — different, often confused.

SEGMENT
  - A slice of users defined by an attribute
  - Static or dynamic (e.g. "users in Mumbai")
  - Used for: comparing behaviour across groups
  - Example: "revenue by segment (business
    vs consumer)"

COHORT
  - A slice of users defined by when they
    first did something
  - Fixed at the time of grouping (e.g. "users
    who signed up in January")
  - Used for: tracking behaviour over time
  - Example: "retention of January signups vs
    February signups"

WHY IT MATTERS
  Segments answer "who are our best users
  right now?"
  Cohorts answer "are we getting better at
  retaining new users?"

  Most "growth is slowing" conversations
  need a cohort view. Most "which channel
  is best" conversations need a segment view.

Interviewer test:
  If you say "cohort" when you mean "segment",
  they'll assume you haven't worked with
  product data.

  If you can correctly pick between them for
  a given question, that alone signals
  real experience.

Funnel analysis — where users drop off.

Definition: a series of steps a user must
complete to reach a goal, with the drop-off
at each step measured.

Example funnel: signup → verify email →
add first item → checkout → purchase.

Query shape (simplified):

WITH step1 AS (
  SELECT DISTINCT user_id
  FROM events WHERE name = 'signup'
),
step2 AS (
  SELECT DISTINCT user_id
  FROM events WHERE name = 'email_verified'
),
step3 AS (
  SELECT DISTINCT user_id
  FROM events WHERE name = 'item_added'
)
SELECT
  (SELECT COUNT(*) FROM step1) AS signup,
  (SELECT COUNT(*) FROM step2 s
     JOIN step1 ON s.user_id = step1.user_id) AS verified,
  (SELECT COUNT(*) FROM step3 s
     JOIN step1 ON s.user_id = step1.user_id) AS added_item;

What makes a funnel useful:
  ✓ Steps are defined clearly
  ✓ Order matters (verified BEFORE adding item)
  ✓ Time window is set (within 7 days)
  ✓ It's segmented (new vs returning, mobile vs web)

What makes it useless:
  ✗ Mixing steps that can happen in any order
  ✗ No time window
  ✗ Only looking at the overall % (not per-step drop)

The interesting number is almost never the
final conversion rate. It's the step with the
biggest drop.

Attribution — which channel gets credit.

Definition: the model that assigns credit for
a conversion to the marketing touchpoints a
user encountered on the way.

Common models:

  LAST-CLICK
    Full credit to the last channel before
    conversion. Simple but overweights the
    bottom of the funnel.

  FIRST-CLICK
    Full credit to the first touch. Good for
    brand-awareness analysis.

  LINEAR
    Equal credit to every touch. Overstates
    cheap channels that appear often.

  TIME-DECAY
    More credit to touches closer to conversion.
    Reasonable default.

  DATA-DRIVEN
    Uses a model to assign credit based on
    actual uplift. Better but requires
    experimentation.

What interviewers want to hear:
  "Attribution is always a choice, and no
   model is objectively correct. The right
   model depends on the question:
   - 'Which campaign brought users in?' → first-click
   - 'Which campaign closed the deal?' → last-click
   - 'How do we split the budget?' → data-driven"

If you answer "last-click is the correct
model", you'll be seen as junior. Every senior
analyst knows last-click oversimplifies.

# ab_test.py — running a proper two-proportion test
import numpy as np
from scipy.stats import norm

def two_prop_test(control_conv, control_n,
                  variant_conv, variant_n,
                  alpha=0.05):
    p_c = control_conv / control_n
    p_v = variant_conv / variant_n

    # Pooled proportion under H0
    p_pool = (control_conv + variant_conv) / (control_n + variant_n)
    se = np.sqrt(p_pool * (1 - p_pool) *
                 (1/control_n + 1/variant_n))

    # z-statistic and two-sided p-value
    z = (p_v - p_c) / se
    p_value = 2 * (1 - norm.cdf(abs(z)))

    # 95% confidence interval on the lift
    se_diff = np.sqrt(
        p_c*(1-p_c)/control_n + p_v*(1-p_v)/variant_n
    )
    z_crit = norm.ppf(1 - alpha/2)
    ci_low = (p_v - p_c) - z_crit * se_diff
    ci_high = (p_v - p_c) + z_crit * se_diff

    # Relative lift
    relative_lift = (p_v - p_c) / p_c

    return {
        "p_control": round(p_c, 4),
        "p_variant": round(p_v, 4),
        "absolute_lift": round(p_v - p_c, 4),
        "relative_lift_pct": round(relative_lift * 100, 2),
        "p_value": round(p_value, 4),
        "ci_95_pct": (
            round(ci_low * 100, 2),
            round(ci_high * 100, 2),
        ),
        "significant": p_value < alpha,
    }

# Example
result = two_prop_test(
    control_conv=420, control_n=5000,
    variant_conv=475, variant_n=5000,
)
print(result)

# Output:
# {'p_control': 0.084,
#  'p_variant': 0.095,
#  'absolute_lift': 0.011,
#  'relative_lift_pct': 13.1,
#  'p_value': 0.0456,
#  'ci_95_pct': (0.04, 2.16),
#  'significant': True}

# Notice two things:
# ✓ The p-value says "significant"
# ✓ The CI says the effect could be as low as
#   0.04% or as high as 2.16% — that's a huge
#   range for a business decision
#
# Interpretation: it's significant, but the
# uncertainty is large. That's the difference
# between "statistically significant" and
# "practically significant".

Reading the output — what to actually report:

GOOD REPORT
"Variant lifted conversion by 13.1% relative
 (0.084 → 0.095, absolute +1.1pp).
 p = 0.046, 95% CI [0.04pp, 2.16pp].
 We ran the test for 2 weeks with 5,000 users
 per arm. Sample size was pre-computed for a
 1pp MDE at 80% power. Result is significant
 but the CI is wide — I'd recommend a 2-week
 follow-up to narrow it before shipping to 100%."

Why this is good:
  ✓ Reports relative AND absolute lift
  ✓ Reports p-value AND confidence interval
  ✓ Reports sample size and duration
  ✓ Reports the MDE and power
  ✓ Interprets, not just reports
  ✓ Recommends an action

BAD REPORT
"It's significant, p < 0.05. Ship it."

Why this is bad:
  ✗ No absolute lift
  ✗ No CI (maybe the effect could be negative)
  ✗ No sample size or duration
  ✗ No interpretation of practical significance
  ✗ No recommendation, just "ship"

The best interview answer about A/B tests:
  "A p-value tells you if the difference is
   likely real. A confidence interval tells
   you how big it might be. Practical
   significance tells you if it's worth
   shipping. I care about all three."

That sentence alone signals more experience
than 10 memorised statistics definitions.