Aggregation Functions and Group By Questions

Fundamentals of aggregation in Structured Query Language covering aggregate functions such as COUNT, SUM, AVG, MIN, and MAX and how to use them to calculate totals, averages, minima, maxima, and row counts. Includes mastery of the GROUP BY clause to group rows by one or more dimensions such as customer, product, region, or time period, and producing metrics like total revenue by month, average order value by product, or count of transactions by date. Covers the HAVING clause for filtering aggregated groups and explains how it differs from WHERE, which filters rows before aggregation. Also addresses related topics commonly tested in interviews and practical problems: grouping by multiple columns, grouping on expressions and date truncation, using DISTINCT inside aggregates, handling NULL values, ordering and limiting grouped results, using aggregates in subqueries or derived tables, and basic performance considerations when aggregating large datasets. Practice examples include calculating monthly revenue, finding customers with more than a threshold number of orders, and identifying top products by sales.

HardSystem Design

57 practiced

Design a daily ETL and reporting architecture that produces aggregated KPIs (daily active users, total revenue, avg session length) for Tableau and Power BI dashboards, given an event stream of 100M events/day. Cover ingestion, staging, incremental aggregation, failure handling, and how you expose data to BI tools for low-latency dashboards.

Sample Answer

Requirements & constraints:- Ingest 100M events/day (≈1.16k events/sec avg, peaks higher), produce daily KPIs: DAU, total revenue, avg session length. Support Tableau & Power BI with low-latency dashboards (sub-minute to few minutes for refreshed aggregates). Must support incremental loads, replay/dedup, retries, and observability.

High-level architecture:Event stream → Ingestion (Kafka) → Raw landing (cloud object store, partitioned by date/hour) → Stream processing (Flink/Beam/KSQL) → Staging tables (append-only in data warehouse) → Incremental aggregator (scheduled SQL/DBT or Spark batch + CDC) → Serving layer (aggregated tables / OLAP like Snowflake/BigQuery/Redshift + materialized views) → BI access via live connections / cached extracts.

Components & responsibilities:- Ingestion: Kafka with producers publishing events. Use partitioning by event_type or user_id, topic retention short. Mirror to object store using Kafka Connect (Parquet/JSONL) for raw immutable archive.- Stream processing: Real-time enrichment (user join from dim store), deduplication (event-id windowed), sessionization (stateful Flink) to emit session start/end and session durations.- Staging: Raw events in object store + landing tables in warehouse for replayable history.- Incremental aggregation: Use a nightly/continuous job that reads only new partitions (date/hour) and merges into daily-aggregate fact table using MERGE/UPSERT. Produce KPI facts: daily_active_users, total_revenue, avg_session_length. Use watermarking and late data window (e.g., accept 2-hour lateness).- Serving: Store aggregated daily rows in partitioned, clustered table in data warehouse; create materialized views for near-real-time rollups; maintain fast lookup tables for last 7/30 days.- BI exposure: Provide semantic layer (metrics table with field-level definitions) and grant BI tools direct read to warehouse. For low latency, use: - Live queries to materialized views for sub-minute freshness - Power BI extracts / Tableau Hyper scheduled every 5–15 minutes for dashboards needing caching - Query cache & result caching in warehouse to reduce load

Failure handling & data quality:- Exactly-once / dedupe: Use event_id dedupe in streaming and idempotent MERGE in warehouse.- Late and out-of-order: Sessionization with watermarks, allow backfills that reprocess affected partitions and replay aggregates.- Retries & dead-letter: Kafka Connect retries + dead-letter queue to object store for malformed events; alerting on DLQ growth.- Monitoring: Track lag (Kafka consumer lag, processing latency), SLA on aggregate freshness, row counts, reconciliation jobs (compare raw totals vs aggregated totals), and data freshness dashboards.- Backfill & corrections: All raw events retained; re-run incremental jobs for date ranges; use job versioning and audit logs.

Scalability & cost trade-offs:- Use serverless/managed warehouse (BigQuery/Snowflake) for elastic concurrency and fast materialized views.- Stream processing stateful on autoscaling cluster; partition by user_id to scale sessionization.- Balance runtime freshness vs cost: real-time Flink emits session events; heavy joins done in batch.

Key operational points for a Data Analyst:- Provide clear metric definitions and SQL for KPIs (single source of truth).- Maintain a metrics catalog and tests (dbt tests) so dashboard numbers are explainable.- Implement scheduled checks that reconcile dashboard numbers with raw event counts; surface anomalies to stakeholders.

HardSystem Design

57 practiced

A dashboard serves many concurrent users with live DB queries for aggregates. Propose an architecture that uses pre-aggregation layers, caching, and materialized views to handle concurrency while preserving acceptable freshness (e.g., 5-15 minutes). Describe cache invalidation and refresh strategies.

