Aggregation and Grouping Questions

Covers SQL grouping and aggregation concepts used to summarize data across rows. Key skills include using GROUP BY with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX, counting distinct values, and filtering grouped results with HAVING while understanding the difference between WHERE and HAVING. Candidates should demonstrate correct handling of NULL values in aggregates, grouping by expressions and multiple columns, and writing multi level aggregations using ROLLUP, CUBE, and GROUPING SETS. Also important is knowing when to use subqueries or common table expressions for intermediate aggregation, the difference between aggregate functions and window functions, and how grouping interacts with joins and data types. Interview questions may test correctness of queries, edge cases, performance considerations such as appropriate indexes and query plans, and the ability to transform business questions like who are the top customers or which categories have declining sales into correct aggregated SQL statements.

HardTechnical

38 practiced

Explain how implicit type casting and precision differences can affect grouping behavior. For example, grouping numeric strings vs numeric types, or floating-point imprecision leading to unexpected distinct groups. Provide practical steps to standardize group keys and avoid miscounts.

Sample Answer

Implicit type casting and precision differences can cause identical-looking values to be grouped separately, producing wrong counts or metrics.

Why it happens- Implicit casting: databases often coerce types during comparison/grouping — e.g., '123' (string) vs 123 (int) may not match or may be coerced inconsistently across engines.- Floating-point imprecision: floats store approximate values; 0.1+0.2 != 0.3 exactly, so grouping by a float column can split logically identical groups.

Examples and practical SQL fixes- Numeric strings vs numbers:

sql

-- bad: mixes types
SELECT col, COUNT(*) FROM events GROUP BY col;

-- standardize to numeric
SELECT CAST(col AS numeric) AS col_num, COUNT(*) 
FROM events
WHERE col ~ '^\d+(\.\d+)?$' -- filter invalid
GROUP BY CAST(col AS numeric);

- Floating-point rounding:

sql

-- group by rounded value
SELECT ROUND(price::numeric, 2) AS price_2, COUNT(*) 
FROM sales
GROUP BY ROUND(price::numeric, 2);

- Nulls and empty strings: normalize before grouping:

sql

SELECT COALESCE(NULLIF(TRIM(col), ''), 'UNKNOWN') AS key, COUNT(*) ...

Best-practice checklist1. Define canonical types/precision for each key (string, integer, decimal with scale).2. Clean and validate raw values (regex, TRY_CAST / safe parsers).3. Normalize at source or in ETL: store keys in single canonical column.4. Use exact types for grouping (integer, decimal) not floats; if floats unavoidable, quantize with ROUND/FLOOR.5. Handle NULL/empty consistently via COALESCE.6. Document transformations so analysts and consumers understand grouping logic.

By explicitly casting, validating, and quantizing, you avoid implicit-coercion surprises and ensure accurate group counts and aggregates.

HardTechnical

32 practiced

Write SQL to compute a running cumulative revenue per category ordered by date using window functions on table orders(category, amount, order_date). Then show how to roll that up to weekly cumulative values. Provide two SQL snippets and explain performance considerations.

Sample Answer

Approach: use window SUM() partitioned by category and ordered by date for daily cumulative; then aggregate to week (using DATE_TRUNC) and either recompute running total on weekly rows or use max cumulative per week. Below are two safe patterns.

Daily running cumulative per category:

sql

