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

20 practiced

You are asked to create a SQL code-review checklist for mentoring junior engineers on query quality for ML pipelines. Produce at least 10 checklist items covering correctness, performance, maintainability, reproducibility, and data lineage. For each item give a one-sentence rationale.

Sample Answer

1) Verify correctness of joins and key usage — ensure join conditions use correct keys and join types to avoid Cartesian products or silent row drops. Rationale: Incorrect joins corrupt training labels/features and produce silent data-skew bugs.

2) Check predicate pushdown and filter order — filters that dramatically reduce rows should be applied early and in the database where possible. Rationale: Early filtering minimizes I/O and speeds up ETL for model training.

3) Validate aggregation semantics and GROUP BY correctness — confirm grouping, HAVING, and aggregations match intended business logic. Rationale: Wrong aggregations change feature distributions and model targets.

4) Ensure deterministic ordering where needed (ORDER BY with explicit columns) — don't rely on unspecified row order for sampling or batching. Rationale: Deterministic ordering is required for reproducible train/test splits and debugability.

5) Avoid SELECT *; list explicit columns with types — select only required columns and document their types. Rationale: Explicit columns prevent schema drift, reduce data transfer, and make feature lineage clear.

6) Enforce schema and null handling (COALESCE/NULL checks) — handle NULLs and validate types before downstream use. Rationale: Nulls and implicit casts create subtle bugs in feature engineering and model inputs.

7) Add comments and rationale for complex transformations — explain nontrivial logic, assumptions, and business rules inline. Rationale: Readable rationale accelerates onboarding and reduces incorrect rework.

8) Parameterize and avoid hard-coded constants — use config tables or parameters for dates, thresholds, and environment-specific values. Rationale: Parameterization enables reproducible runs across environments and time windows.

9) Confirm window-function frames and partitioning are correct — explicitly define PARTITION BY and frame clauses for deterministic results. Rationale: Mis-specified windows produce inconsistent feature calculations across runs.

10) Profile and estimate cardinality and cost for heavy operations — identify expensive joins, sorts, and GROUP BYs and propose indexes or pre-aggregations. Rationale: Cost awareness prevents slow pipelines and resource contention in production.

11) Record provenance metadata and lineage links (source tables, job IDs, versions) — ensure each output includes references to input datasets and SQL revision. Rationale: Lineage is essential for debugging drift, audits, and retraining decisions.

12) Add tests and assertions (row counts, value ranges, uniqueness) and include them in CI — include lightweight SQL checks as part of the pipeline. Rationale: Automated assertions catch regressions early and guarantee data quality for models.

13) Check for idempotence and safe re-runs — ensure queries can be retried without duplicating or corrupting outputs. Rationale: Idempotent pipelines simplify recovery and support reliable scheduled training.

14) Evaluate privacy and compliance (PII masking, access controls) — ensure sensitive fields are redacted or access-restricted per policy. Rationale: Protecting data maintains legal compliance and model trustworthiness.

This checklist balances correctness, performance, maintainability, reproducibility, and lineage to mentor juniors toward production-ready SQL for ML pipelines.

HardSystem Design

21 practiced

Explain the importance of data locality and colocation for repeated joins in distributed query engines. Provide concrete partitioning/bucketing strategies that minimize shuffle for common join patterns in ML pipelines and examples of how to implement them in SQL write paths.

Sample Answer

Start with why it matters: repeated joins in ML pipelines (feature joins with labels, enrichment with user/item metadata, historical aggregations) can dominate job time because shuffles (network + disk + serialization) are expensive. Data locality and colocation minimize or eliminate shuffles so joins become map-side (local) operations or cheap merges — reducing latency, cost, and cluster load and making iterative model training and backfills practical.

Key strategies- Hash partition / bucket by join key: ensure all rows with same join key land on same partition/node so joins are local and can use sort-merge or hash-based map-side joins.- Co-partition/co-locate related tables: write features and label tables with identical partitioning/bucketing and sort order.- Partition by time + bucket by key: for time-series ML, partition by date to prune scans, and bucket within partition by key to keep joins local for same-day data.- Small-side broadcast: when one table is small (< broadcast threshold), broadcast it to avoid shuffle.- Sort within bucket (bucket-sort): enables efficient merge joins and incremental compaction.

Concrete SQL write-path examples

Spark (DataFrame -> Hive/ORC):- Partition by date and bucket by user_id into 256 buckets, sorted by user_id:

sql

CREATE TABLE feature_store.user_features (
  user_id STRING, feature1 DOUBLE, feature2 DOUBLE, ds DATE
)
USING ORC
PARTITIONED BY (ds)
CLUSTERED BY (user_id) INTO 256 BUCKETS
SORTED BY (user_id) INTO 256 BUCKETS;

Write using Spark so bucketing is preserved:

python

