Set Operations and Complex Aggregations Questions

Understanding UNION, UNION ALL, EXCEPT, INTERSECT operations and their performance implications. Complex GROUP BY queries, HAVING clauses, and multi-level aggregations.

HardTechnical

61 practiced

In a geo-distributed system with eventual consistency, implementing EXCEPT and INTERSECT semantics across shards can produce surprising results. Describe the correctness challenges when rows are duplicated or partially replicated across regions, and propose strategies to get deterministic set operation results for BI use cases.

Sample Answer

Situation: In geo-distributed, eventually-consistent stores, INTERSECT and EXCEPT across shards can be nondeterministic when the same logical row exists in multiple regions (duplicates) or when only parts of a row are replicated (partial replication). BI queries that expect set semantics (true mathematical set intersection/difference) can therefore produce surprising or non-repeatable results.

Correctness challenges:- Duplicate rows: The same logical entity present in multiple shards may appear multiple times or be counted differently depending on read timing and anti-entropy state.- Partial replication: Different regions may hold different subsets of columns; an INTERSECT on a subset of columns can miss matches because other attributes are absent or stale.- Concurrent updates & deletes: Deletes may be delayed (tombstone propagation), so EXCEPT may falsely include rows that are logically deleted elsewhere.- Non-deterministic timestamps: Using last-writer-wins (LWW) with unsynchronized clocks leads to conflicting resolution across regions.

Deterministic strategies for BI use cases:1. Canonical identity & deduplication - Ensure a globally unique primary key (GUID or composite natural key) and a stable canonical form used by all regions. - In the BI/ETL layer, deduplicate by key (keep canonical row per key) before set ops.

2. Deterministic conflict resolution - Use application-level deterministic rules (e.g., prefer source-region A, or highest-priority region) rather than relying on wall-clock LWW. - Or use Hybrid Logical Clocks / version vectors so causality-aware resolution is consistent.

3. Materialized, consistent snapshots for analytics - Build read-consistent snapshots (periodic global snapshots or MVCC snapshot exported to data warehouse) and run INTERSECT/EXCEPT on that snapshot to avoid in-flight divergence. - Use CDC to populate a central analytics store (Snowflake/Redshift/BigQuery) where set semantics are deterministic.

4. Metadata & tombstones - Propagate delete tombstones globally and retain them long enough for analytics jobs to see consistent state; include tombstone and last-modified metadata in BI filters. - Use soft-delete + compacted GC only after global watermark.

5. Query-time quorum/read-repair - For low-latency interactive dashboards, read with sufficient quorum or run cross-region reconciliation logic: fetch candidate keys from all relevant shards and reconcile deterministically in the BI layer.

6. Column-aware set semantics - Define exactly which columns define set membership (schema-on-read). For partial replication, restrict INTERSECT/EXCEPT to the intersection of replicated columns or enrich regions to replicate the required minimal key set.

7. Validation & lineage - Log provenance (region, version) per row so analysts can trace why a row was included/excluded and rerun deterministic resolution if needed.

Trade-offs:- Snapshots/centralization cost freshness but give repeatability.- Quorum reads increase latency/cost.- Deterministic rules reduce surprises but can hide latest updates.

Recommendation for BI: for critical reports, use CDC to centralize and run set operations on a reconciled warehouse snapshot; for near-real-time dashboards, enforce global keys, include version metadata, and perform deterministic dedupe & conflict-resolution in the ETL/query layer.

EasyTechnical

82 practiced

Provide an efficient SQL pattern to merge two aggregated result sets and add a column to indicate the source of each aggregate. For example, aggregate daily revenue from online_sales and offline_sales, then UNION ALL them with a source column named channel. Include schema examples and explain why UNION ALL is preferable here.

Sample Answer

To merge two aggregated result sets and mark their source, aggregate each table separately, add a literal column for the channel, then combine with UNION ALL. UNION ALL is preferred because it avoids the de-duplication/sort cost of UNION and preserves counts.

Schema examples:- online_sales(order_id, order_date DATE, amount NUMERIC)- offline_sales(txn_id, txn_date DATE, amount NUMERIC)