SELECT
  category,
  order_date,
  SUM(amount) AS daily_revenue,
  SUM(SUM(amount)) OVER (
    PARTITION BY category
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM orders
GROUP BY category, order_date
ORDER BY category, order_date;

Weekly roll-up (compute weekly revenue then running cumulative):

sql

WITH weekly AS (
  SELECT
    category,
    DATE_TRUNC('week', order_date)::date AS week_start,
    SUM(amount) AS weekly_revenue
  FROM orders
  GROUP BY category, DATE_TRUNC('week', order_date)
)
SELECT
  category,
  week_start,
  weekly_revenue,
  SUM(weekly_revenue) OVER (
    PARTITION BY category
    ORDER BY week_start
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS weekly_cumulative_revenue
FROM weekly
ORDER BY category, week_start;

Performance considerations:- Pre-aggregate (GROUP BY date/week) before windowing to reduce rows scanned.- Ensure indexes on (category, order_date) to speed group/order operations.- For very large datasets, consider materialized weekly aggregates or incremental ETL to avoid re-scanning raw orders repeatedly.- Window functions require sorting; memory/temp-spill can occur—increase work_mem (Postgres) or use distributed engine optimizations (Spark/BigQuery) and limit partition cardinality.- Handle missing dates/weeks by generating a calendar table and LEFT JOIN if continuous time series is required.

EasyTechnical

33 practiced

How are NULL values treated when they appear in columns used in GROUP BY clauses? Given table orders(order_id, region text, amount numeric) describe what happens to rows with region = NULL when grouped by region and how some SQL engines display the NULL group.

Sample Answer

NULL is treated as a valid grouping key — all rows whose region is NULL are aggregated together into a single group. Aggregates inside that group behave as usual: most aggregate functions (SUM, AVG, COUNT(col)) ignore NULL values in the aggregated column (COUNT(*) counts rows regardless of NULLs).

Example:

sql

SELECT region, COUNT(*) AS orders, SUM(amount) AS total_amount
FROM orders
GROUP BY region;

Result: you'll get one row per distinct region value and one row where region is NULL containing the counts/sums for all rows with region IS NULL.

Display differences: SQL engines vary in how they render the NULL group. Many show the literal NULL in the region column; some clients/display tools show it as an empty string, '(null)', or other placeholder. If you want a clearer label in reports, coalesce it:

sql

SELECT COALESCE(region, 'Unknown') AS region_label, COUNT(*)...

Pitfalls: GROUP BY NULL is different from filtering with WHERE — use WHERE region IS NULL to restrict rows, or HAVING to filter groups. Also remember NULL != NULL, but GROUP BY treats multiple NULLs as the same grouping key.

HardTechnical

30 practiced

You must produce a pivot-like report using GROUPING SETS across six dimensions (region, category, channel, device, week, campaign) that includes aggregations for each single dimension and the overall grand total. Provide the SQL pattern using GROUPING SETS and discuss the combinatorial explosion risk and techniques to limit output and optimize performance.

Sample Answer

Approach: use GROUPING SETS to explicitly produce one-row aggregations per single dimension plus the grand total. Use GROUPING() or GROUPING_ID() to label which dimension is aggregated. For six high-cardinality dims, avoid full cross-combinatorial sets — list only the six single-dimension sets plus the empty set for grand total.

sql

SELECT
  COALESCE(region, 'ALL')     AS region,
  COALESCE(category, 'ALL')   AS category,
  COALESCE(channel, 'ALL')    AS channel,
  COALESCE(device, 'ALL')     AS device,
  COALESCE(week, 'ALL')       AS week,
  COALESCE(campaign, 'ALL')   AS campaign,
  SUM(impressions)            AS impressions,
  SUM(clicks)                 AS clicks,
  SUM(conversions)            AS conversions,
  ROUND(SUM(revenue),2)       AS revenue,
  GROUPING(region)  AS g_region,
  GROUPING(category) AS g_category,
  GROUPING(channel)  AS g_channel,
  GROUPING(device)   AS g_device,
  GROUPING(week)     AS g_week,
  GROUPING(campaign) AS g_campaign
FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-06-30'  -- restrict scan
GROUP BY GROUPING SETS (
  (region),
  (category),
  (channel),
  (device),
  (week),
  (campaign),
  ()            -- grand total
)
ORDER BY g_region, g_category, g_channel, g_device, g_week, g_campaign;

Key points / performance & explosion mitigation:- GROUPING SETS above produces 7 grouping rows per distinct key value set only for the single dims + total — not the full 2^6 combinations.- Risk: each single-dimension aggregation still returns as many rows as the cardinality of that dimension; with high-cardinality dims (campaign, device, etc.) result can be large.Techniques to limit output and optimize:- Filter early (WHERE, date ranges, campaign lists) to reduce scanned rows.- Pre-aggregate: create materialized views or summary tables (e.g., daily_campaign_summary) and query those.- Use partition pruning (partition by date) and proper indexes (bitmap or columnar) to speed scans.- Push predicates into storage (e.g., use columnar DBs like Redshift/Snowflake/BigQuery) and leverage clustering/partitioning.- Sample or cap results for exploratory pivot (LIMIT or TOP N by revenue).- If you need many combos later, build incremental ETL that computes and stores needed roll-ups to avoid repeated heavy queries.- Use GROUPING_ID() to detect which column is aggregated for labeling/formatting in reports.

HardSystem Design

38 practiced

Design the fact and dimension model to support common aggregated queries for product sales reporting. Specify grain, key design, partitioning, surrogate keys, which aggregates to precompute, and trade-offs between normalization and denormalization for query performance and storage.

Sample Answer

Requirements & grain:- Support common aggregated product sales queries: daily/weekly/monthly sales by product, category, region, customer segment, channel, and promotion. - Grain: one row = one sales transaction line (order_line_id) — i.e., a sold quantity of a product on a date at a price, before/after discounts.

High-level star schema:- Fact_Sales(order_line_sk, order_date_sk, product_sk, store_sk, customer_sk, channel_sk, promotion_sk, quantity, unit_price, discount_amt, cost_amt, ext_sales_amt)- Dim_Date(date_sk, date, day, week, month, quarter, year, is_holiday)- Dim_Product(product_sk, product_id, name, brand, category, subcategory, attributes...)- Dim_Store(store_sk, store_id, region, country, store_type)- Dim_Customer(customer_sk, customer_id, segment, lifetime_value_bucket...)- Dim_Channel, Dim_Promotion

Keys & surrogate keys:- Use integer surrogate keys (SK) for all dimensions; natural keys (product_id, order_id) stored as attributes. Surrogates speed joins and handle slowly changing dimensions (SCD).- Fact uses compact integer foreign keys and a primary composite on (order_line_sk) or surrogate fact_id for append-only.

Partitioning & indexing:- Partition Fact_Sales by order_date (monthly or weekly) for fast pruning of time-range queries. For large tables, subpartition by region or store for locality. - Cluster/sort keys: (date_sk, product_sk) or (date_sk, store_sk) depending on common query patterns. Create bitmap or b-tree indexes on product_sk, store_sk, customer_sk for ad-hoc filters.

Precomputed aggregates:- Materialized views / aggregate tables: - agg_daily_product: date_sk, product_sk, sum_qty, sum_sales, sum_cost, sum_discount - agg_monthly_category_region: month_sk, category_sk, region, metrics - agg_week_channel: week_sk, channel_sk, metrics- Precompute commonly used rollups (day→month→quarter) and combinations with high cardinality dimensions (product x date, category x region x month).- Maintain with incremental ETL (CDC or insert-only delta) to keep near-real-time.

Normalization vs denormalization trade-offs:- Normalize dims to avoid duplication and support attribute changes (use SCD type 2 when historical correctness needed). - Denormalize frequently queried attributes (product name, category) into facts or materialized views to reduce join cost for dashboards — accept storage blow-up for query speed. - For ultra-low latency dashboards, use pre-aggregated wide tables. For flexible ad-hoc analysis, keep normalized dims + aggregate layer.

Other considerations:- SCD strategy: Type 2 for product/customer when historical context matters (e.g., category changes). Store both current and effective SKs in fact or use date_sk joins to SCD history table. - Compression and columnar storage (Parquet/Delta/Redshift Spectrum) to reduce storage and speed scans. - Monitor query patterns and maintain only aggregates that materially reduce compute/cost. Balance freshness vs. storage/maintenance cost.

This design balances analyst-friendly querying (star schema + aggregates) with storage and maintainability trade-offs.

Unlock Full Question Bank

Get access to hundreds of Aggregation and Grouping interview questions and detailed answers.

Join thousands of developers preparing for their dream job.