df.write \
  .format("orc") \
  .mode("overwrite") \
  .partitionBy("ds") \
  .bucketBy(256, "user_id") \
  .sortBy("user_id") \
  .saveAsTable("feature_store.user_features")

When joining label table also written with same PARTITION/Bucket settings, Spark can perform bucketed joins without full shuffle (spark.sql.sources.bucketing.enabled = true).

Presto / Hive (SQL):- Hive bucketing + dynamic partitioning:

sql

CREATE TABLE labels (
  user_id STRING, label INT
)
PARTITIONED BY (ds STRING)
CLUSTERED BY (user_id) INTO 256 BUCKETS
STORED AS ORC;

Insert ensuring DISTRIBUTE BY user_id so files land in same buckets:

sql

FROM staging_labels
INSERT OVERWRITE TABLE labels PARTITION(ds)
SELECT user_id, label, ds
DISTRIBUTE BY user_id;

Patterns for common ML joins- Training (features JOIN labels): co-partition/bucket by user_id and ds; result: map-side merges, minimal shuffle.- Feature-aggregation rollups: partition by ds, bucket by key used for group-by then persisted for downstream joins.- Cold features/static metadata: keep small table and broadcast it (or replicate) so joins are broadcast hash joins.

Trade-offs & operational notes- Choosing bucket count: align to cluster cores and data cardinality; too many small files increases overhead; too few -> skew.- Handle skew: use salted keys for heavy hitters or separate hot-key handling.- Compatibility: ensure engine (Spark/Hive/Presto) supports bucketed map-side joins and configure settings (e.g., spark.sql.shuffle.partitions, broadcast thresholds).- Schema evolution & compaction: maintain consistent bucketing across rewrites; compact small files to preserve performance.

Bottom line: design write paths to produce co-partitioned, bucketed, and (when useful) sorted datasets so repeated joins in ML pipelines become local, predictable operations — dramatically reducing shuffle cost and improving training/serving throughput.

MediumTechnical

23 practiced

Given a table user_events(user_id, event_date date) write a SQL query to detect users who made purchases on 3 consecutive days. Show an approach using window functions (lag/row_number) that groups consecutive days together. Explain how the 'date - row_number()' trick works.

Sample Answer

To find users who purchased on 3 consecutive days, we can assign a dense sequential index per user ordered by date and use the "date - row_number()" trick to collapse consecutive dates into the same group. Consecutive calendar dates differ from their row_number by a constant; subtracting the integer row_number from the date yields the same value for all days in a consecutive run.

Query (Postgres syntax — works for other DBs with date arithmetic):

sql

WITH numbered AS (
  SELECT
    user_id,
    event_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date) AS rn
  FROM (
    -- remove duplicates in same day if any
    SELECT DISTINCT user_id, event_date
    FROM user_events
  ) d
),
grp AS (
  SELECT
    user_id,
    event_date,
    rn,
    (event_date - (rn || ' days')::interval)::date AS grp_start_key
  FROM numbered
)
SELECT
  user_id,
  MIN(event_date) AS start_date,
  MAX(event_date) AS end_date,
  COUNT(*) AS consecutive_days
FROM grp
GROUP BY user_id, grp_start_key
HAVING COUNT(*) >= 3
ORDER BY user_id, start_date;

Why it works:- For a user with dates e.g. 2025-01-01, 2025-01-02, 2025-01-03, rn = 1,2,3.- Subtracting rn days gives 2024-12-31, 2024-12-31, 2024-12-31 — same key => grouped.- Non-consecutive dates produce different keys and start new groups.

Edge cases:- Ensure distinct per-day events or deduplicate first.- If DB uses integer date arithmetic (e.g., MySQL: DATE_SUB(event_date, INTERVAL rn DAY) or use event_date - INTERVAL rn DAY).- Timezones: use date (not timestamp) to avoid partial-day issues.

MediumTechnical

17 practiced

Write a SQL snippet using LATERAL (Postgres) to, for each user in users(user_id), pick the latest event before cutoff_time from events(user_id, event_time, payload) and join it to the users table. Explain performance characteristics of LATERAL vs window functions for selecting the single latest row per group.

Sample Answer

Use LATERAL to pick, per user, the single most-recent event before a cutoff. LATERAL allows the right-side subquery to reference the current row from users and return at most one row efficiently:

sql

-- cutoff_time is a timestamp parameter
SELECT u.user_id, e.event_time, e.payload
FROM users u
LEFT JOIN LATERAL (
  SELECT ev.event_time, ev.payload
  FROM events ev
  WHERE ev.user_id = u.user_id
    AND ev.event_time < :cutoff_time
  ORDER BY ev.event_time DESC
  LIMIT 1
) e ON true;