Sample Answer

Requirements:- Serve many concurrent dashboard users with aggregates refreshed every 5–15 minutes.- Low latency (<200ms) for reads, tolerable staleness window 5–15m.- Cost-efficient, easy to operate by analytics team (SQL-friendly).

High-level architecture:- OLTP / event source → ETL/streaming (Kafka/Flink) → Pre-aggregation layer → Analytical DB (columnar / OLAP) with materialized views → Cache (CDN / in-memory like Redis + application-level cache) → Dashboard (Tableau/PowerBI/BI web).

Core components & responsibilities:1. Streaming ETL: ingest events, compute rolling aggregations or write raw events to data lake.2. Pre-aggregation engine: produce time-bucketed aggregates (minute/hour) via incremental jobs (Spark/Flink) and write to analytic DB.3. Materialized Views in analytical DB (e.g., ClickHouse, BigQuery, Redshift): store pre-aggregates for common queries (by dimension, time-window).4. Cache layer: Redis for fast keyed aggregates and optionally CDN for public dashboards.5. API / Query layer: lookup cache first, fallback to MV/DB, with async recompute when needed.

Refresh & invalidation strategies:- Periodic refresh: schedule incremental MV refreshes every 1–5 minutes for high-volume metrics, 15 minutes for lower-priority ones.- Incremental/append-only updates: use partitioned MVs by time bucket to refresh only newest partitions.- Cache TTL aligned with MV freshness: set Redis TTL = refresh interval (+ small jitter). Example: MV updated every 5m → cache TTL 6–7m.- Write-through for critical updates: when ETL writes new pre-aggregate, atomically update cache entries for affected keys.- Lazy refresh (cache-aside + background warmers): on cache miss, serve last MV result and enqueue background recompute; use stale-while-revalidate pattern so dashboards show data within staleness SLA.- Invalidation on schema/logic change: full MV rebuild + purge cache; flag dashboards to indicate rebuild in progress.

Concurrency & consistency:- Use read replicas / separate analytic cluster to isolate dashboards from OLTP.- Idempotent incremental jobs and partitioned locks to avoid race conditions during concurrent refreshes.- Provide metadata (timestamp, partition) with each response so analysts know data age.

Trade-offs:- Shorter freshness increases compute/cost. Use tiered freshness: critical KPIs refresh every 1–5m; secondary metrics every 15m.- Materialized views + incremental pre-aggregations minimize on-the-fly heavy queries while keeping system simple for analysts using SQL.

Operational notes:- Monitor latency, MV refresh success, cache hit ratio; alert on increases.- Maintain documented list of pre-aggregations tied to dashboard queries to avoid explosion of MVs.

MediumTechnical

52 practiced

Design a SQL approach to aggregate hierarchical geography sales: country -> region -> city. Provide an example using ROLLUP or GROUPING SETS to produce subtotals at each level and explain how to display hierarchy in a BI tool that consumes this result.

Sample Answer

Approach: use GROUP BY with ROLLUP or GROUPING SETS to compute city, region, country and grand totals in one query. Use GROUPING()/GROUPING_ID() (or GROUPING_ID equivalent) to detect subtotal rows and build a "level" and label for BI tools.

Example using ROLLUP (Postgres / SQL Server syntax similar):

sql

SELECT
  COALESCE(country,'All Countries') AS country,
  COALESCE(region,'All Regions') AS region,
  COALESCE(city,'All Cities') AS city,
  SUM(sales) AS sales,
  GROUPING(country) AS g_country,
  GROUPING(region)  AS g_region,
  GROUPING(city)    AS g_city,
  (GROUPING(country)*1 + GROUPING(region)*2 + GROUPING(city)*4) AS grouping_mask
FROM sales_fact
GROUP BY ROLLUP(country, region, city)
ORDER BY
  -- ensure country first, then region, then city, with totals after members
  country NULLS LAST, region NULLS LAST, city NULLS LAST;

How to interpret grouping flags:- g_country=0,g_region=0,g_city=0 → detail (city)- g_country=0,g_region=0,g_city=1 → region subtotal (city NULL)- g_country=0,g_region=1,g_city=1 → country subtotal (region & city NULL)- all 1 → grand total

Preparing for BI (Tableau/Power BI):- Add a "level" column derived from grouping flags (e.g., CASE WHEN g_city=0 THEN 'City' WHEN g_region=0 THEN 'Region' WHEN g_country=0 THEN 'Country' ELSE 'Total' END).- Provide a single "name" column and a "parent" key for hierarchical visualizations: - name = COALESCE(city, region, country, 'All') - parent = CASE WHEN city IS NOT NULL THEN region WHEN region IS NOT NULL THEN country WHEN country IS NOT NULL THEN 'All' ELSE NULL END- Sort by country, region, city and use the level column as the hierarchy level in the BI tool. This lets tree tables, nested totals, or drill-down visuals show the hierarchy and subtotals correctly.

