Advanced Querying with Structured Query Language Questions

Covers authoring correct, maintainable, and high quality Structured Query Language statements for analytical and transactional problems. Candidates should demonstrate writing Select Insert Update and Delete statements and using filtering grouping ordering and aggregation correctly. Emphasis is on complex query constructs and patterns such as multi table joins and join condition logic self joins for hierarchical data nested and correlated subqueries common table expressions including recursive common table expressions window functions such as row number rank dense rank lag and lead set operations like union and union all and techniques for calculating running totals moving averages cohort metrics and consecutive event detection. Candidates should be able to break down and refactor complex requirements into composable queries for readability and maintainability while reasoning about performance implications on large data sets. Senior expectations may include mentoring on best practices for query composition and understanding how schema and configuration choices influence query performance.

HardTechnical

25 practiced

Describe how you would use a recursive CTE to traverse a hierarchical table categories(id, parent_id, name) and produce the full path for each category (e.g., 'Electronics > Phones > Smartphones'). Provide the recursive CTE SQL and discuss performance considerations for deep hierarchies.

Sample Answer

To produce full paths from categories(id, parent_id, name) use a recursive CTE that walks from root to leaf (or from node up to root). Example builds path from root downward and outputs a string like 'Electronics > Phones > Smartphones':

sql

WITH RECURSIVE cat_path AS (
  -- base: roots (parent_id IS NULL)
  SELECT
    id,
    parent_id,
    name,
    name AS full_path,
    1 AS depth,
    ARRAY[id] AS path_ids
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- recursion: attach children
  SELECT
    c.id,
    c.parent_id,
    c.name,
    cp.full_path || ' > ' || c.name AS full_path,
    cp.depth + 1 AS depth,
    cp.path_ids || c.id
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
  -- optional cycle protection
  WHERE NOT c.id = ANY(cp.path_ids)
)
SELECT id, parent_id, full_path, depth
FROM cat_path
ORDER BY full_path;

Key points and reasoning:- Start at roots so full_path is built left-to-right; could also traverse upward and then reverse segments.- Use an array of visited ids to prevent cycles; for DBs without arrays adjust (e.g., string of ids).- Include depth for filtering very deep trees.

Performance considerations for deep hierarchies:- Recursive CTEs are fine for moderate depth (tens to low hundreds). For very deep trees (thousands), recursion can be slow or hit DB recursion limits (adjust max_recursion_depth where supported).- Indexes: ensure an index on parent_id (and id primary key) to speed joins.- Materialized paths: if queries frequently need full paths, consider storing a computed materialized_path column and maintaining it on insert/update (faster reads, more complex writes).- Alternative models: nested sets or closure table provide faster read queries for ancestry/descendant queries at cost of more complex maintenance.- Paging and limits: avoid selecting full recursive result for entire table when you only need subsets; add WHERE or depth limits.- Monitor execution plans and test with realistic data; for very large trees, precompute paths offline (ETL) and query the denormalized result.

MediumTechnical

19 practiced

Explain how partitioning a very large fact table by date can improve query performance and maintenance. Describe the trade-offs and write the SQL to create a RANGE partitioned table orders_by_day in PostgreSQL for daily partitions, plus how you would add a partition for a new date.

Sample Answer

Partitioning a very large fact table by date improves performance and maintenance by enabling partition pruning (queries that filter on date touch only relevant partitions), faster vacuum/ANALYZE per-partition, cheaper bulk deletes (drop/attach partitions instead of large DELETEs), smaller index sizes, and easier archiving/backups. For a Data Analyst this means faster adhoc queries and more predictable report runtimes.

Trade-offs:- Many small partitions add planning/metadata overhead; PostgreSQL has limits and slower DDL if you create thousands.- No true global indexes in Postgres: indexes are per-partition (you must maintain them).- Need automation for creating future partitions and managing retention.- Slightly more complex query/ETL pipelines (routing to correct partition).

SQL: parent table with daily RANGE partitions on order_date

sql

CREATE TABLE orders_by_day (
  order_id     BIGINT NOT NULL,
  customer_id  BIGINT,
  order_date   DATE NOT NULL,
  amount       NUMERIC(10,2),
  status       TEXT,
  -- other columns
  PRIMARY KEY (order_id, order_date)  -- include partition key to satisfy constraints
) PARTITION BY RANGE (order_date);

Create a partition for a single day (e.g., 2025-01-01):

sql

CREATE TABLE orders_2025_01_01 PARTITION OF orders_by_day
  FOR VALUES FROM ('2025-01-01') TO ('2025-01-02');

-- create indexes on the partition as needed
CREATE INDEX idx_orders_2025_01_01_customer ON orders_2025_01_01 (customer_id);

To add a partition for a new date programmatically (example function/script approach):

sql

-- simple example SQL to add next-day partition
CREATE TABLE orders_2025_01_02 PARTITION OF orders_by_day
  FOR VALUES FROM ('2025-01-02') TO ('2025-01-03');

