SQL Query Support - Debug and Optimize Slow or Incorrect SQL Queries

Question

## Round 1 - SQL

## Problem

You are given the following schema and three SQL tasks. For each, either fix the bug or optimize the query.

```sql
CREATE TABLE users       (user_id INT, name VARCHAR, created_at DATE);
CREATE TABLE orders      (order_id INT, user_id INT, total DECIMAL, created_at DATE);
CREATE TABLE order_items (item_id INT, order_id INT, product_id INT, qty INT, price DECIMAL);
```

**Task 1 - Bug:** The following query is supposed to return users with no orders, but returns all users. Fix it.
```sql
SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.user_id != u.user_id;  -- BUG
```

**Task 2 - Optimization:** The following query is slow on 50M rows. Rewrite it.
```sql
SELECT user_id, SUM(total) FROM orders
WHERE YEAR(created_at) = 2024
GROUP BY user_id;
```

**Task 3 - Write from scratch:** Find the top 3 products by revenue in Q1 2024. Revenue = SUM(qty * price) per product.

## Follow-ups

1. For Task 1: why does `WHERE o.user_id IS NULL` correctly find users with no orders after a LEFT JOIN?
2. For Task 2: what index would you create and how does the rewrite avoid a full-table function scan?
3. In Task 3: how do you break ties in the top 3 (by product_id, name)?
4. How would you rewrite Task 3 using a window function instead of GROUP BY + LIMIT?

SQL Query Support - Debug and Optimize Slow or Incorrect SQL Queries

Question Details

Full Details

Topics

More from OpenAI