Notes & edge cases:- GROUPING functions differ slightly by dialect (GROUPING_ID available in some systems).- For large tables, pre-aggregate or add appropriate indexes to optimize GROUP BY.- Handle NULLs in source data (use COALESCE) so subtotals align with expected labels.

HardTechnical

58 practiced

Count distinct pairs (user_id, product_id) in different SQL engines. Show approaches to compute the count of unique (user_id, product_id) pairs in PostgreSQL, BigQuery, and a database that lacks tuple DISTINCT support. Explain pitfalls like string concatenation collisions and type differences.

Sample Answer

To count distinct (user_id, product_id) pairs reliably across engines, prefer set-based DISTINCT on tuples where supported, avoid naive string concatenation, and handle NULLs and types explicitly.

PostgreSQL (tuple DISTINCT)

sql

SELECT COUNT(DISTINCT (user_id, product_id)) AS distinct_pairs
FROM events;

This treats the pair as a composite value; NULL in either column is part of tuple equality semantics.

BigQuery (supports STRUCT DISTINCT)

sql

SELECT COUNT(DISTINCT STRUCT(user_id, product_id)) AS distinct_pairs
FROM `project.dataset.events`;

STRUCT preserves types and nulls; efficient when combined with partitioning/sharding.

Databases without tuple DISTINCT (or older engines)Option A — GROUP BY:

sql

SELECT COUNT(*) AS distinct_pairs
FROM (
  SELECT user_id, product_id
  FROM events
  GROUP BY user_id, product_id
) t;

Option B — CONCAT key (avoid unless safe) — if you must:

sql

SELECT COUNT(DISTINCT CONCAT(CAST(user_id AS VARCHAR), '||', CAST(product_id AS VARCHAR))) FROM events;

Pitfalls and best practices:- String concatenation collisions: different pairs can map to same string ("12|3" vs "1|23") — use an unambiguous separator and CAST types; but separators can still collide with data values.- Type differences: cast numeric and UUID types explicitly and consistently; binary vs text encodings matter.- NULL handling: CONCAT often yields NULL; use COALESCE to normalize nulls.- Performance: COUNT(DISTINCT ...) can be expensive on large tables — use pre-aggregated tables, hashing, or approximate functions (e.g., BigQuery APPROX_COUNT_DISTINCT on a hash of STRUCT) when exactness isn’t required.- Collation and encoding: string comparisons are affected by collation; avoid relying on string ordering.Recommendation: use native tuple/struct DISTINCT or GROUP BY. Only use concatenation with strong normalization (CAST, delimiter, COALESCE) and be aware of correctness vs performance trade-offs.

HardTechnical

51 practiced

Your report uses a correlated subquery that computes customer_total in the SELECT for each row and runs slowly on 100M rows. Explain why correlated subqueries can be slow and show a refactor that computes customer totals once (e.g., using a CTE or JOIN to an aggregated table). Provide before-and-after query sketches.

Sample Answer

Correlated subqueries are slow because they execute per outer row (row-by-row), causing the inner query to run N times — with 100M rows this is catastrophic. The optimizer can sometimes inline or cache results, but for non-trivial aggregates across groups you should precompute aggregates once and join.

Before (slow — correlated subquery runs per row):

sql

SELECT
  o.order_id,
  o.customer_id,
  o.order_date,
  o.amount,
  (
    SELECT SUM(amount)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
      AND o2.order_date < o.order_date
  ) AS customer_total_prior
FROM orders o
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31';

After (fast — compute totals once via CTE or aggregated table and join):Option A — CTE with aggregated totals (if you need total per customer overall):

sql

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS customer_total
  FROM orders
  GROUP BY customer_id
)
SELECT o.order_id, o.customer_id, o.order_date, o.amount, ct.customer_total
FROM orders o
LEFT JOIN customer_totals ct USING (customer_id)
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31';

Option B — windowed cumulative sum (if you need cumulative prior-to-order per customer):

sql

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    AS customer_total_prior
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Why these are better:- Aggregation/CTE computes each customer once: O(#customers) work instead of O(#rows * cost of inner query).- JOIN or window functions let the engine use indexes, parallel plans, and avoid repeated scans.Practical tips:- Ensure proper indexes on customer_id and order_date.- For very large tables, pre-aggregate into a summary table refreshed periodically.- Validate NULL handling and date-bound logic to match original correlated semantics.

Unlock Full Question Bank

Get access to hundreds of Aggregation Functions and Group By interview questions and detailed answers.

Join thousands of developers preparing for their dream job.