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.

MediumTechnical

18 practiced

Tables:- users(user_id, region)- orders(order_id, user_id, amount, created_at)- promotions(promo_id, user_id, applied_at)Write a query to compute total revenue per region including only orders where a promotion was applied within 30 days before the order. Explain which JOIN type to use and where to place the date predicate to avoid accidentally converting an OUTER JOIN to an INNER JOIN.

Sample Answer

Approach: Use a LEFT JOIN from orders to promotions (so we keep orders even if no promo) and filter promotions to those applied within 30 days before the order using the JOIN condition (not the WHERE clause). Then join orders -> users to get region and aggregate only orders that have a matching promo (i.e., non-null promo) by filtering in the WHERE after join to users or by aggregating conditional sums. Placing the date predicate inside the ON clause avoids turning the LEFT JOIN into an INNER JOIN.

SQL (Postgres-compatible example):

sql

SELECT
  u.region,
  SUM(o.amount) AS total_revenue
FROM orders o
JOIN users u
  ON o.user_id = u.user_id
LEFT JOIN promotions p
  ON p.user_id = o.user_id
  AND p.applied_at BETWEEN o.created_at - INTERVAL '30 days' AND o.created_at
-- only keep orders that had a qualifying promo
WHERE p.promo_id IS NOT NULL
GROUP BY u.region;

Key points:- Use LEFT JOIN so you can express the temporal predicate in ON without filtering rows prematurely.- Put the date-range condition in the ON clause; putting it in WHERE (e.g., WHERE p.applied_at BETWEEN ...) would null-out unmatched p rows and act like an INNER JOIN.- If multiple promos can match an order, this may double-count orders; dedupe by using DISTINCT promo selection (e.g., EXISTS or aggregate by order_id first). Example alternative using EXISTS to avoid duplicates:

sql

SELECT u.region, SUM(o.amount) total_revenue
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE EXISTS (
  SELECT 1 FROM promotions p
  WHERE p.user_id = o.user_id
    AND p.applied_at BETWEEN o.created_at - INTERVAL '30 days' AND o.created_at
)
GROUP BY u.region;

Edge cases:- Timezones / timestamp types — ensure consistent types.- Multiple promos per order — handle deduplication as shown.

MediumTechnical

20 practiced

You have customers_master(customer_id) and customers_active(customer_id, last_active_date). Write SQL to find customers in master who have no active record in the last 12 months. Compare three approaches: LEFT JOIN ... WHERE active.customer_id IS NULL, NOT EXISTS, and EXCEPT (or MINUS). Discuss performance trade-offs and which you would prefer.

Sample Answer

Approach summary: filter customers_master for those that do NOT have an customers_active row within the last 12 months. Three common SQL patterns—LEFT JOIN anti-join, NOT EXISTS, and EXCEPT/MINUS—are shown, with trade-offs and my preference.

LEFT JOIN ... IS NULL

sql

SELECT m.customer_id
FROM customers_master m
LEFT JOIN (
  SELECT DISTINCT customer_id
  FROM customers_active
  WHERE last_active_date >= CURRENT_DATE - INTERVAL '12 months'
) a ON m.customer_id = a.customer_id
WHERE a.customer_id IS NULL;

NOT EXISTS

sql

SELECT m.customer_id
FROM customers_master m
WHERE NOT EXISTS (
  SELECT 1
  FROM customers_active a
  WHERE a.customer_id = m.customer_id
    AND a.last_active_date >= CURRENT_DATE - INTERVAL '12 months'
);

EXCEPT (or MINUS)

sql

-- PostgreSQL / standard
SELECT customer_id FROM customers_master
EXCEPT
SELECT customer_id
FROM customers_active
WHERE last_active_date >= CURRENT_DATE - INTERVAL '12 months';

