Structured Query Language Fundamentals and Aggregation Questions

This topic covers core Structured Query Language fundamentals for analytical querying and reporting. Candidates should be able to write correct, readable, and maintainable SELECT queries with filtering using WHERE, sorting with ORDER BY, grouping with GROUP BY, and group filtering with HAVING. They should apply aggregate functions such as COUNT, COUNT DISTINCT, SUM, AVG, MIN, and MAX and understand how NULL values affect results, how empty result sets behave, and when to use different counting approaches. The scope includes date and time filtering, basic cohort segmentation, and common time based comparisons used to compute metrics such as daily active users, average revenue per user, and period over period comparisons. Candidates are expected to use basic joins and join predicates including inner joins and left joins, write simple subqueries and conditional expressions, and perform common data transformation and cleansing patterns to prepare data for analysis. Finally, this topic assesses query readability and maintainability practices such as aliasing and formatting, plus awareness of elementary performance considerations including index usage and avoiding unnecessary full table scans for entry to mid level analytical tasks.

HardTechnical

53 practiced

You need to one-hot encode a categorical column 'category' with moderate cardinality (under 20) into columns for model training using SQL. Write a SQL query that pivots categories into separate boolean columns per user, explain the approach (CASE aggregates or crosstab), and discuss performance and maintainability trade-offs when the cardinality increases.

Sample Answer

Approach: for moderate cardinality (<20) easiest portable solution is CASE aggregates (SUM/MAX over CASE) to pivot into boolean one‑hot columns per user. In Postgres you can also use crosstab (tablefunc) which is concise but less portable and requires knowing columns ahead of time.

SQL (CASE-aggregate portable):

sql

SELECT
  user_id,
  MAX(CASE WHEN category = 'A' THEN 1 ELSE 0 END) AS cat_A,
  MAX(CASE WHEN category = 'B' THEN 1 ELSE 0 END) AS cat_B,
  MAX(CASE WHEN category = 'C' THEN 1 ELSE 0 END) AS cat_C
  -- repeat for each category (generate dynamically if needed)
FROM events
GROUP BY user_id;

SQL (Postgres crosstab example):

sql

SELECT * FROM crosstab(
  $$SELECT user_id, category, 1 FROM events ORDER BY 1,2$$,
  $$VALUES ('A'),('B'),('C')$$
) AS ct(user_id int, cat_A int, cat_B int, cat_C int);

Key points and trade-offs:- CASE approach: highly portable, easy to generate via script, predictable NULL/0 semantics; may be verbose but fast with proper indexing and GROUP BY. Use MAX to collapse multiple rows.- crosstab: compact, potentially faster for wide pivots in Postgres but requires tablefunc and fixed category list; changes need DDL updates.- If cardinality grows (>50–100): one-hot becomes high-dim — leads to sparse features, model bloat, slower training. Consider alternatives: target/hashing encoding, embedding vectors (learned or via lookup), or feature hashing to bound dimensionality.- Maintainability: generate SQL programmatically from distinct categories, add CI to detect new categories, and prefer encoding strategies that scale with cardinality.

MediumTechnical

60 practiced

You maintain a long, nested SQL that calculates monthly metrics with several subqueries. Rewrite the structure into readable CTEs (WITH ...), explain the pros/cons of CTEs for readability and performance, and show a short example converting two nested subqueries into two named CTEs.

Sample Answer

Using named CTEs (WITH ...) makes complex, nested SQL far easier to read, test, and maintain—each logical step gets a descriptive name and can be validated independently. For an AI engineer producing monthly metrics pipelines, CTEs help separate feature engineering, filtering, and aggregation steps and make it straightforward to add instrumentation (row counts, debug columns) or swap model features.

Pros:- Readability: logical decomposition with names/comments.- Reusability within a single query: reference the same CTE multiple times.- Easier debugging: run a single CTE to inspect intermediate results.- Modularity: easier to version and document transformations.

Cons:- Performance: some databases (older engines) materialize CTEs, which can increase I/O and memory; others inline them so no penalty. Complex CTE chains can prevent optimizer from pushing predicates.- Lifetime limited to query: cannot reuse across queries without views or temp tables.- Overuse can make planning harder; test performance after refactor.

Example — convert two nested subqueries into CTEs.

Original (nested):

sql

