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.

HardTechnical

72 practiced

Design an efficient SQL strategy to compute a 7-day moving average of daily revenue for three years of data to support interactive dashboards. Discuss options such as window functions with appropriate frames, pre-computed daily sums used as base for rolling calculations, incremental rolling updates when new days arrive, and trade-offs between compute time and freshness.

Sample Answer

Approach: pre-aggregate raw transactions into a daily revenue table, then compute the 7-day moving average using a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (deterministic when one row per date). For dashboards, maintain a materialized daily_agg table and refresh incrementally each day to balance freshness and compute cost.

SQL — create daily aggregates (run once, then incremental daily):

sql

-- base daily aggregation (initial full run)
CREATE TABLE daily_revenue AS
SELECT
  CAST(tx_date AS date) AS day,
  SUM(amount) AS revenue
FROM transactions
WHERE tx_date >= CURRENT_DATE - INTERVAL '3 years'
GROUP BY CAST(tx_date AS date);

-- 7-day moving avg using ROWS frame (requires a row per day; fill gaps)
WITH days AS (
  SELECT generate_series(min(day), max(day), '1 day')::date AS day
  FROM daily_revenue
),
daily AS (
  SELECT d.day, COALESCE(r.revenue,0) AS revenue
  FROM days d
  LEFT JOIN daily_revenue r USING (day)
)
SELECT
  day,
  revenue,
  ROUND(AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) AS ma7
FROM daily
ORDER BY day;

Incremental update pattern:- Each day insert/merge that day's aggregate into daily_revenue.- Recompute ma7 only for affected window (new day affects last 6 days + new day) — use a small window update job or compute on-the-fly for dashboard queries limited to last N days.

Trade-offs:- Pre-aggregation + incremental refresh: low dashboard latency, near-real-time freshness (daily), minimal compute for interactive users.- On-the-fly full window over raw transactions: freshest but high compute and slow for dashboards.- Using RANGE BETWEEN INTERVAL '6 days' AND CURRENT ROW is convenient but can be slower on large tables and depends on proper indexing; ROWS is fastest when you have dense one-row-per-day series.- Storage vs compute: storing daily_revenue and optionally precomputed ma7 speeds queries but needs maintenance; compute time for incremental partial recompute is O(k) where k is window size (small).Edge cases:- Missing days: fill zeros or NULL as business requires.- Partial first window (<7 days): decide whether to show NULL or average of available days.

MediumTechnical

57 practiced

Explain how COALESCE and NULLIF assist when aggregating to handle missing or zero values. Given invoice(invoice_id, amount, tax_amount, invoice_date), write SQL to compute monthly average tax_rate defined as tax_amount / NULLIF(amount, 0) aggregated by month, demonstrating protection against division by zero and handling of NULLs for averaging.

Sample Answer

Approach:- Use NULLIF(amount, 0) to avoid division-by-zero: when amount is 0 this returns NULL so the division yields NULL rather than error.- Let AVG aggregate naturally ignore NULL tax_rate values (so rows with amount = 0 or NULL are excluded).- Use COALESCE around the final AVG to provide a default (e.g., 0) when there are no valid rows for a month.

SQL (Postgres-style):

sql

SELECT
  date_trunc('month', invoice_date) AS month,
  -- compute per-row tax_rate; division by zero -> NULL, AVG ignores NULLs
  COALESCE(AVG( tax_amount::numeric / NULLIF(amount, 0) ), 0) AS avg_tax_rate
FROM invoice
-- optional: filter out obviously irrelevant rows (e.g., future test data)
WHERE invoice_date IS NOT NULL
GROUP BY date_trunc('month', invoice_date)
ORDER BY month;

Key points:- NULLIF(amount, 0) converts 0 -> NULL so division doesn’t raise an error.- AVG(...) ignores NULLs, so only valid tax_rate values contribute.- COALESCE(AVG(...), 0) ensures a deterministic value for months with no valid tax rates.Edge cases:- If tax_amount is NULL you’ll get NULL tax_rate (excluded from AVG) — if you prefer treating missing tax as 0, change tax_amount::numeric to COALESCE(tax_amount, 0) before division (but that will bias the average). Choose behavior based on business rules.

MediumTechnical

60 practiced

Describe how parameterized filters in BI tools such as Tableau, Looker and Power BI affect aggregated SQL queries. Explain the difference between applying filters at the data source versus client-side, how passing a product category parameter into aggregated queries changes load, and best practices to keep dashboards responsive while supporting dynamic user filters.

Sample Answer

Applying parameterized filters in BI tools changes where and how aggregation happens, which directly affects query cost and dashboard responsiveness.

Data-source (server-side) filters: the BI layer passes filter values into SQL WHERE clauses so the database aggregates only the filtered rows. Example: passing product_category into the WHERE reduces scanned data and speeds up GROUP BY.

SELECT product_category, SUM(sales) 
FROM sales
WHERE product_category = 'Electronics'  -- parameter from dashboard
GROUP BY product_category;

Client-side (post-aggregation) filters: the tool fetches pre-aggregated results (often for all categories) and then filters locally. This reduces DB CPU but increases network transfer and memory in the client and can be wrong for metrics requiring row-level filtering (e.g., distinct counts with pre-aggregation).