Key points and trade-offs:- Correctness: All three work if you dedupe customers_active or use DISTINCT/EXISTS. EXCEPT removes duplicates implicitly.- NULL/duplicate behavior: LEFT JOIN can produce duplicates if customers_master has duplicates; DISTINCT helps. NOT EXISTS naturally handles duplicates in the right side. EXCEPT compares sets.- Performance: Most engines optimize NOT EXISTS into an anti-join; it's often the most readable and reliably performant. LEFT JOIN ... IS NULL can be optimized similarly, but historically performed worse when the planner chooses a hashed join and large intermediate result sets appear. EXCEPT may require sorting/hash to dedupe both sets — potentially expensive for large tables.- Indexing: Ensure customers_active.customer_id is indexed (and ideally last_active_date included or partitioned by date) so the WHERE last_active_date filter is selective and indexable.- Scale considerations: For very large active tables, use a prefiltered or materialized list of active customer_ids (daily refresh) or partition pruned queries to avoid full scans.

My preference: NOT EXISTS. It is clear, avoids unnecessary intermediate rows, and most modern optimizers turn it into an efficient anti-join. If profiling shows a different plan, choose the variant the DB’s optimizer handles best and ensure proper indexing or pre-aggregation.

MediumTechnical

20 practiced

Given price_timeseries(symbol, trade_time TIMESTAMP, price DECIMAL), write SQL to compute both a 7-day moving average and a 30-day moving average per symbol aligned to each trade_time using window functions. Explain the choice of frame (ROWS vs RANGE) and performance implications on large time series data.

Sample Answer

Approach: use windowed AVG over each symbol partition ordered by trade_time, with a time-based frame so the window includes rows whose timestamps fall within the last 7 (or 30) days up to the current trade_time. For time windows use RANGE ... INTERVAL to express "last N days"; ROWS would instead refer to a fixed number of rows (not correct for irregular time series).

SQL (Postgres-style):

sql

