SQL for Data Analysis Questions

Direct answer

A plain GROUP BY over transactions only ever produces rows for months that actually had activity, so months with zero transactions are missing, not zero. Fix it by generating a full 12-month calendar spine and LEFT JOINing the aggregated transactions onto it, then wrapping the aggregate columns in COALESCE(..., 0) so a month with no matches shows 0 instead of disappearing.

Approach

1. Build a "spine" of every month you need in the output, independent of whether any transaction happened. In portable SQL this is typically a recursive CTE (common table expression) or a generate_series (Postgres) / calendar table; this answer uses SQLite's recursive CTE.
2. Aggregate transactions by month separately, filtered to the same window.
3. LEFT JOIN the spine to the aggregated transactions on month, so every calendar month survives even with zero matches.
4. COALESCE the aggregate columns to 0 for the LEFT JOIN's NULL rows.

Worked example (sqlite3, verified)

Sample data (reference "current month" pinned to 2024-04, so the trailing 12 full months are 2023-04 through 2024-03):

CREATE TABLE transactions (transaction_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER, transaction_date TEXT);
INSERT INTO transactions VALUES
  (1, 1, 100, '2023-05-03'),
  (2, 2, 200, '2023-05-20'),
  (3, 1, 150, '2023-07-01'),
  (4, 3, 300, '2023-07-15'),
  (5, 1, 50,  '2023-07-28'),
  -- 2023-06 and 2023-09 have zero transactions and must still appear as zero rows
  (6, 2, 400, '2023-10-01'),
  (7, 4, 250, '2024-02-14'),
  (8, 1, 999, '2022-12-01');  -- outside the 12-month window

Naive GROUP BY, shown first to demonstrate the bug:

SELECT strftime('%Y-%m', transaction_date) AS year_month,
       SUM(amount) AS total_revenue, COUNT(DISTINCT customer_id) AS unique_customers
FROM transactions
WHERE transaction_date >= '2023-04-01' AND transaction_date < '2024-04-01'
GROUP BY year_month
ORDER BY year_month;

Result: only 4 rows (2023-05, 2023-07, 2023-10, 2024-02). 8 of the 12 months in the window are silently missing, exactly the bug the question is asking about.

Calendar spine via recursive CTE:

WITH RECURSIVE months(month_start) AS (
  SELECT date('2023-04-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2024-03-01')
)
SELECT month_start FROM months ORDER BY month_start;

This recursive CTE runs in passes: the first SELECT produces a single base row (2023-04-01), then each following pass takes the previous pass's output row and builds the next one from it (date(month_start, '+1 month')), feeding back into months again. It keeps generating new rows this way until a pass's output fails the WHERE condition (month_start < date('2024-03-01')), at which point the recursion stops.

Result: 12 rows, 2023-04-01 through 2024-03-01, one per calendar month, confirmed by count.

Full zero-filled query:

WITH RECURSIVE months(month_start) AS (
  SELECT date('2023-04-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2024-03-01')
),
monthly_txn AS (
  SELECT strftime('%Y-%m-01', transaction_date) AS month_start,
         SUM(amount) AS total_revenue, COUNT(DISTINCT customer_id) AS unique_customers
  FROM transactions
  WHERE transaction_date >= '2023-04-01' AND transaction_date < '2024-04-01'
  GROUP BY month_start
)
SELECT strftime('%Y-%m', m.month_start) AS year_month,
       COALESCE(t.total_revenue, 0) AS total_revenue,
       COALESCE(t.unique_customers, 0) AS unique_customers
FROM months m
LEFT JOIN monthly_txn t ON t.month_start = m.month_start
ORDER BY year_month;

Result: 12 rows, exactly one per month. 2023-04, 2023-06, 2023-08, 2023-09, 2023-11, 2023-12, 2024-01, and 2024-03 all show (0, 0); 2023-05 shows (300, 2), 2023-07 shows (500, 2), 2023-10 shows (400, 1), 2024-02 shows (250, 1). This matches the naive query's 4 non-zero months exactly, now with the 8 zero months made explicit instead of silently absent.

Key points

• The calendar spine must be built independent of the transactions table; if you generate months by extracting distinct months from transactions itself, you can never produce a month with zero rows, since it wouldn't exist in the source data to extract from.
• LEFT JOIN direction matters: the spine is the "keep every row" side, monthly_txn is the side that's allowed to have no match.
• COALESCE is applied to the aggregate output columns (total_revenue, unique_customers), not to the join key; the join key match/mismatch is what determines which rows need the COALESCE fallback in the first place.