Operational notes:- Automate partition creation (cron/CI job) before data arrives.- Use CHECK constraints or triggers on ingestion if older clients insert without specifying partition key.- Periodically detach and archive old partitions (ATTACH/DETACH or ALTER TABLE ... DETACH PARTITION) to implement retention.

MediumTechnical

18 practiced

You are given a query that runs slowly: it filters on LOWER(email) = 'abc@example.com'. Explain why this may prevent index usage and propose alternatives to make lookups case-insensitive while remaining sargable. Include SQL examples and index recommendations for PostgreSQL.

Sample Answer

Applying LOWER(email) in the WHERE clause prevents PostgreSQL from using a plain b-tree index on email because the index is on the raw column value; wrapping the column in a function changes the expression and makes the plain column index non-sargable. To be sargable you must either index the same expression or use a case-insensitive type.

Recommended alternatives for PostgreSQL (with examples):

1) Functional (expression) index on lower(email)- Create index:

sql

CREATE INDEX users_email_lower_idx ON users (lower(email));

- Query (uses the index because expression matches):

sql

SELECT * FROM users
  WHERE lower(email) = 'abc@example.com';

- You can also enforce uniqueness case-insensitively:

sql

CREATE UNIQUE INDEX users_email_lower_uq ON users (lower(email));

2) Persist a normalized column and index it- Add and backfill:

sql

ALTER TABLE users ADD COLUMN email_norm text;
  UPDATE users SET email_norm = lower(email);
  CREATE INDEX users_email_norm_idx ON users (email_norm);

- Keep in sync with trigger or during app writes. Query:

sql

SELECT * FROM users WHERE email_norm = 'abc@example.com';

3) Use the citext extension (recommended for simplicity)- Install and convert:

sql

CREATE EXTENSION IF NOT EXISTS citext;
  ALTER TABLE users ALTER COLUMN email TYPE citext;
  CREATE UNIQUE INDEX users_email_citext_uq ON users (email);

- Queries can use email = 'abc@example.com' and the index is used because citext is case-insensitive at the type level.

Notes and trade-offs:- For simple equality lookups, a b-tree expression index or citext is best. Both are sargable.- For pattern searches (LIKE '%...') consider pg_trgm GIN/GiST indexes or text_pattern_ops for prefix searches.- Be aware of collation and Unicode normalization differences; lower() behavior depends on locale. Using citext delegates behavior to PostgreSQL collations. Index maintenance cost and storage overhead apply for extra columns/indexes.

HardTechnical

19 practiced

Provide a SQL strategy to maintain aggregate metrics (daily active users, revenue) with incremental updates when new event batches arrive each hour. Your solution should support idempotent reruns and minimal recomputation. Outline schema, keys, and SQL patterns for upserts/merges.

Sample Answer

Approach (brief)- Ingest hourly event batches into a staging table, dedupe by a unique event_id, then compute incremental aggregates (e.g., DAU, revenue) grouped by day. Use MERGE/UPSERT into a daily_aggregates table keyed by date + metric. Ensure idempotency by dedup keys and writing aggregates computed only from staging after dedupe. Minimize recomputation by aggregating only the new/changed events (using batch_id or event_timestamp watermark).

Schema (suggested)- raw_events(stage): - event_id PK (string) - user_id - event_type - revenue (numeric) - event_ts (timestamp) - batch_id (string) -- hourly batch identifier - received_at- daily_aggregates: - event_date (date) -- PK part 1 - metric (varchar) -- 'dau'|'revenue' PK part 2 - value (numeric) - last_updated_at (timestamp)- processed_batches: - batch_id PK - processed_at

Pattern (steps)1) Load staging (raw_events) append-only for each hour.2) Deduplicate staging for that batch:

sql

CREATE TABLE deduped_events AS
SELECT DISTINCT ON (event_id) *
FROM raw_events
WHERE batch_id = :batch_id
ORDER BY event_id, received_at DESC;

3) Compute per-day incremental aggregates from deduped_events:

sql

WITH per_day AS (
  SELECT
    CAST(event_ts AS date) AS event_date,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type IS NOT NULL) AS dau,
    SUM(revenue) AS revenue
  FROM deduped_events
  GROUP BY CAST(event_ts AS date)
)
SELECT * FROM per_day;

4) Upsert into daily_aggregates using MERGE (idempotent):

sql

MERGE INTO daily_aggregates t
USING (
  SELECT event_date, 'dau' AS metric, dau::numeric AS value FROM per_day
  UNION ALL
  SELECT event_date, 'revenue' AS metric, revenue::numeric FROM per_day
) s
ON t.event_date = s.event_date AND t.metric = s.metric
WHEN MATCHED THEN
  UPDATE SET value = t.value + s.value, last_updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (event_date, metric, value, last_updated_at)
  VALUES (s.event_date, s.metric, s.value, CURRENT_TIMESTAMP);

5) Record batch processed:

sql

INSERT INTO processed_batches(batch_id, processed_at) VALUES (:batch_id, now());