Impact of passing product category parameter: when pushed to the DB it reduces rows scanned and I/O, lowering query time and cost. When not pushed, DB runs larger aggregations; dashboard must transfer more data and client must compute filters — slower and less scalable.

Best practices to keep dashboards responsive while supporting dynamic filters:- Push filters to the data source where possible (use parameterized queries / LookML liquid or Tableau parameter actions).- Use context/dependent filters: apply heavy filters first (date, region) to reduce cardinality before optional category filters.- Pre-aggregate (materialized views, summary tables) for common drill pairs; expose these via derived tables in LookML or aggregated tables in Power BI.- Implement query caching and result caching on the BI layer.- Limit high-cardinality parameters; replace free-text with controlled lists.- Use async loading and progressive reveal: load overview aggregates first, detailed panels on demand.- Monitor slow queries and add appropriate indexes or partitioning.

This approach balances accuracy, cost, and interactivity for enterprise dashboards.

MediumTechnical

58 practiced

A query using HAVING COUNT(*) > 5 runs slowly. Explain why HAVING can be expensive and show how to rewrite or restructure the query so that filtering happens earlier. Provide an example rewrite that moves selective predicates into WHERE or uses a subquery to reduce work before GROUP BY.

Sample Answer

HAVING is applied after aggregation, so the database must read and group all qualifying rows before filtering. That becomes expensive when the raw row set is large or when selective predicates are written in HAVING instead of WHERE. To improve performance, push any non-aggregated, selective predicates into WHERE (so rows are eliminated before GROUP BY) or pre-aggregate / filter using a subquery or CTE to reduce work.

Example (slow — filters in HAVING):

sql

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
AND MAX(order_date) > '2025-01-01';

Here MAX(order_date) > ... forces full aggregation for every customer.

Rewrite 1 — push non-aggregates into WHERE:

sql

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE order_date > '2024-01-01'  -- selective predicate applied early
GROUP BY customer_id
HAVING COUNT(*) > 5;

Rewrite 2 — pre-aggregate selective subset with a subquery/CTE:

sql

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT customer_id, COUNT(*) AS orders
FROM recent_orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Rewrite 3 — aggregate then filter via a covering index or top-N prefilter:

sql

SELECT customer_id, orders
FROM (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  WHERE order_status = 'completed'
  GROUP BY customer_id
) t
WHERE orders > 5;

Additional tips:- Ensure appropriate indexes on filtered columns so WHERE is cheap.- Consider approximate or incremental aggregates for very large tables.- Use EXPLAIN to confirm the optimizer applies the WHERE/filter before grouping.

HardTechnical

55 practiced

A global dashboard aggregates orders by day; describe strategies to ensure correct daily aggregation across time zones. Discuss storing timestamps in UTC, converting to users' time zones during aggregation, date_trunc with timezone functions, handling daylight savings time, and provide sample SQL to compute daily revenue in America/Los_Angeles timezone.

Sample Answer

Approach (high level)- Always store event timestamps in UTC (e.g., created_at_utc TIMESTAMP WITHOUT TIME ZONE or TIMESTAMPTZ depending on DB).- For daily aggregates, convert timestamps to the target reporting timezone before truncating to date boundaries — this ensures dates align with business-local midnight, including DST shifts.- Use timezone-aware functions (Postgres: AT TIME ZONE / TIMESTAMPTZ; BigQuery: TIMESTAMP, DATETIME, SAFE.PARSE_TIMESTAMP with time zone) so DST rules are applied automatically.- Consider whether aggregation should use the buyer’s local timezone (per-user tz) vs a common reporting tz (e.g., America/Los_Angeles). If per-user, convert each event using that user's tz field.

Postgres sample: daily revenue in America/Los_Angeles

sql

-- Assumes orders(order_id, amount_cents, created_at_utc timestamp without time zone)
-- We treat created_at_utc as UTC and convert to America/Los_Angeles (handles DST)
SELECT
  (date_trunc('day', (created_at_utc AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles'))::date AS local_date,
  SUM(amount_cents) / 100.0 AS revenue_usd,
  COUNT(*) AS orders
FROM orders
WHERE created_at_utc >= ('2023-01-01'::date - INTERVAL '1 day') -- optional filter
  AND created_at_utc < ('2024-01-01'::date + INTERVAL '1 day')
GROUP BY local_date
ORDER BY local_date;

Key notes and edge cases- Use TIMESTAMP WITH TIME ZONE if your DB preserves tz; otherwise store UTC and apply AT TIME ZONE 'UTC' first.- DST: timezone database applies DST automatically when converting; do not hardcode offsets.- Late-arriving / backdated events: decide cut-off and whether to backfill aggregates; maintain event ingestion time vs event time.- Consistency: document which tz each dashboard uses; include timezone in visuals/labels.- Per-user tz: if orders have user_tz, replace 'America/Los_Angeles' with that column (careful: GROUP BY per computed local_date).- Performance: precompute local_date in ETL for heavy tables and index it to speed dashboards.

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.