Complexity, edge cases & pitfalls

• Dialect variation: this recursive CTE approach is portable across SQLite, Postgres, and SQL Server; Postgres also supports the more compact generate_series(date1, date2, interval '1 month'), and BigQuery has GENERATE_DATE_ARRAY. The underlying LEFT JOIN plus COALESCE pattern is identical across all of them, only the spine-generation syntax changes.
• If the transactions table is large, filtering the date window before aggregating (as done in monthly_txn) keeps the aggregation cheap; the spine itself is always cheap (12 rows), so the LEFT JOIN's cost is dominated by the aggregation, not the spine generation.
• A common mistake is generating the spine as a set of month labels (strings like '2023-04') rather than actual dates, then trying to compare or sort them lexicographically; that happens to work for zero-padded YYYY-MM strings but breaks immediately for anything that isn't already zero-padded and sortable as text.
• unique_customers in a zero-activity month is correctly 0 via COALESCE; be careful not to instead COALESCE the join key or the raw customer_id column, which would produce a wrong (non-zero) sentinel value rather than a true zero count.

Structure I would use

• Raw data tab: one Excel Table holding the source data only.
• Calc/helper tab: normalized fields, date groups, and any lightweight helper columns.
• Dashboard tab: KPI tiles, a few trend charts, and slicers/timelines.

Formulas and layout

• Use the raw table as the single source of truth.
• Build KPI tiles with SUMIFS, COUNTIFS, or AVERAGEIFS when the numbers need to react instantly to filters.
• If the workbook is large, I would summarize first in PivotTables and point the dashboard to those results.
• Use named ranges or table references instead of hard-coded cell addresses.

Performance choices

• Avoid volatile functions unless they are truly needed.
• Prefer table references over whole-column formulas.
• Keep chart series small and focused.
• Use one or two helper columns for grouping instead of many nested formulas.
• Reduce duplicated logic by calculating once and reusing the result.

Drill-down and usability

• Add slicers for the main dimensions.
• Add a timeline for date filtering.
• Keep labels plain-language and numbers formatted consistently.
• Hide technical sheets so stakeholders only see the dashboard and the raw source if needed.

My rule is to make the dashboard fast to open, easy to refresh, and simple enough that a non-technical user can filter it without breaking the workbook.

Direct answer

Produce one output row per column by computing the same four metrics (null count, distinct count, min, max) for every column and stacking the results with UNION ALL, so the profile reads top-to-bottom as a column-by-column summary instead of one wide, unreadable row. For a table you'll profile repeatedly, generate that UNION ALL SQL programmatically from information_schema.columns instead of hand-writing it, so adding a column to the table doesn't require remembering to update the profiling query too.

Structured elaboration

1. One row per column, not one row per table. A wide row with customer_id_null_count, customer_id_distinct_count, email_null_count, ... for a 20-column table is unreadable; a UNION ALL stack with column_name, null_count, distinct_count, min_value, max_value scales to any width and is trivial to scan or plot.
2. Cast min/max to a common type (VARCHAR) across the UNION ALL branches, since different columns have different underlying types and UNION ALL requires matching types column-by-column.
3. Automate the generation for real use. Querying information_schema.columns for a table name and building the SELECT ... UNION ALL text programmatically means the profiling query never goes stale when a column is added or dropped. This is the single biggest lever for "profile hundreds of tables nightly" at scale, more than any per-query optimization.

Worked example

Seed data: a customers table, 7 rows, with NULLs scattered across every column except customer_id.

Hand-written profile:

SELECT 'customer_id' AS column_name,
       COUNT(*) FILTER (WHERE customer_id IS NULL) AS null_count,
       COUNT(DISTINCT customer_id) AS distinct_count,
       MIN(customer_id)::VARCHAR AS min_value, MAX(customer_id)::VARCHAR AS max_value
FROM customers
UNION ALL
SELECT 'email', COUNT(*) FILTER (WHERE email IS NULL), COUNT(DISTINCT email),
       MIN(email), MAX(email) FROM customers
UNION ALL
SELECT 'signup_date', COUNT(*) FILTER (WHERE signup_date IS NULL), COUNT(DISTINCT signup_date),
       MIN(signup_date)::VARCHAR, MAX(signup_date)::VARCHAR FROM customers
UNION ALL
SELECT 'country', COUNT(*) FILTER (WHERE country IS NULL), COUNT(DISTINCT country),
       MIN(country), MAX(country) FROM customers
