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.

HardSystem Design

36 practiced

Design schema and aggregation strategy for a multi-tenant SaaS analytics product that must compute daily active users, revenue, and funnel conversion per tenant. Discuss tenant isolation, storage layout (single table vs per-tenant), sharding heavy tenants, pre-aggregation, query routing, cost allocation, and how to prevent cross-tenant data leakage.

Sample Answer

Requirements & constraints:- Functional: daily active users (DAU), revenue, funnel conversion per tenant, historical retention, ad-hoc analytics.- Non-functional: strict tenant isolation, cost transparency, low-latency queries for recent data, scale to many small tenants + few heavy hitters.

High-level approach:- Ingest event stream (Kafka) → stream processor (Flink/Beam) for realtime enrichment → write raw events to a partitioned data lake (Parquet on S3) + write to OLAP store for pre-aggregates (ClickHouse / Snowflake / BigQuery).- Maintain two logical layers: Raw events (for ML/diagnostics) and Aggregates (for fast analytics).

Schema (logical):- Raw events (single multi-tenant table; partitioned & indexed) tenant_id (UUID), event_time (ts), user_id, event_type, revenue_cents, properties (JSON), ingest_shard Partition by tenant_id hash prefix + date; cluster by event_time,user_id.

- Daily aggregates (per-tenant rows): tenant_id, date, dau, revenue_cents, funnel_stage_1, funnel_stage_2, funnel_conversion_rate, updated_at

Storage layout decision:- Default: single multi-tenant tables with strong partitioning by tenant_id and date. Pros: query planner leverages statistics, easier cross-tenant maintenance, lower metadata overhead. Cons: hotspots for heavy tenants.- Exception: heavy tenants get isolated physical resources (dedicated table or dedicated ClickHouse shard / BigQuery slot reservations) to avoid noisy neighbors.

Sharding heavy tenants:- Detect heavy tenants via ingress metrics; migrate them to dedicated shard/cluster or use tenant-aware hashing to spread their partitions across multiple physical nodes.- For user-scoped operations shard by tenant_id + user_id to maintain session affinity.

Pre-aggregation strategy:- Streaming pre-aggregates: compute per-minute/per-hour partial aggregates in stream jobs keyed by (tenant_id,date,event_type/user_id) and upsert into OLAP.- Daily rollups: nightly job materializes final daily aggregates into the DailyAggregates table and stores time-series for funnels (windowed counts per funnel stage).- Maintain both incremental and full-refresh pipelines for correctness.

Query routing:- API/service layer accepts tenant_id and auth token, enforces RBAC, then routes: - Small queries -> aggregates store - Ad-hoc or long-range queries -> run on raw data via query engine (Athena/Presto) with cost limits - Heavy-tenant queries routed to their dedicated cluster

Cost allocation & metering:- Tag all storage/compute by tenant_id at ingestion. Emit usage metrics: bytes stored, compute seconds, query counts.- Chargeback model: base tier + usage (storage + query + reserved slots). Provide dashboards per tenant.

Prevent cross-tenant leakage:- Enforce row-level security: all queries injected with tenant_id filter at service layer; use SQL UDFs or query gateway to add WHERE tenant_id = :tenant.- Use separate auth tokens and IAM roles; limit direct cluster access. For multi-tenant shared clusters, enable enforced Row-Level Security (RLS) or use views that mask other tenants.- Encryption-at-rest and in-transit, tenant-specific encryption keys for heavy tenants.- Automated tests & audits: chaos tests, unit tests verifying that every query path applies tenant filter; periodic data access reviews and anomaly detection for cross-tenant join patterns.- For ML: produce training datasets per-tenant or use differential privacy / synthetic data when models are trained across tenants.

Trade-offs:- Single table + partitioning minimizes ops overhead but requires careful hotspot management and enforcement of RLS. Per-tenant isolation increases security and predictable performance but increases cost/maintenance.- Pre-aggregates reduce query latency and cost but increase storage and pipeline complexity and risk of staleness—mitigate via near-realtime streaming updates + fallback to raw queries.

Metrics & monitoring:- Track query latency, cost per query, tenant ingress qps, aggregate freshness, and any failed RLS enforcement. Use alerts to auto-migrate noisy tenants.

This design balances performance, security, and cost transparency while enabling the Data Science workflows that need raw events plus fast aggregate lookups for modeling and reporting.

HardTechnical

31 practiced

sessions(session_id, user_id, events jsonb) where events is an array of objects {event_type, value}. Write Postgres SQL to unnest events across all sessions and compute total value per user per event_type. Provide the SQL and discuss indexing strategies or ETL alternatives to avoid expensive jsonb unnest on large historical data.

