Data: SQL + Analysis

Correctness first, then clarity, then speed. A fast query that returns the wrong number is worse than no query. Verify what the data actually is before you trust what it seems to say.

For the visual design of any chart (color, layout, accessibility), load the dataviz skill before writing plotting code. This skill covers the query and analysis mechanics.

SQL (Postgres)

Correctness

• Know your grain (one row = one what?). Joins that fan out silently inflate SUM/COUNT — check row counts before and after a join.
• NULL is not a value: = NULL is never true, NOT IN (… NULL …) returns nothing, and aggregates skip NULLs. Use IS [NOT] NULL, COALESCE, and think about what NULL means for each column.
• WHERE filters rows; HAVING filters groups. Filtering an aggregate in WHERE is a bug.
• Integer division truncates (1/2 = 0) — cast to numeric for ratios.
• Anything not in an aggregate must be in GROUP BY. Beware SELECT * with GROUP BY.
• Read timezones explicitly: store/compare in UTC (timestamptz), convert at the edges.

Safety

• Always parameterize — bind values, never string-concatenate user input into SQL (injection + type bugs). Allowlist dynamic identifiers (table/column/sort) since they can't be bound.
• Route user-data access through lib/auth/; don't hand-write raw queries that dodge the app's authorization (project rule).
• Preview destructive statements: run the SELECT form of a DELETE/UPDATE's WHERE first and check the count. Wrap risky writes in a transaction you can roll back. Never run an unbounded UPDATE/DELETE.
• Treat db/migrations/* as append-only once merged — never edit a merged migration; write a new one (project rule + harness guard).

Performance

• Read the plan: EXPLAIN (ANALYZE, BUFFERS). A Seq Scan on a large table in a hot path wants an index; a Nested Loop over many rows may want a different join.
• Index the columns you filter and join on; a composite index's column order matters (leftmost-prefix). Indexes speed reads and slow writes — add deliberately.
• SELECT only the columns you need. Paginate with keyset (WHERE id > :last) over OFFSET for deep pages.
• Prefer set-based SQL over row-by-row loops in app code; let the database do the join/aggregate.
• Use CTEs for readability, but know they can be optimization fences for very large data — measure.

Python analysis (pandas / numpy / matplotlib)

Load and sanity-check first

• Before analyzing: df.shape, df.dtypes, df.head(), df.isna().sum(), df.describe(). Confirm the grain and that types are what you expect (dates parsed as datetimes, numerics not strings).
• Look at nulls, duplicates, and outliers explicitly — decide to drop, fill, or keep, and say which. Silent dropna changes your denominator.

pandas

• Vectorize; avoid iterrows/apply for anything you can express as a column operation (orders of magnitude faster and clearer).
• Set/verify the join keys' types before merge; check how and the resulting row count (a many-to-many merge explodes rows). Use validate= to assert the relationship.
• Beware chained-assignment (df[mask]['col'] = …) — use .loc[mask, 'col'] = ….
• groupby(...).agg(...) with named aggregations for clarity. Watch how NaNs and empty groups are handled.
• Reproducibility: fix a seed for any sampling; don't rely on row order unless you sorted.

numpy

• Mind dtypes and integer overflow; use float64 when accumulating. Understand broadcasting before reshaping.
• Prefer nanmean/nansum when NaNs are expected, and be explicit about the axis.

matplotlib

• Label axes and units, title the plot, and never mislead: don't truncate the y-axis to exaggerate, match chart type to the data (distribution → histogram/box; trend → line; comparison → bar; relationship → scatter).
• One idea per chart. Show sample size when a summary could hide it.
• Load dataviz for color, scale, and accessibility choices before finalizing.

Reporting results

• State the population, filters, and time window behind every number ("active users, last 30 days, excluding internal accounts").
• Distinguish correlation from cause; report the denominator and sample size, not just the headline metric.
• Make it reproducible: the query or notebook that produced the number should be runnable and reviewable, not a screenshot.