SELECT m.month, m.user_count, purchases.total_spend
FROM (
  SELECT date_trunc('month', created_at) AS month, COUNT(DISTINCT user_id) AS user_count
  FROM users WHERE active = true GROUP BY 1
) m
JOIN (
  SELECT date_trunc('month', p.created_at) AS month, SUM(amount) AS total_spend
  FROM purchases p WHERE p.status = 'completed' GROUP BY 1
) purchases ON m.month = purchases.month;

Refactored with CTEs:

sql

WITH active_users AS (
  SELECT date_trunc('month', created_at) AS month,
         COUNT(DISTINCT user_id) AS user_count
  FROM users
  WHERE active = true
  GROUP BY 1
),
monthly_purchases AS (
  SELECT date_trunc('month', created_at) AS month,
         SUM(amount) AS total_spend
  FROM purchases
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT a.month, a.user_count, p.total_spend
FROM active_users a
JOIN monthly_purchases p USING (month);

Practical tips: after refactor, run EXPLAIN to compare plans; if a CTE is expensive and reused, consider a temporary table or materialized view for heavy pipelines. Use short, descriptive CTE names and add brief comments for each transformation.

EasyTechnical

56 practiced

You are handed a long, single-line SQL used in a report with no aliases or formatting. Describe 5 concrete readability and maintainability changes you would apply (naming, aliasing, comments, CTEs, ordering), then show a short example refactor of a messy JOIN into a readable CTE-based query.

Sample Answer

1) Use clear, consistent naming: rename cryptic columns/tables to meaningful names (user_id → customer_id, amt_usd → revenue_usd). 2) Add table aliases and qualify columns: short, consistent aliases (u, o, p) to prevent ambiguity. 3) Add comments: explain business logic, assumptions, and tricky joins/filters above blocks. 4) Break into CTEs: split stages (base data, filters, aggregations) so each piece has a purpose and is testable. 5) Order and format logically: SELECT columns grouped (keys, metrics), JOINs in logical flow, predicates in WHERE, and ORDER BY last.

Example: messy one-line JOIN refactored to readable CTEs

sql

-- Get customers with their latest paid order and total revenue last 30 days
WITH recent_orders AS (
  -- base: paid orders in last 30 days
  SELECT
    o.id AS order_id,
    o.customer_id,
    o.total_amount_usd,
    o.created_at
  FROM orders o
  WHERE o.status = 'PAID'
    AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
),
latest_order AS (
  -- latest order per customer
  SELECT DISTINCT ON (r.customer_id)
    r.customer_id,
    r.order_id,
    r.total_amount_usd,
    r.created_at AS last_order_at
  FROM recent_orders r
  ORDER BY r.customer_id, r.created_at DESC
),
revenue_by_customer AS (
  -- aggregation per customer
  SELECT
    customer_id,
    SUM(total_amount_usd) AS revenue_last_30d
  FROM recent_orders
  GROUP BY customer_id
)
SELECT
  c.id AS customer_id,
  c.email,
  l.order_id AS last_paid_order_id,
  l.last_order_at,
  r.revenue_last_30d
FROM customers c
JOIN latest_order l ON c.id = l.customer_id
LEFT JOIN revenue_by_customer r ON c.id = r.customer_id
WHERE c.is_active = true
ORDER BY r.revenue_last_30d DESC NULLS LAST;

Key benefits: easier debugging, reuse of CTEs in tests, explicit intent via names/comments, and simpler incremental verification.

MediumTechnical

53 practiced

Tables:orders(order_id, user_id, placed_at)order_items(item_id, order_id, product_id, price)

Write a SQL query to compute the number of unique users who purchased in the last 30 days. Explain why joining orders to order_items naively can inflate counts and demonstrate the safe SQL pattern to avoid inflated unique-user counts.

Sample Answer

Approach: If you only need the number of unique users who made a purchase in the last 30 days, count distinct user_id from the orders table filtered by date. Avoid joining to order_items naively because each order can have multiple items — joining duplicates order rows and will inflate counts if you then count rows (or even user_id without DISTINCT).

Naive (inflates counts):

sql

-- WRONG: joining multiplies rows for orders with multiple items
SELECT COUNT(*) AS purchases_rows
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.placed_at >= CURRENT_DATE - INTERVAL '30 days';

If you then counted users without DISTINCT, or aggregated incorrectly, you'd overcount.

Safe patterns:

1) Simple and efficient — count distinct users directly from orders:

sql

SELECT COUNT(DISTINCT user_id) AS unique_buyers_30d
FROM orders
WHERE placed_at >= CURRENT_DATE - INTERVAL '30 days';