UNION ALL
SELECT 'lifetime_value', COUNT(*) FILTER (WHERE lifetime_value IS NULL), COUNT(DISTINCT lifetime_value),
       MIN(lifetime_value)::VARCHAR, MAX(lifetime_value)::VARCHAR FROM customers;

Real output:

Now generate the same query programmatically from information_schema.columns (this is the piece that makes profiling scale to many tables):

SELECT string_agg(
  'SELECT ''' || column_name || ''' AS column_name, ' ||
  'COUNT(*) FILTER (WHERE ' || column_name || ' IS NULL) AS null_count, ' ||
  'COUNT(DISTINCT ' || column_name || ') AS distinct_count, ' ||
  'MIN(' || column_name || ')::VARCHAR AS min_value, ' ||
  'MAX(' || column_name || ')::VARCHAR AS max_value FROM customers',
  ' UNION ALL '
  ORDER BY ordinal_position
) AS generated_sql
FROM information_schema.columns
WHERE table_name = 'customers';

This produced the exact SELECT ... UNION ALL ... text shown above, generated from the table's actual column list rather than typed by hand. Running that generated text produced the identical result set shown above, confirming the generator and the hand-written version agree.

Trade-offs & pitfalls

• MIN/MAX on a text column is lexical, not semantic ('Zebra' < 'apple' in a case-sensitive collation because uppercase sorts before lowercase); treat min/max on strings as "what shape the values take," not a business ordering.
• Sample values (a LIMIT 3 of distinct non-null values, via ORDER BY random() or similar) are more useful than min/max for spotting formatting problems (mixed casing, stray whitespace) that a lexical min/max won't surface; add that alongside min/max, not instead of it.
• COUNT(DISTINCT ...) on a huge table is a real cost (a full distinct scan per column); for a nightly profile over very large tables, an approximate distinct count (e.g. HyperLogLog-based functions where the engine supports them) trades a small amount of accuracy for a large reduction in scan cost.
• Regenerating and running the profiling SQL from information_schema on every run means it never goes stale, but also means it profiles a column you may not care about (an internal audit column, say); filter information_schema.columns by name pattern or a maintained allowlist if that's a concern.

Direct answer

GROUP BY collapses every row in a group down into one summary row (a count, a sum, an average). A window function computes that same kind of aggregate or ranking, but keeps one output row per input row, because the calculation runs "over a window" of related rows without collapsing them. Reach for a window function whenever the result needs to sit alongside the original row-level detail, such as a running total, a cumulative count, or a rank per row, rather than replacing it.

Structured elaboration

The signal to look for in the question: does the expected output have "one row per X" (X = user, order, day) where X still needs to appear, but also needs an aggregate or rank attached? If yes, that is a window function, not GROUP BY, even if an aggregate function like SUM or COUNT is involved.

Worked example

Seed data: 10 signups across 4 weekly cohorts.

CREATE TABLE signups (user_id INTEGER, cohort_week DATE);
-- 3 users in week of 2026-06-01, 2 in 2026-06-08, 4 in 2026-06-15, 1 in 2026-06-22

GROUP BY collapses the rows you need (one row per user is gone):

SELECT cohort_week, COUNT(user_id) AS new_users
FROM signups GROUP BY cohort_week ORDER BY cohort_week;

Real output:

A window function is the right tool when the stakeholder also wants the running cumulative total next to each week (still one row per week, group-level, but now with a value that depends on prior groups too):

SELECT cohort_week, COUNT(user_id) AS weekly_new,
       SUM(COUNT(user_id)) OVER (ORDER BY cohort_week) AS cumulative_new
FROM signups GROUP BY cohort_week ORDER BY cohort_week;

Real output:

And when you need one row per user (not per week) with a rank attached, GROUP BY cannot produce that at all, because GROUP BY would collapse the users away. A window function with no GROUP BY does it directly:

SELECT user_id, cohort_week,
       RANK() OVER (PARTITION BY cohort_week ORDER BY user_id) AS signup_rank_in_week
FROM signups ORDER BY cohort_week, user_id;

Real output (first 4 of 10 rows; every user_id is preserved):

Trade-offs & pitfalls

• The most common mistake is reaching for a self-join or a correlated subquery to get a running total or rank, when a window function does the same thing in one pass and is both simpler to read and generally faster.
• Window functions do not reduce row count, so on very large tables (hundreds of millions of rows) they can be more expensive than a GROUP BY that collapses the data first; if the frame is unbounded (like ORDER BY cohort_week with no explicit bound, which defaults to "everything up to the current row"), the engine has to look back arbitrarily far for every row.
• GROUP BY and window functions are not mutually exclusive: the cumulative-total example above uses both together, aggregating with GROUP BY first and then running a window function over the grouped result.

Join each transaction's date to a fiscal_calendar lookup table on the exact date, and group by the fiscal columns it returns. Model this as a join, not date arithmetic (DATE_TRUNC, manual month offsets), because fiscal period boundaries are a business decision that can be irregular: they don't have to land on calendar month-ends, and no closed-form date formula can capture that reliably. A calendar table stores the mapping as data, once, and every query just joins to it.

The join

SELECT
  fc.fiscal_year,
  fc.fiscal_month_number,
  fc.fiscal_period_label,
  COUNT(t.txn_id) AS txn_count,
  SUM(t.amount)   AS total_amount
FROM transactions t
LEFT JOIN fiscal_calendar fc ON fc.cal_date = t.txn_date
GROUP BY 1, 2, 3
ORDER BY fc.fiscal_year, fc.fiscal_month_number;

Worked example: why the calendar table matters, not just style preference

Run in sqlite3; strftime('%Y-%m', txn_date) is sqlite3's month-truncation function. The equivalent in Postgres is DATE_TRUNC('month', txn_date), in BigQuery DATE_TRUNC(txn_date, MONTH), in SQL Server DATEFROMPARTS(YEAR(txn_date), MONTH(txn_date), 1), all illustrating the same naive date-arithmetic approach the calendar-table join is being compared against.

Seed a small "4-4-5" style fiscal calendar (a retail convention where each quarter's three months split into a 4-week, a 4-week, and a 5-week period) where fiscal period 1 runs 2026-01-18 through 2026-02-14 (crossing the January/February month boundary) and fiscal period 2 starts 2026-02-15 (mid-month):

fiscal_calendar: (2026-01-30, FY2026, P01), (2026-02-01, FY2026, P01), (2026-02-15, FY2026, P02)
transactions:    (1, 2026-01-30, 100), (2, 2026-02-01, 200), (3, 2026-02-15, 50), (4, 2026-03-01, 75)

Calendar-join result:

fiscal_period_label  fiscal_year  fiscal_month_number  txn_count  total_amount
UNMAPPED                                               1          75
FY2026-P01           2026         1                    2          300
FY2026-P02           2026         2                    1          50

Compare that to naive calendar-month grouping (strftime('%Y-%m', txn_date) or equivalently DATE_TRUNC('month', txn_date)) on the exact same rows:

calendar_month  total_amount
2026-01         100
2026-02         250
2026-03         75

The two approaches disagree, and the disagreement is the whole point: fiscal period P01 correctly groups transactions 1 and 2 together (total 300, they're in the same fiscal period even though one is dated in January and one in February), but naive month-grouping splits them apart (100 in "2026-01", part of 250 in "2026-02"). Meanwhile naive "2026-02" of 250 wrongly merges transaction 2 (fiscal P01) with transaction 3 (fiscal P02), two different fiscal periods that happen to share a calendar month. No amount of date-arithmetic cleverness reproduces the calendar table's answer here, because the boundary (Feb 15) isn't a fixed offset from anything, it's whatever the finance calendar says it is that year.

Transaction 4 (2026-03-01) has no row in this intentionally partial calendar, and the LEFT JOIN surfaces it as UNMAPPED rather than silently dropping it, the same silent-drop risk that applies to any join. In production, that's your signal the calendar table needs to be extended, not a query bug.

Trade-offs and pitfalls

• The calendar table has to be maintained and kept current. It needs a row for every date the business might report on, extended annually (or whenever the fiscal calendar is redefined), and if it falls behind, transactions land in UNMAPPED (via the LEFT JOIN) rather than being silently miscategorized, which is the safer failure mode but still needs monitoring.
• Indexing. fiscal_calendar.cal_date as a primary key (as seeded here) makes this an efficient equality join, meaningfully cheaper than a range join; it's an equi-join specifically because the calendar table pre-computes the answer for every date, so you don't need BETWEEN logic at query time.
• Multi-entity / multi-fiscal-year complexity. Companies with subsidiaries on different fiscal calendars (common after M&A) need the calendar table keyed by entity as well as date, an extra join column, not a fundamentally different pattern.
• Auditability is the real payoff. Beyond correctness, centralizing the fiscal logic in one table means a fiscal calendar change (the company shifts its year-end) is a data update in one place, not a hunt through every report's date-arithmetic logic to find and fix every place the old boundary was hardcoded.