Approach: compute daily totals per source, add channel column, UNION ALL, and (optionally) aggregate the union for a full daily total or pivot.

sql

-- daily aggregates per channel, kept separate
WITH online AS (
  SELECT
    order_date::date AS day,
    SUM(amount) AS revenue,
    'online' AS channel
  FROM online_sales
  GROUP BY order_date::date
),
offline AS (
  SELECT
    txn_date::date AS day,
    SUM(amount) AS revenue,
    'offline' AS channel
  FROM offline_sales
  GROUP BY txn_date::date
)

-- combine them; use UNION ALL to avoid extra work
SELECT day, revenue, channel
FROM online
UNION ALL
SELECT day, revenue, channel
FROM offline
ORDER BY day, channel;

If you need a single daily total with breakouts:

sql

SELECT day,
       SUM(revenue) AS total_revenue,
       SUM(CASE WHEN channel='online' THEN revenue END) FILTER (WHERE channel='online') AS online_revenue,
       SUM(CASE WHEN channel='offline' THEN revenue END) FILTER (WHERE channel='offline') AS offline_revenue
FROM (
  -- CTEs as above
) combined
GROUP BY day
ORDER BY day;

Why UNION ALL:- Faster: no distinct/sort step.- Correct: preserves duplicates when both channels have activity same day.- Predictable resource usage; better for large datasets and BI pipelines.

Edge cases & best practices:- Ensure date normalization (time zones, truncation).- Cast/round numeric types consistently.- If deduplication is required, use UNION (costly) or aggregate after UNION ALL.- Add indexes on date columns to speed upstream aggregation.

MediumTechnical

109 practiced

What happens if you use ORDER BY and LIMIT inside each branch of a UNION and then UNION those branches together? Explain the SQL standard behavior and common pitfalls when trying to limit rows per branch versus limiting the final result, and provide a correct approach to fetch top M rows per unioned group.

Sample Answer

Short answer: The SQL standard only allows ORDER BY (and OFFSET/FETCH) to affect the final result of a statement. ORDER BY/LIMIT placed inside each branch of a UNION is not guaranteed by the standard to constrain those branch results before the UNION — many engines will ignore per-branch ORDER BY unless it's wrapped so the engine can treat it as a derived table. Also UNION (not UNION ALL) removes duplicates, which can alter counts.

Pitfalls:- Expecting each branch’s LIMIT to produce the "top N per source" can fail on some databases.- Using UNION (vs UNION ALL) may drop rows you expected.- ORDER BY in branches may be ignored; only outer ORDER BY is guaranteed.

Correct, portable approach: perform ordering + limiting using window functions, then union (or union all) the pre-ranked results and filter by the row number. Example: suppose you want top 3 rows per source after unioning two queries.

sql

WITH q AS (
  SELECT id, source, value,
         ROW_NUMBER() OVER (PARTITION BY source ORDER BY value DESC) AS rn
  FROM (
    SELECT id, 'A' AS source, value FROM table_a
    UNION ALL
    SELECT id, 'B' AS source, value FROM table_b
  ) t
)
SELECT id, source, value
FROM q
WHERE rn <= 3
ORDER BY source, value DESC;

Why this works:- ROW_NUMBER() partitions by group (source) and orders deterministically.- Filtering rn <= M yields top M per group reliably across engines.- Use UNION ALL to preserve rows; use UNION only if you intentionally want de-duplication.

If you must rely on per-branch LIMIT in a DB that supports it (e.g., MySQL allows LIMIT in subqueries), wrap each branch as a derived table:

sql

SELECT * FROM (
  SELECT * FROM (SELECT ... FROM table_a ORDER BY value DESC LIMIT 3) a
  UNION ALL
  SELECT * FROM (SELECT ... FROM table_b ORDER BY value DESC LIMIT 3) b
) x
ORDER BY source, value DESC;

But prefer the window-function approach for clarity and portability.

EasyTechnical

66 practiced

Describe INTERSECT and EXCEPT (or MINUS) set operations in SQL and provide a realistic BI example using orders_2023(order_id, customer_id) and orders_2024(order_id, customer_id). Explain when to use INTERSECT and when to use EXCEPT to identify returning customers and lost customers, and discuss how NULLs and duplicate rows affect results.