Idempotency & Minimal recomputation- Dedupe by event_id so reprocessing same batch doesn't double-count.- Use idempotent batch_id tracking: skip a batch if processed_batches contains batch_id (or allow re-run by deleting its previous effect first).- To support re-run that corrects prior bad data: compute per_day for the batch and either (a) subtract previous batch's contribution before adding new (store batch-level aggregates), or (b) maintain a batch_aggregates table and MERGE by replacing batch contribution per (event_date, metric) then recompute daily sum as SUM over batch_aggregates.Example pattern (batch_aggregates + recompute):

sql

-- replace batch contribution
MERGE INTO batch_aggregates b
USING (SELECT event_date, 'dau' metric, dau value FROM per_day) s
ON b.batch_id = :batch_id AND b.event_date = s.event_date AND b.metric = s.metric
WHEN MATCHED THEN UPDATE SET value = s.value
WHEN NOT MATCHED THEN INSERT (batch_id, event_date, metric, value) VALUES (:batch_id, s.event_date, s.metric, s.value);

-- recompute daily from batch_aggregates and upsert to daily_aggregates (replace value)
MERGE INTO daily_aggregates t
USING (
  SELECT event_date, metric, SUM(value) AS value FROM batch_aggregates GROUP BY event_date, metric
) s
ON t.event_date = s.event_date AND t.metric = s.metric
WHEN MATCHED THEN UPDATE SET value = s.value, last_updated_at = now()
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

Trade-offs / notes- Simple additive MERGE is cheaper but harder to correct retroactively. Maintaining batch_aggregates gives full idempotent re-run and easier corrections at cost of storage and recompute per affected date ranges only.- Partition daily_aggregates by event_date for faster replaces.- Keep retention / housekeeping for batch_aggregates to control storage.- Monitor late-arriving events and decide policy (accept and reprocess affected dates).

Edge cases- Late-arriving events crossing date boundaries (use event_ts timezone normalization).- Duplicates across batches — ensure global dedupe by event_id.- Large backfills — run partition replacement or full recompute for the affected date range.

MediumTechnical

17 practiced

Write SQL to compute the difference between two snapshots of a table customers_snapshot(date, customer_id, email, status) to find changed, new, and removed customers between snapshot dates D1 and D2. Provide a UNION-based diff or a FULL OUTER JOIN approach and explain which is clearer/performant.

Sample Answer

Approach: compare two snapshots filtered to dates D1 (older) and D2 (newer). Show two options: FULL OUTER JOIN (clearer) and UNION-based (works where FULL OUTER JOIN unsupported). Explain trade-offs.

FULL OUTER JOIN (recommended when supported)

sql

-- D1 = '2025-01-01', D2 = '2025-02-01'
WITH s1 AS (
  SELECT customer_id, email AS email_d1, status AS status_d1
  FROM customers_snapshot
  WHERE date = '2025-01-01'
),
s2 AS (
  SELECT customer_id, email AS email_d2, status AS status_d2
  FROM customers_snapshot
  WHERE date = '2025-02-01'
)
SELECT
  COALESCE(s2.customer_id, s1.customer_id) AS customer_id,
  s1.email_d1, s1.status_d1,
  s2.email_d2, s2.status_d2,
  CASE
    WHEN s1.customer_id IS NULL THEN 'new'
    WHEN s2.customer_id IS NULL THEN 'removed'
    WHEN (s1.email_d1 <> s2.email_d2 OR s1.status_d1 <> s2.status_d2) THEN 'changed'
    ELSE 'unchanged'
  END AS diff_type
FROM s1
FULL OUTER JOIN s2 USING (customer_id);

UNION-based (works without FULL OUTER JOIN)

sql

WITH s1 AS (...), s2 AS (...)
-- new and changed/unchanged
SELECT s2.customer_id, s1.email_d1, s1.status_d1, s2.email_d2, s2.status_d2,
  CASE WHEN s1.customer_id IS NULL THEN 'new'
       WHEN (s1.email_d1 <> s2.email_d2 OR s1.status_d1 <> s2.status_d2) THEN 'changed'
       ELSE 'unchanged' END AS diff_type
FROM s2
LEFT JOIN s1 USING (customer_id)

UNION ALL

-- removed
SELECT s1.customer_id, s1.email_d1, s1.status_d1, NULL, NULL, 'removed'
FROM s1
LEFT JOIN s2 USING (customer_id)
WHERE s2.customer_id IS NULL;

Which is clearer/performant:- FULL OUTER JOIN is clearer and more concise; it expresses intent directly and usually performs well with proper indexes (customer_id) since DB can plan a single set operation.- UNION-based is more verbose and may scan or join twice but is necessary on DBs without FULL OUTER JOIN (e.g., older MySQL). Performance differences depend on optimizer; with large data ensure filtering to the two dates first, index on (date, customer_id), and consider hashing/partitioning.Edge cases: NULLable columns (use IS DISTINCT FROM where supported), duplicate customer_id per date (aggregate or deduplicate first), and large snapshots — filter and index before join.

Unlock Full Question Bank

Get access to hundreds of Advanced Querying with Structured Query Language interview questions and detailed answers.

Join thousands of developers preparing for their dream job.