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.

MediumBehavioral

55 practiced

Describe a time when business stakeholders disagreed about the definition of an important metric (for example, 'active user' or 'churned customer'). Using the STAR structure, explain how you approached alignment, what data/queries you used to reconcile definitions, and how you implemented the agreed definition into aggregated reports and dashboards.

Sample Answer

Situation: At my previous company, Product, Marketing, and Customer Success disagreed on the definition of "active user"—Marketing wanted 7-day active users (for campaign measurement), Product wanted feature-specific engagement (events), and CS wanted 30-day active users to track retention. This produced inconsistent reports and conflicting KPIs.

Task: As the BI Analyst, I needed to lead alignment on a single, documented definition (with acceptable variants), quantify the impact of each definition on metrics, and implement the agreed definition into canonical dashboards and automated reports.

Action:- I convened a 90-minute alignment workshop with reps from each team, presenting a decision matrix (use-case, time window, event criteria, business decisions supported, sensitivity to noise).- To make the trade-offs concrete I ran comparative queries showing how choice affected counts and trends. Example SQL I used to compare 7-day vs 30-day active users:

sql

-- Count users with at least one session in last N days
WITH events AS (
  SELECT user_id, event_time
  FROM analytics.events
  WHERE event_time >= current_date - interval '30' day
)
SELECT
  SUM(CASE WHEN last_event >= current_date - interval '7' day THEN 1 ELSE 0 END) AS active_7d,
  SUM(CASE WHEN last_event >= current_date - interval '30' day THEN 1 ELSE 0 END) AS active_30d
FROM (
  SELECT user_id, MAX(event_time) AS last_event
  FROM events
  GROUP BY user_id
) u;

- I also ran feature-specific queries (e.g., users with >=3 feature_x events in 14 days) to show variance.- Using the data, stakeholders agreed on a tiered approach: canonical "Active User (30d, general)" for executive and retention reporting; "Weekly Active (7d)" for marketing cadence; and "Feature-Active" metrics for Product experiments. We documented precise event lists, deduplication rules, and exclusion criteria (bots, test accounts).- Implementation: I created parameterized measures in Looker (and corresponding SQL-based ETL views) so every dashboard used the same upstream definition. I added: - A data model view (derived table) that computed canonical active flags daily. - Reusable metrics (LookML/Power BI measures) referencing that table. - A header on dashboards showing the exact definition and last refresh.- I backfilled 12 months to ensure historical continuity, added automated data-quality checks that alert if active counts change >15% day-over-day, and published a short doc + Slack post explaining when to use each variant.

Result: Stakeholders quickly adopted the canonical metrics—reports converged, cross-team meetings cut reconciliation time by ~70%, and executive dashboards became the single source of truth. Marketing retained the 7-day view for campaigns but pointed to the canonical 30-day metric for executive decisions. The standardized definitions reduced ad-hoc analysis requests and improved confidence in decisions. Learned: show concrete data comparisons, document exact event logic, and build reusable metrics to enforce consistency.

HardSystem Design

39 practiced

Compare pre-aggregated materialized views, OLAP cubes (e.g., SSAS), and in-memory/columnar engines (e.g., Druid, ClickHouse) for powering interactive BI dashboards that allow complex groupings and filters. Discuss maintenance, latency, query flexibility, cost, and recommended use cases for each approach.

Sample Answer

Clarify tradeoffs first: dashboards need interactive latency (sub-second to few-seconds), support for ad-hoc groupings/filters, and maintainable pipelines. Below I compare materialized views (pre-aggregations), OLAP cubes (SSAS), and in-memory/columnar engines (Druid/ClickHouse) across maintenance, latency, query flexibility, cost, and typical use cases.

Materialized views / pre-aggregations- Maintenance: ETL-driven; straightforward to incrementally refresh if source keys are stable. Requires orchestration and careful aging/partitioning logic for many dimension combinations.- Latency: Very low for defined aggregations (sub-second). Freshness depends on refresh cadence (near-real-time if incremental).- Query flexibility: Limited to pre-computed rollups. Ad-hoc groupings outside covered aggregates need fallback to raw data with higher latency.- Cost: Lower compute at query time; storage cost proportional to number of rollups.- Use cases: Dashboards with known KPIs and predictable drill paths where predictable performance and low cost matter.

OLAP cubes (SSAS)- Maintenance: Tooling handles aggregation levels, hierarchies, security; cube processing can be complex and often requires scheduled reprocesses or incremental processing.- Latency: Excellent for pre-modeled slices (sub-second). Processing large re-builds causes stale windows.- Query flexibility: Good for hierarchical drill-downs and pre-modeled MDX queries; less flexible for arbitrary ad-hoc group-bys or very wide schemas.- Cost: Licensing and operational overhead (Windows/SQL Server ecosystem) can be significant.- Use cases: Enterprise BI with complex business hierarchies, role-based security, and many OLAP-style analyses.