Sample Answer

INTERSECT and EXCEPT are set operations in SQL that return row-level results after applying set semantics (based on all selected columns).

- INTERSECT: returns rows present in both query results.- EXCEPT (or MINUS in some DBs): returns rows present in the first query but not in the second.

Examples (identify customers at the customer_id level):

Returning customers (appeared in both years):

sql

SELECT customer_id
FROM orders_2023
INTERSECT
SELECT customer_id
FROM orders_2024;

Lost customers (ordered in 2023 but not in 2024):

sql

SELECT customer_id
FROM orders_2023
EXCEPT
SELECT customer_id
FROM orders_2024;

When to use:- Use INTERSECT to find stable/returning customers or overlapping sets (e.g., retention cohorts).- Use EXCEPT to find drop-offs, churn, or items present in one period but not another.

NULLs and duplicates:- INTERSECT/EXCEPT treat results as sets: duplicates are removed automatically (they behave like DISTINCT).- NULLs compare as equal for the purpose of set operations in most SQL implementations: a NULL customer_id in both tables will be treated as matching in INTERSECT. However, behavior can vary—some engines treat NULLs specially—so prefer explicit filtering (WHERE customer_id IS NOT NULL) if you intend to ignore NULLs.- If you need counts including duplicates (e.g., number of orders), use JOINs, EXISTS, or aggregation instead of set operators.

Practical BI tip: wrap results with counts or join back to customer dimension to add names/segments before reporting.

HardTechnical

65 practiced

Explain HyperLogLog (HLL) mergeability and how it enables approximate distinct counts across unioned partitions or pre-aggregated daily sketches. As a BI analyst, describe how you would integrate HLL into daily pipeline to provide approximate distinct user counts for dashboards and how to convey accuracy to stakeholders.

Sample Answer

HyperLogLog (HLL) is a probabilistic algorithm that estimates distinct counts using fixed-size registers. Its key property for BI pipelines is mergeability: two sketches created from disjoint (or overlapping) subsets can be combined by taking per-register maxima to produce a single sketch whose estimate equals the estimate of the union. That enables incremental aggregation (per-partition/day) and fast unioning without re-scanning raw events.

How I'd integrate HLL into a daily pipeline:- In ETL, for each event stream partition (e.g., hourly), compute an HLL sketch keyed by date and dimension (user_id hashed → HLL update). Store sketches in a column-store or object store (e.g., Parquet, Redis, BigQuery HLL functions).- At daily aggregation, merge that day's hourly sketches by taking register-wise maxima (most libraries provide merge/union). Persist one daily sketch per metric.- For dashboards, pre-merge weekly/monthly sketches by unioning daily sketches on demand or precomputing materialized aggregates to serve fast queries.

Example (conceptual Python using a common HLL lib):

python

from hyperloglog import HyperLogLog
hll = HyperLogLog(p=14)        # precision
for user in users_stream:
    hll.add(user_id_hash(user))
# serialize hll to S3

Merging:

python

hll_total = HyperLogLog(p=14)
for serialized in daily_sketches:
    hll_total.update(deserialize(serialized))
estimate = len(hll_total)

Accuracy & communicating to stakeholders:- Explain bias and error: relative standard error ≈ 1.04 / sqrt(2^p). For p=14, RSE ≈ 0.81% — show numeric examples (e.g., estimate ±1%).- Provide confidence bands on dashboards (estimate ± RSE*estimate) and surface sample sizes and sketch precision.- Add metadata: precision parameter, number of merged partitions, and a "known exact" compare for small cohorts (when cardinality < threshold, compute exact count).- Document trade-offs: fixed memory, fast union, tiny error vs. inability to get exacts for very small groups unless complemented with exact counters.- Recommend governance: standardize p, serialization format, and monitoring (periodic ground-truth audits using full scans for a subset).

This approach gives scalable, low-cost distinct-user counts for dashboards with transparent accuracy metrics so stakeholders can trust and interpret the approximations.

Unlock Full Question Bank

Get access to hundreds of Set Operations and Complex Aggregations interview questions and detailed answers.

Join thousands of developers preparing for their dream job.