SELECT
  symbol,
  trade_time,
  price,
  AVG(price) OVER (
    PARTITION BY symbol
    ORDER BY trade_time
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS ma_7d,
  AVG(price) OVER (
    PARTITION BY symbol
    ORDER BY trade_time
    RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
  ) AS ma_30d
FROM price_timeseries;

Key points / reasoning:- PARTITION BY symbol scopes the computation per symbol.- ORDER BY trade_time plus RANGE BETWEEN INTERVAL 'N days' PRECEDING AND CURRENT ROW yields a logical time window covering N+1 days including current timestamp (6 days preceding + current = 7-day window).- Use RANGE for time-based windows because ROWS N PRECEDING would pick the last N physical rows (wrong if sampling frequency varies).

Edge cases & caveats:- If multiple rows share the same trade_time, RANGE treats them all as within the same logical boundary; if you want deterministic tie-breaking, add a unique tie column to ORDER BY.- Sparse data: a 7-day window may include fewer than expected rows — this is correct for time-based averages.- Timezones: ensure trade_time is consistent (use TIMESTAMPTZ if needed).

Performance implications on large time series:- RANGE frames are logically convenient but may be more expensive because the DB must identify rows in a time interval for each row; optimizers differ across engines.- ROWS frames are cheaper when you truly need fixed-row windows (uses offset pointers).- For large datasets, improve performance by: - Partitioning the table by symbol and range (e.g., by month) so scans are limited. - Creating a composite index on (symbol, trade_time) so range scans are efficient. - Pre-aggregating to daily buckets if intra-day detail isn’t required. - Using cumulative SUM/COUNT (running totals) and subtracting earlier cumulative values: compute cumulative sum ordered by trade_time, then for each row find the last trade_time < current_time - N days (via lateral join or binary search on array of times) to compute window sum in O(1) per row; this reduces repeated scanning. - Consider materialized incremental views for frequently queried moving averages.

Complexity: naive window evaluation is effectively O(n) per partition if the engine maintains sliding-window state (good engines do), but without optimizations it can be costly. Test on representative data and use explain/analyze to confirm plan.

HardTechnical

19 practiced

Compute for each user the average event_value per session (session_id, user_id), then compute a 7-session moving average of those session averages per user ordered by session_start. Provide SQL that first aggregates per session and then applies a window function across sessions.

Sample Answer

Approach: first aggregate events to compute one session-level average per (session_id, user_id) with its session_start. Then use a window function partitioned by user_id ordered by session_start (and session_id to break ties) to compute a 7-session moving average of those session averages.

sql

WITH session_avg AS (
  -- Aggregate raw events into one row per session
  SELECT
    s.session_id,
    s.user_id,
    MIN(s.session_start) AS session_start,      -- or use the session table's start
    AVG(e.event_value) AS avg_event_value_per_session,
    COUNT(*) AS event_count
  FROM events e
  JOIN sessions s
    ON e.session_id = s.session_id
  GROUP BY s.session_id, s.user_id
),
session_ranked AS (
  -- Ensure deterministic ordering (session_start + session_id)
  SELECT
    session_id,
    user_id,
    session_start,
    avg_event_value_per_session
  FROM session_avg
)
SELECT
  user_id,
  session_id,
  session_start,
  avg_event_value_per_session,
  -- 7-session moving average (current + 6 prior sessions)
  ROUND(
    AVG(avg_event_value_per_session) OVER (
      PARTITION BY user_id
      ORDER BY session_start, session_id
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 6
  ) AS moving_avg_last_7_sessions
FROM session_ranked
ORDER BY user_id, session_start, session_id;

Key points and reasoning:- session_avg CTE collapses events into per-session averages so window operates on session-level values.- Use ORDER BY session_start, session_id to guarantee stable order if sessions share the same timestamp.- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW computes a fixed 7-row window (7 sessions). If you want a 7-day window instead, use RANGE with timestamps.- ROUND is optional for readability.- If a user has fewer than 7 sessions, the window averages available sessions (no padding).

Edge cases:- Sessions with zero events are excluded by the join; if you need them, left join and treat avg as NULL or 0.- If event_value can be NULL, use AVG(COALESCE(e.event_value,0)) or filter NULLs explicitly.- For large tables, ensure sessions table is indexed on session_id and (user_id, session_start) for performance.

Alternatives:- Use LAG to compute custom weighted windows (less convenient).- Use RANGE BETWEEN INTERVAL '...' PRECEDING for time-based windows rather than fixed-count sessions.

HardTechnical

25 practiced

You have a query that joins orders -> order_items -> discounts and aggregates revenue by customer, but results are inflated due to join duplication. Show how to refactor the query using subqueries or CTEs to avoid double-counting order item amounts when discounts are at the order level. Explain why the original plan caused duplication and how your refactor fixes it.

Sample Answer

Original problem: joining orders -> order_items -> discounts where discounts are at order level causes each order-level discount row to multiply rows from order_items, inflating aggregates.

Problematic query (simplified):

sql

SELECT
  o.customer_id,
  SUM(oi.price * oi.quantity * (1 - d.percent)) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN discounts d ON d.order_id = o.id
GROUP BY o.customer_id;

Why duplication happens:- If an order has N items and M discount rows (e.g., multiple discount records or a single row that should apply once), the join produces N*M rows. The discount factor repeats per item-row, or if multiple discounts exist it's applied multiple times, overcounting.

Refactor: compute order-level values once (aggregate items per order), then join discounts and combine at order granularity (CTE or subquery):

sql

WITH items_per_order AS (
  SELECT
    order_id,
    SUM(price * quantity) AS items_total
  FROM order_items
  GROUP BY order_id
),
best_discount AS (
  -- pick appropriate discount per order (example: max percent or single active)
  SELECT order_id, MAX(percent) AS percent
  FROM discounts
  GROUP BY order_id
)
SELECT
  o.customer_id,
  SUM(ipo.items_total * COALESCE(1 - bd.percent, 1)) AS revenue
FROM orders o
LEFT JOIN items_per_order ipo ON ipo.order_id = o.id
LEFT JOIN best_discount bd ON bd.order_id = o.id
GROUP BY o.customer_id;

Why this fixes it:- items_total is computed once per order so no multiplicative row explosion.- discounts are reduced to one row per order (via aggregation/selection) so they apply exactly once.- Keeps aggregation logic clear and avoids double-counting.

Performance notes:- Pre-aggregating reduces join size. Ensure proper indexes on order_items(order_id) and discounts(order_id). If discounts have complex rules, compute discount amount per order in the discount CTE to keep final join simple.

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.