Sample Answer

Approach: expand the jsonb events array per session using a lateral unnest, extract event_type and numeric value, then GROUP BY user_id and event_type to sum values.

SQL (works in Postgres 12+):

sql

-- Unnest jsonb array and sum values per user and event_type
SELECT
  s.user_id,
  e.event_type,
  SUM(e.value) AS total_value
FROM sessions s
CROSS JOIN LATERAL (
  SELECT
    (elem->>'event_type')::text AS event_type,
    (elem->>'value')::numeric AS value
  FROM jsonb_array_elements(s.events) AS t(elem)
) e
GROUP BY s.user_id, e.event_type
ORDER BY s.user_id, e.event_type;

Notes on variants:- If events have a fixed schema, jsonb_to_recordset can be faster:

sql

CROSS JOIN LATERAL jsonb_to_recordset(s.events) AS e(event_type text, value numeric)

Performance considerations and indexing strategies:- GIN index on events (CREATE INDEX ON sessions USING gin (events jsonb_path_ops)) speeds existence/path queries but does not help array expansion/aggregation.- Expression indexes can't index every array element directly; you can create generated columns that extract frequently-used fields (e.g., first_event_type) and index those.- For selective filters (WHERE events @> '[{"event_type":"purchase"}]'), GIN helps; for aggregations across all historical rows, no index avoids full jsonb parsing.

ETL / modeling alternatives (recommended for large historical data):- Normalize: ETL sessions -> events table (events(session_id, user_id, event_type, value, ts)). This makes aggregations trivial and indexes effective (btree on user_id + event_type, partition by date).- Incremental pre-aggregation: maintain a daily/hourly aggregated table (user_id, event_type, day, total) via batch jobs or streaming (Debezium/Kafka + consumer), then roll up to arbitrary windows quickly.- Materialized view: create a MATERIALIZED VIEW of the unnest + aggregate and refresh CONCURRENTLY on a schedule.- Partitioning: store sessions partitioned by date to limit scanned data when querying ranges.- Use BigQuery/Redshift/Snowflake or columnar store for very large analytics workloads.

Trade-offs:- JSONB keeps write simplicity but is costly for large-scale analytical queries.- Normalizing increases write complexity but yields much lower query latency and better indexability — typically the right choice for production analytics.

EasyTechnical

34 practiced

Explain the difference between WHERE and HAVING in SQL. Using a table orders(order_id, customer_id, amount numeric), provide two example SQL queries: one that filters rows with amount > 100 before aggregation and one that filters groups having COUNT(*) > 5 after aggregation. Explain why aggregate functions cannot be used in WHERE and when HAVING is required.

Sample Answer

WHERE filters individual rows before any GROUP BY/aggregation; HAVING filters groups after aggregation. Use WHERE when your predicate references raw columns; use HAVING when it references aggregates like COUNT(), SUM(), AVG().

Example — filter rows with amount > 100 before aggregation:

sql

SELECT customer_id, COUNT(*) AS orders_over_100, SUM(amount) AS total_amount
FROM orders
WHERE amount > 100
GROUP BY customer_id;

This excludes any order with amount <= 100 prior to counting/summing.

Example — filter groups having more than 5 orders (after aggregation):

sql

SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

COUNT(*) is evaluated per group; HAVING removes groups that don't meet the condition.

Why aggregates can't be used in WHERE: WHERE operates on individual rows and is evaluated before grouping, so aggregate results (which require seeing the group's rows) don't exist yet. HAVING is evaluated after aggregation and therefore can reference aggregate functions. Use HAVING whenever your condition depends on an aggregate.

HardTechnical

30 practiced

You have per-shard HyperLogLog sketches for unique users produced by multiple services. Explain how to merge these sketches to produce a global unique user estimate, describe parameter compatibility concerns, discuss error propagation across merges, and suggest storage formats for sketches in a relational database.

Sample Answer

High-level answer- HyperLogLog (HLL) sketches merge by taking the element-wise maximum of corresponding registers; the merged sketch then yields an unbiased estimate of the cardinality of the union using the same HLL estimate function. This lets you aggregate per-shard sketches into a global unique-user estimate without re-streaming raw events.

Parameter compatibility- Precision p (number of index bits; m = 2^p): must be identical across sketches. Different p => different register counts and incompatible register indices.- Hash function / seed: must be the same (or at least produce identical bit distributions). If services used different hashers/seeds, merging is invalid.- HLL variant/version: HLL vs HLL++ (bias correction, sparse encoding) must be compatible; if some use sparse representation, serialize to the same canonical dense/register form before merging.- Register width / max-rank and any implementation-specific tweaks must match.