Performance notes and trade-offs:- Indexes: Create an index to support the per-user top-N lookup: CREATE INDEX ON events (user_id, event_time DESC); This lets Postgres seek to the latest row for each user and stop after LIMIT 1, making LATERAL very fast when users are many but each per-user scan is cheap.- LATERAL pros: Executes per outer row and can short-circuit with LIMIT; ideal when you need a small number of rows per key, or when outer set is filtered/small. Works well with selective predicates on users.- Window-function approach (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC)) requires scanning and sorting or partitioning the full events set (or at least the joined subset) which can be more I/O/memory intensive but can be faster if you want top-1 for many users in one pass and can leverage a single sort/partition.- Choose LATERAL when you can index and do targeted seeks; choose window functions when you prefer set-based processing (large joins where a single scan + partition is cheaper). Always check EXPLAIN (ANALYZE) to compare plans.

HardTechnical

25 practiced

Explain how database configuration parameters such as work_mem, maintenance_work_mem, effective_cache_size, and parallel_workers affect analytical query performance in PostgreSQL. Describe steps you would take to tune for costly JOINs and sorts on large tables used for ML training.

Sample Answer

work_mem, maintenance_work_mem, effective_cache_size and parallel_workers are key knobs that change the PostgreSQL planner’s choices and the runtime resource usage — which directly affects analytical queries common in ML pipelines (large JOINs, aggregations, sorts).

What each does (high-level):- work_mem: memory allowed per sort/hash operation per query worker. Larger work_mem lets sorts and hash joins stay in RAM (faster); too large and concurrent queries exhaust RAM because it's allocated per operation per backend.- maintenance_work_mem: memory for maintenance tasks (CREATE INDEX, VACUUM FULL, CLUSTER). Increasing it speeds index builds and bulk vacuuming used when preparing training datasets.- effective_cache_size: planner hint about OS + DB cache size. Higher values bias planner toward index/bitmap index plans by assuming more data will be cached; it does not allocate memory.- parallel_workers / max_parallel_workers_per_gather: number of workers planner can use for parallel scans/joins/aggregates. Helps scale CPU-bound parts of analytics but increases I/O contention and memory usage per worker (each uses work_mem).

Tuning steps for costly JOINs and large sorts (practical checklist):1. Measure: run EXPLAIN (ANALYZE, BUFFERS) to see join types, sort spill-to-disk, temp files, and worker usage. Check pg_stat_activity and pg_stat_statements for hotspots.2. Reduce data scanned: - Push predicates, use covering indexes, and ensure statistics are current (ANALYZE). For ML training, pre-filter or store feature tables in columnar external storage (Parquet) or use MATERIALIZED VIEW. - Consider partitioning large tables by date or shard key used in joins.3. Indexing & join strategy: - Add btree indexes on join keys; for selective joins use index scans; for large many-to-many joins prefer hash/merge joins. - Force different join methods temporarily (enable_* GUCs) to compare.4. Memory and planner tuning: - Raise effective_cache_size to a realistic fraction of RAM (e.g., 50–75% minus OS/buffer needs) so planner favors index/bitmap plans. - Increase work_mem for specific sessions/queries (SET LOCAL work_mem = '512MB') to keep sorts and hash tables in-memory; compute safe global: worst-case memory = work_mem * num_concurrent_ops * workers. Tune conservatively. - Increase maintenance_work_mem when building/rebuilding large indexes for feature tables.5. Parallelism: - Raise max_parallel_workers_per_gather if CPU-bound and I/O can handle it; ensure max_worker_processes and max_parallel_workers are sufficient. - Monitor for diminishing returns — too many workers increases random I/O and context switching.6. Prevent spill-to-disk: - Use EXPLAIN BUFFERS to detect temp files. If sorts are spilling, either increase work_mem for that query or rewrite query to reduce sort size (pre-aggregate, limit columns).7. Storage and I/O: - Use fast SSDs, separate temp_tablespaces on faster disks, tune checkpoint and wal settings for large imports.8. Operational: VACUUM (or autovacuum tuned), run ANALYZE after large loads, consider CLUSTER or REINDEX for heavily updated tables to keep indexes efficient.9. Alternatives when relational DB hits limits: - Offload heavy ML data prep to Spark/Dask or use columnar DBs/data warehouses (ClickHouse, Redshift, BigQuery) for massive scans.

Example safe-memory calc:If work_mem=64MB, a query doing 3 concurrent hash joins with 4 parallel workers could use up to 64MB * 3 * 4 = 768MB per session for those operations — multiply by expected concurrent sessions to avoid OOM.

Key trade-offs:- Bigger work_mem reduces disk I/O but raises risk of OOM under concurrency.- Higher parallelism speeds CPU-bound operations but can increase I/O pressure.- effective_cache_size doesn’t change runtime memory — misestimating it misleads planner.

Conclusion: iterate—measure with EXPLAIN ANALYZE, tune per-query with SET, adjust cluster-wide GUCs only after modeling memory concurrency, and use indexes/partitioning/materialized views to reduce work for heavy JOINs/sorts used in ML training.

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.