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.

EasyTechnical

24 practiced

You have tables customers(customer_id, name) and orders(order_id, customer_id, total). Write two SQL queries: (1) list all customers and their total order count including customers with zero orders; (2) list only customers who have at least one order. Explain which JOIN you used for each and why that choice matters for dashboard metrics.

Sample Answer

To get customer order counts, use aggregation with different joins depending on whether you want to include customers with zero orders.

1) All customers including zero orders — use LEFT JOIN so customers without matching orders are preserved:

sql

SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC;

Notes: COUNT(order_id) ignores NULLs so customers with no orders return 0.

2) Only customers with at least one order — use INNER JOIN (filters to customers that have orders):

sql

SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC;

Alternatively use EXISTS or GROUP BY on orders then join.

Why the JOIN choice matters for dashboard metrics:- LEFT JOIN gives a complete customer base view (essential for activation, churn, or conversion metrics) and prevents undercounting total customers.- INNER JOIN shows only active customers and is useful for engagement or revenue-per-active-customer metrics.Using the wrong join can skew KPIs (e.g., average orders per customer) and lead to wrong business decisions. Always pick join type based on the metric definition and document it in the dashboard.

EasyTechnical

20 practiced

Describe a safe, production-ready pattern to delete duplicate rows from a table duplicates(id serial primary key, user_id int, created_at timestamptz, data jsonb) keeping the earliest record per user. Provide the DELETE SQL and explain backup, transaction, and batching strategies.

Sample Answer

A safe, production-ready pattern is: 1) back up the affected rows, 2) run deletes inside a transaction (or multiple small transactions for large tables), 3) batch deletes to avoid long locks, and 4) verify results.

Example SQL (keeps earliest created_at per user, deletes others):

sql

-- Step 0: backup duplicates (before any delete)
CREATE TABLE duplicates_backup AS
SELECT * FROM duplicates WHERE user_id IN (SELECT user_id FROM duplicates);

-- Delete in batches to avoid long transactions/locks
WITH to_delete AS (
  SELECT d.id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at ASC, id ASC) AS rn
    FROM duplicates
  ) d
  WHERE d.rn > 1
  LIMIT 1000  -- batch size
)
DELETE FROM duplicates
WHERE id IN (SELECT id FROM to_delete);

Strategy and rationale:- Backup: snapshot rows (or whole table) so you can restore or audit. Consider pg_dump or COPY for large datasets.- Transaction & verification: run DELETE inside a transaction when batch size is small; verify counts with SELECT COUNT(*) beforehand and after; test in staging.- Batching: use LIMIT or loop until no rows remain to avoid long-running transactions and heavy WAL. Example: run the CTE DELETE repeatedly (in a script) until it deletes 0 rows.- Logging/audit: insert deleted rows into an audit table instead of or in addition to backup for traceability.- Edge cases: tie-breaker (id) for equal timestamps; consider foreign keys/constraints and disable triggers cautiously.

EasyTechnical

20 practiced

Refactor the following nested SQL into a readable CTE-based query and explain the readability benefits:

SELECT c.customer_id, SUM(o.total)FROM customers cJOIN ( SELECT order_id, customer_id, total FROM orders WHERE status = 'completed') o ON c.customer_id = o.customer_idGROUP BY c.customer_idHAVING SUM(o.total) > 1000;

Sample Answer

Approach: Pull the filtered orders in a named CTE, then aggregate by customer in the main query. CTEs make the logic modular and easier for BI consumers/maintainers to read and reuse.

sql

WITH completed_orders AS (
  -- isolate relevant rows and columns
  SELECT order_id, customer_id, total
  FROM orders
  WHERE status = 'completed'
)

SELECT
  c.customer_id,
  SUM(o.total) AS total_completed_amount
FROM customers c
JOIN completed_orders o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING SUM(o.total) > 1000;

Key points / readability benefits:- Named step (completed_orders) documents intent: we only care about completed orders.- Keeps the main query focused on business logic (join + aggregation) rather than filtering details.- Easier to test/debug: run the CTE alone to validate data.- Reusable: other metrics can reference the same CTE if extended.- For BI handoffs, non-SQL stakeholders or analysts can more quickly understand each logical block.

Edge cases to consider:- NULL totals (coalesce if needed)- Customer with no completed orders (excluded by inner join; use LEFT JOIN if you want zeros)

MediumTechnical

25 practiced

Write SQL that calculates, per product category, the monthly churn rate defined as the percentage of customers who bought in month N-1 but did not buy in month N. Tables: orders(order_id, customer_id, product_id, order_date), products(product_id, category_id). Provide a clear SQL approach and discuss edge cases.

Sample Answer

Approach:1. Build month-level cohorts (month = first day of month) of customers who purchased each category.2. For each category and month, count unique customers in month N-1 and how many of those did NOT purchase in month N.3. Compute churn% = (churned_customers / customers_prev_month) * 100. Handle months with zero previous customers.

SQL (Postgres / Redshift / Snowflake style; adjust DATE_TRUNC if needed):