How to merge (practical)- Ensure all sketches are decoded to arrays of m registers.- For i in 0..m-1: R_global[i] = max(R_shard1[i], R_shard2[i], ...).- Apply the global estimator (including bias correction / small-range correction / large-range correction depending on variant).

Error propagation & statistical behavior- For compatible sketches, the relative standard error of the merged union estimate remains approximately the same as a single sketch with m registers: std_err ≈ 1.04 / sqrt(m) (for classical HLL). Merging does not systematically increase variance because HLL is designed so register maxima preserve the same estimator variance for the union.- Caveats: - Estimator variance assumes independent hashing of items; heavy dependence or correlated errors from upstream (e.g., deterministic sharding by user ID combined with biased hash) can bias results. - Estimating intersections via inclusion–exclusion (|A ∩ B| = |A| + |B| − |A ∪ B|) compounds variances: variances add, so intersection estimates can have much larger relative error, and bias corrections may not cancel. For intersections or set similarity, consider MinHash / KMV sketches which give better intersection/Jaccard estimates. - Merging many sketches that came from streams with very skewed cardinalities can push the estimator into small-range or large-range correction regimes — ensure bias correction is applied consistently.

Storage formats for relational DBs- Store sketches as binary blobs with explicit metadata columns: - schema: id, ts, algorithm (HLL/HLL++), p, hash_seed, register_bytes (VARBINARY/bytea), cardinality_cache (FLOAT), version- Representations: - Dense canonical byte array of registers (fixed length m * 1 byte or more depending on register width). Pros: fast merging, fixed-size. Cons: larger. - Sparse encoded form (serialized as compact bytes) for small cardinalities (HLL++ uses sparse-to-dense conversion). If storing mixed variants, normalize to dense on read before merge. - Base64 encoding if DB client can't handle binary; preferable: native VARBINARY / bytea to avoid text overhead.- Compression: compress blobs (zlib/snappy) if storing many sketches; include uncompressed length/version in metadata.- Indexing/caching: persist a cached cardinality and last-updated timestamp to avoid repeated deserialization for reads.- Backwards compatibility: version the stored format; store p and algorithm so readers can validate compatibility before merging.

Best practices- Enforce a global library/version and hashing config across services; centralize HLL creation code.- Validate sketches at ingestion (p and hash_seed).- Keep ability to re-create sketches from raw events if you ever need to change p or algorithm.- Use HLL for unions; for intersections or high-precision small-set operations prefer complementary sketches (KMV, MinHash) or maintain raw keys for high-value subsets.

This approach ensures correct merges, stable error behavior for unions, and practical, query-friendly storage in a relational DB.

MediumTechnical

30 practiced

Given orders(order_id, product_id, amount), write SQL to compute the 90th percentile of order amount per product using Postgres functions (percentile_cont or percentile_disc). Show the query and explain differences between continuous and discrete percentile functions.

Sample Answer

Approach: use Postgres’ ordered-set aggregate functions to compute the 90th percentile per product. They accept a percentile (0..1) and an ORDER BY to define the distribution. Exclude NULL amounts and consider minimum count thresholds if needed.

Query (per-product p90 using continuous and discrete variants):

sql

SELECT
  product_id,
  COUNT(*) FILTER (WHERE amount IS NOT NULL) AS n_orders,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY amount) AS p90_continuous,
  percentile_disc(0.9) WITHIN GROUP (ORDER BY amount) AS p90_discrete
FROM orders
WHERE amount IS NOT NULL
GROUP BY product_id
ORDER BY product_id;

Explanation — continuous vs discrete:- percentile_cont(0.9): returns an interpolated value on the continuous distribution. If the 90th rank falls between two observed amounts, Postgres linearly interpolates between them. Use when you want a smooth estimate (e.g., monetary percentiles for reports, inputs to models).- percentile_disc(0.9): returns the actual observed value at the discrete rank (the smallest amount whose cumulative distribution ≥ 0.9). Use when you must pick an observed/order-level value (e.g., thresholding decisions, bucket boundaries).

Practical notes:- Both ignore NULLs; include a FILTER to be explicit.- For small groups (n < 10), interpretation of p90 may be unstable — consider requiring min sample size.- Performance: these are single-pass aggregates but sorting per group occurs; ensure appropriate indexes or pre-aggregated tables for very large datasets.- If you need percentiles across the entire table or sliding windows, consider windowed percentile functions or approximate methods (e.g., tdigest extension) for scale.

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.