In-memory / columnar engines (Druid, ClickHouse)- Maintenance: Simpler ingest pipelines; support partitioning, TTL, and rollup at ingestion. Operational requirements include cluster tuning and compaction.- Latency: Very low for both pre-aggregated and raw queries; designed for high-concurrency interactive queries (sub-second to low-seconds).- Query flexibility: High — fast scans and ad-hoc group-bys, late-arriving dimensions supported. Columnar compression keeps storage efficient.- Cost: Higher compute and storage (but often lower than full OLAP licensing); scales horizontally; cloud-managed options reduce ops cost.- Use cases: Interactive exploratory dashboards, ad-hoc analysis over large datasets, high-concurrency BI for event/time-series data.

Recommendation for a BI Analyst- If dashboards have stable KPIs and limited drill paths: use materialized views for predictability and low cost.- If organization needs formalized hierarchies, role security, and traditional OLAP semantics: choose OLAP cubes.- If you need flexibility, high concurrency, fast ad-hoc slicing across large datasets: prefer columnar engines (Druid/ClickHouse). Often a hybrid works best: columnar engine as primary interactive store + targeted pre-aggregations/materialized views for very heavy, predictable queries.

EasyTechnical

34 practiced

Given a transactions table with schema: transactions(transaction_id int, user_id int NULL, amount decimal, occurred_at timestamp). Write SQL that returns: total_transactions (COUNT(*)), non_null_user_count (COUNT(user_id)), distinct_user_count (COUNT(DISTINCT user_id)). Explain the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column) and how NULLs affect each result.

Sample Answer

You can compute the three metrics in one query:

sql

SELECT
  COUNT(*)                         AS total_transactions,
  COUNT(user_id)                   AS non_null_user_count,
  COUNT(DISTINCT user_id)          AS distinct_user_count
FROM transactions;

Explanation — what each COUNT does and how NULLs affect results:- COUNT(*) counts all rows, including those where user_id is NULL. Use this for total number of transactions.- COUNT(column) counts only non-NULL values in that column. COUNT(user_id) therefore excludes rows where user_id IS NULL and is useful when you want the number of transactions with an associated user.- COUNT(DISTINCT column) counts unique non-NULL values. COUNT(DISTINCT user_id) gives the number of distinct users who appear in the table; it ignores NULLs entirely.

Examples:- If there are 100 rows, 10 rows have user_id NULL, and 90 non-NULL user_ids but only 50 unique users, results will be: total_transactions = 100, non_null_user_count = 90, distinct_user_count = 50.

Practical BI notes:- Be explicit about how you treat NULLs in reports. If you need to include an “anonymous” user bucket, replace NULL with a sentinel (e.g., COALESCE(user_id, -1)) before DISTINCT, or handle it in the visualization layer.

HardTechnical

32 practiced

You have a boolean column 'is_active' and some rows have NULL. For reporting you want counts grouped into three buckets: 'Active', 'Inactive', 'Unknown'. Write a SQL snippet that groups by this label and returns counts. Discuss how this representation affects cardinality and indexing strategies.

Sample Answer

Approach: map boolean and NULL into three readable buckets with a CASE expression, then GROUP BY that label to get counts. I'll show both a simple query and a version that preserves deterministic ordering. Then discuss how this representation affects cardinality and indexing choices for reporting.

sql

-- Simple grouping into three buckets
SELECT
  CASE
    WHEN is_active = TRUE  THEN 'Active'
    WHEN is_active = FALSE THEN 'Inactive'
    ELSE 'Unknown' -- covers NULL
  END AS activity_status,
  COUNT(*) AS cnt
FROM users
GROUP BY activity_status
ORDER BY
  CASE activity_status WHEN 'Active' THEN 1 WHEN 'Inactive' THEN 2 ELSE 3 END;

Notes and alternatives:- If is_active is stored as 0/1/NULL, use WHEN is_active = 1 / = 0 patterns.- For DBs that support FILTER: you can compute three counts in one row:

sql

SELECT
  COUNT(*) FILTER (WHERE is_active = TRUE)  AS active,
  COUNT(*) FILTER (WHERE is_active = FALSE) AS inactive,
  COUNT(*) FILTER (WHERE is_active IS NULL)  AS unknown
FROM users;