sql

WITH orders_cat AS (
  SELECT
    o.customer_id,
    p.category_id,
    DATE_TRUNC('month', o.order_date)::date AS month
  FROM orders o
  JOIN products p USING (product_id)
  WHERE o.order_date IS NOT NULL
),
unique_month_customers AS (
  -- distinct customers per category-month
  SELECT DISTINCT customer_id, category_id, month
  FROM orders_cat
),
customers_by_month AS (
  SELECT
    category_id,
    month,
    COUNT(*) AS customers_prev_month  -- we'll treat this as "customers in that month" then shift
  FROM unique_month_customers
  GROUP BY category_id, month
),
prev_current AS (
  -- for each category-month (current = month), get customers in prev month and whether they appear in current
  SELECT
    curr.category_id,
    curr.month AS month,
    prev.customers_prev_month AS customers_prev_month,
    COUNT(prev_c.customer_id) FILTER (WHERE curr_c.customer_id IS NULL) AS churned_customers
  FROM
    -- months present as "current"
    (SELECT DISTINCT category_id, month FROM unique_month_customers) curr
  LEFT JOIN
    -- prev month counts
    customers_by_month prev
    ON prev.category_id = curr.category_id
    AND prev.month = curr.month - INTERVAL '1 month'
  -- expand prev-month customers to check membership in current month
  LEFT JOIN unique_month_customers prev_c
    ON prev_c.category_id = curr.category_id
    AND prev_c.month = prev.month
  LEFT JOIN unique_month_customers curr_c
    ON curr_c.category_id = curr.category_id
    AND curr_c.month = curr.month
    AND curr_c.customer_id = prev_c.customer_id
  GROUP BY curr.category_id, curr.month, prev.customers_prev_month
)
SELECT
  category_id,
  month,
  COALESCE(customers_prev_month, 0) AS customers_prev_month,
  COALESCE(churned_customers, 0) AS churned_customers,
  CASE
    WHEN customers_prev_month IS NULL OR customers_prev_month = 0 THEN NULL
    ELSE ROUND(100.0 * churned_customers / customers_prev_month, 2)
  END AS churn_pct
FROM prev_current
ORDER BY category_id, month;

Key points and reasoning:- Use DISTINCT customer per month to avoid double-counting repeat purchases.- Shift months by 1 to compare N-1 → N.- Return NULL for churn% where there were zero customers in previous month (avoids misleading 0%).- Round for presentation.

Edge cases:- First month in data has no prior month — report churn as NULL.- Customers buying multiple times in a month must be deduplicated.- Category changes over time (if product’s category_id changes historically) — join snapshot of products valid at order time or use historical product table.- Timezones and order_date granularity — normalize to UTC and use order_date timestamp -> date.- Returns/refunds: if churn should consider net purchases, exclude refunded orders or use a transactions table with net quantity.- Categories with sparse data: consider aggregating to weekly/quarterly for stability.

MediumTechnical

18 practiced

For each customer, return their last 3 orders as a comma-separated list of product names suitable for a dashboard widget. Tables: customers(customer_id), orders(order_id, customer_id, order_date), order_items(order_id, product_id), products(product_id, name). Write SQL using LATERAL (or APPLY) and string aggregation.

Sample Answer

Approach: use a LATERAL join to fetch each customer's most-recent 3 order rows (join orders → order_items), then aggregate product names into a comma-separated string with string_agg. This runs per-customer and is efficient because each lateral subquery is limited to 3 rows.

Assumption: we want the product names from the customer's most recent 3 order items (if an order has multiple items, those items count separately). If instead you want up to 3 most-recent orders and each order's products concatenated, see the note below.

Postgres example (LATERAL + string_agg):

sql

SELECT
  c.customer_id,
  COALESCE(lat.last_3_products, '') AS last_3_products
FROM customers c
LEFT JOIN LATERAL (
  SELECT string_agg(p.name, ', ' ORDER BY r.order_date DESC, r.order_id DESC) AS last_3_products
  FROM (
    -- pick the most recent 3 order_items (by order_date)
    SELECT o.order_id, oi.product_id, o.order_date
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC, o.order_id DESC
    LIMIT 3
  ) r
  JOIN products p ON p.product_id = r.product_id
) lat ON true;

Key points:- LATERAL lets the subquery reference c.customer_id.- LIMIT 3 enforces top-N per customer.- string_agg(... ORDER BY ...) preserves recency ordering in the comma list.

Edge cases and notes:- Customers with <3 order items return fewer names (empty string via COALESCE).- If you want the last 3 distinct orders and for each order list its products (e.g., "OrderA: a,b; OrderB: c; OrderC: d"), first aggregate products per order, then lateral-select the top 3 orders and aggregate those order-level strings.- SQL Server uses CROSS APPLY and either STRING_AGG (SQL Server 2017+) combined with a nested TOP 3 subquery, or FOR XML PATH for older versions.

Performance tips:- Ensure indexes on orders(customer_id, order_date) and order_items(order_id) for fast top-N retrieval.

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.