2) If you must reference order_items (e.g., filter by product or price), deduplicate at the order or user level first, or use EXISTS:

sql

-- dedupe orders by user
SELECT COUNT(*) AS unique_buyers_30d
FROM (
  SELECT DISTINCT o.user_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.placed_at >= CURRENT_DATE - INTERVAL '30 days'
    AND oi.product_id = 123  -- example filter
) t;

-- or use EXISTS to avoid row multiplication
SELECT COUNT(DISTINCT o.user_id) AS unique_buyers_30d
FROM orders o
WHERE o.placed_at >= CURRENT_DATE - INTERVAL '30 days'
  AND EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.order_id = o.order_id
      AND oi.product_id = 123
  );

Why these are safe: COUNT(DISTINCT user_id), SELECT DISTINCT user_id, or EXISTS ensure each user is counted once regardless of how many items or rows a join would produce.

HardTechnical

45 practiced

Tables:transactions(user_id, occurred_at, revenue)

Write SQL to compute cohort LTV (lifetime value): for each signup cohort (by month), compute the cumulative revenue per user at 30, 60, and 90 days after signup, then compute the cohort median LTV at each horizon. Use window functions and explain steps to avoid double-counting revenue across horizons.

Sample Answer

Approach: derive each user’s signup date (first transaction), join transactions to that signup to compute days-since-signup, aggregate per user the cumulative revenue up to 30/60/90 days (each horizon computed independently to avoid overlap/double-counting), then compute cohort (signup month) median LTV per horizon using percentile_cont(0.5).

sql

-- Postgres-style solution
WITH user_signup AS (
  -- signup = first observed transaction (if real signup table exists, use that)
  SELECT user_id,
         MIN(occurred_at)::date AS signup_date,
         TO_CHAR(MIN(occurred_at)::date, 'YYYY-MM') AS cohort_month
  FROM transactions
  GROUP BY user_id
),
tx_with_meta AS (
  SELECT t.user_id,
         t.occurred_at::date AS tx_date,
         t.revenue,
         u.signup_date,
         u.cohort_month,
         (t.occurred_at::date - u.signup_date) AS days_since_signup
  FROM transactions t
  JOIN user_signup u USING (user_id)
),
per_user_horizon AS (
  -- compute cumulative revenue per user for each horizon independently
  SELECT
    user_id,
    cohort_month,
    SUM(CASE WHEN days_since_signup BETWEEN 0 AND 29 THEN revenue ELSE 0 END) AS rev_0_29,  -- day0..29 => 30 days
    SUM(CASE WHEN days_since_signup BETWEEN 0 AND 59 THEN revenue ELSE 0 END) AS rev_0_59,  -- 60 days
    SUM(CASE WHEN days_since_signup BETWEEN 0 AND 89 THEN revenue ELSE 0 END) AS rev_0_89   -- 90 days
  FROM tx_with_meta
  WHERE days_since_signup BETWEEN 0 AND 89  -- limit scanned rows; transactions after 90d irrelevant
  GROUP BY user_id, cohort_month
),
-- ensure users with zero transactions in window are counted with zero LTV
cohort_user_base AS (
  SELECT u.user_id, u.cohort_month
  FROM user_signup u
)
SELECT
  c.cohort_month,
  -- median LTV per user in cohort at 30/60/90 days
  percentile_cont(0.5) WITHIN GROUP (ORDER BY COALESCE(p.rev_0_29, 0)) AS median_ltv_30d,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY COALESCE(p.rev_0_59, 0)) AS median_ltv_60d,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY COALESCE(p.rev_0_89, 0)) AS median_ltv_90d,
  COUNT(*) AS users_in_cohort
FROM cohort_user_base c
LEFT JOIN per_user_horizon p USING (user_id, cohort_month)
GROUP BY c.cohort_month
ORDER BY cohort_month;

Key points / reasoning:- Compute per-user cumulative revenue separately for each horizon to avoid double-counting across horizons (we do not sum 30d into 60d; each is independently computed as revenue within day ranges).- Using days_since_signup BETWEEN 0 and N-1 gives inclusive 30/60/90-day windows (adjust if you want inclusive of day 30).- Use COALESCE to include users with zero revenue in median computation.- percentile_cont(0.5) computes exact median; for very large datasets consider approx_percentile for performance.Edge cases: users with no tx in 90 days, timezone/date normalization, negative day diffs (future-dated rows) filtered out.

Unlock Full Question Bank

Get access to hundreds of Structured Query Language Fundamentals and Aggregation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.