Cardinality & indexing implications:- The derived label has only three distinct values — very low cardinality. Indexes on is_active are low-selectivity; bitmap or partial indexes may be more effective than full b-tree for large tables.- For reporting, pre-aggregated counters (materialized views or summary tables) are often better: computing counts on every query scans many rows otherwise.- If queries frequently filter by other columns plus is_active (e.g., region + is_active), consider a composite index with region first then is_active to improve selective filtering; but be mindful that is_active contributes little to selectivity.- Partial indexes can optimize common filters: e.g., CREATE INDEX idx_users_active ON users(id) WHERE is_active = TRUE — useful if Active rows are a minority and queried often.- Null handling: treat NULL explicitly (IS NULL) in predicates; some indexes may not include NULLs depending on DB (e.g., some implementations skip NULLs in partial indexes), so test behavior per engine.

Best practice for BI:- Use CASE mapping in views or semantic layer so dashboards always show 'Active'/'Inactive'/'Unknown'.- Back heavy queries with materialized aggregates refreshed on a schedule to keep dashboard latency low.

HardSystem Design

35 practiced

Design a materialized view or aggregated table strategy for dashboards that require 10-minute freshness over a dataset that ingests ~1TB/day of raw events. Describe how you would implement incremental refresh, partition-level refresh, trade-offs with storage, and how BI tool caching would interact with this strategy.

Sample Answer

Requirements & constraints:- Dashboard freshness: <=10 minutes- Input: ~1 TB/day raw events (~11.6k MB/s peak), late arrivals possible- Low query latency for BI tools (Tableau/Looker/Power BI)

High-level design:- Build near-real-time aggregated tables (materialized views) at multiple granularities: 10-min (primary), 1-hour, daily rollups.- Ingest raw events via streaming (Kafka/Kinesis) or micro-batch landing into a staging zone (cloud object store or streaming table).- Use a scheduler (Airflow/DB-native tasks) every 1–5 minutes to compute increments for the latest 10-min window(s) and write into partitioned/clustered aggregate tables.

Partitioning & partition-level refresh:- Partition aggregates by event_date and event_10min_bucket (e.g., YYYYMMDD_HHMM rounded down to 10m). This enables replacing individual 10-min partitions without touching others.- For throughput efficiency, group small recent partitions (last 1–6 windows) into a “hot” partitioning strategy if the DB penalizes many tiny partitions; otherwise keep strict 10-min partitions.- Partition refresh: when new raw data arrives for window T, compute delta-aggregate for T and perform atomic upsert/replace of partition T (CTAS to temp then atomic swap).

Incremental refresh strategy:- Use append-only ingestion + offsets (stream position or max(event_time)) to identify new data since last run.- Compute incremental aggregates using windowed GROUP BY on only new or changed events; for late-arriving events run a reprocess for affected recent partitions (watermark and a “reprocess window” e.g., last 60 minutes).- For correctness, maintain idempotent upsert semantics (use MERGE or partition replace) and store last-processed offsets/checkpoints.

Storage and trade-offs:- Storage cost vs query latency: - Full pre-aggregation at many dimensions increases storage but gives fastest BI queries. - Keep sparse/necessary dimensional combinations and push ad-hoc joins to compute layer.- Compression and columnar formats (Parquet/ORC) reduce size.- Retention: keep high-resolution 10-min partitions for recent period (e.g., 30–90 days), roll older data into hourly/daily rollups to save space.- If DB charges per partition/file, balance partition granularity accordingly.

BI tool caching interaction:- Use BI caching to reduce load: set cache TTL slightly > processing interval (e.g., 10–12 minutes) or trigger cache invalidation after partition swap.- Best pattern: dashboards connect to pre-aggregated tables (fast) with BI set to auto-refresh every 10 minutes or on-demand extracts scheduled slightly after materialized view refresh to avoid stale reads.- For low-latency live dashboards, use direct/live connection to aggregates; for heavy ad-hoc users, provide extracts with scheduled refreshes and incremental extract logic.

Operational concerns:- Monitor lag, processing failures, and reprocess queue for late data.- Provide SLA: e.g., 95th percentile freshness <=10 minutes, with alerts when reprocessing backlog > N windows.- Backfill tooling for schema changes or historical recompute.

Trade-offs summary:- More pre-aggregation = faster dashboards, higher storage & ETL complexity.- Finer partitions = precise incremental refresh but more metadata/management.- BI caching reduces DB load but adds another freshness layer to coordinate—coordinate cache TTL/invalidation with ETL schedule.

This approach gives predictable 10-minute freshness, efficient incremental updates via partition swaps, controlled storage growth via rollups/retention, and clear coordination with BI caching for consistent end-user experience.

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.