Joins Level 2 Problems

Level 1 covered each join type on its own. Level 2 mixes joins with other tools you already know: aggregate functions (COUNT, AVG, SUM, MIN), arithmetic, and join conditions that go beyond a simple "column equals column" match.

Problems in this set

Quick reminders from the concept notes before you start

• INNER JOIN keeps only rows that have a match on both sides.
• A self join uses two aliases on the same table, so one table can act like two.
• The ON clause can hold more than one condition, and the conditions do not have to be equality checks.
• A NULL join key never matches anything, because NULL = anything is UNKNOWN, not TRUE.

Problem 1: Sales Analysis

Problem Statement

A company tracks product sales over different years. Each sales record stores the product ID, the year of the sale, the quantity sold, and the price per unit. The sales table only holds product IDs, not product names, so a report built directly from it is hard to read.

Business context Nobody wants to read a report that says "product 101 sold for 5000 in 2008." People want to see "Sony sold for 5000 in 2008." Product names live in a separate table, so the two tables must be combined before the report makes sense.

Goal For each sale, show the product name, the year of the sale, and the price per unit.

Input Two tables: Sales (one row per sale) and Product (one row per product).

Output product_name, year, and price for every sale. The result can be in any order.

Table Structure

Table: Sales

Table: Product

Column Meaning

• sale_id — a unique ID for each sale transaction
• product_id (Sales) — the ID of the product that was sold; it points to a row in the Product table (a foreign key)
• year — the year in which the sale happened
• quantity — how many units were sold in that sale
• price — the price of one unit at the time of the sale
• product_id (Product) — the unique ID of each product, and the primary key of Product
• product_name — the name of the product

Important Notes

• The primary key of Sales is the pair (sale_id, year). The primary key of Product is product_id.
• The same product can appear in many sales rows (for example, Sony sold in both 2008 and 2009). This is a one-to-many relationship from Product to Sales.
• A product that has never been sold (Dell in the example) has no row in Sales, so it will not appear in the result.
• The report asks for the price per unit, not the total amount. quantity is not needed for this question.
• Both tables have a column called product_id, so every reference to it in the query must use a table alias prefix (s.product_id or p.product_id). Without the prefix, MySQL reports an "ambiguous column" error.

Example

Input: Sales

Input: Product

Expected Output

CREATE TABLE Sales (sale_id INTEGER, product_id INTEGER, year INTEGER, quantity INTEGER, price INTEGER);
INSERT INTO Sales VALUES
  (1, 101, 2008, 10, 5000),
  (2, 101, 2009, 12, 5000),
  (3, 202, 2011, 15, 9000);
CREATE TABLE Product (product_id INTEGER, product_name TEXT);
INSERT INTO Product VALUES
  (101, 'Sony'),
  (202, 'Lenovo'),
  (303, 'Dell');

Explanation

• Sony (product_id 101) sold for 5000 in 2008 and again for 5000 in 2009, so it appears twice.
• Lenovo (product_id 202) sold for 9000 in 2011.
• Dell (product_id 303) has no matching sale in the Sales table, so it is left out.

Extra Example (to see how the result changes)

Suppose the Sales table is different:

Using the same Product table, the output is:

Sony shows up again with a different price, because it is a different sale. Dell now appears because it finally has a sale. Lenovo is missing this time because, in this data, it has no sale. A product shows up in the result only when at least one sale row points to it.

Intuition

The Sales table is like a stack of receipts where each receipt only shows a product code, not a product name. The Product table is the code book that says which code belongs to which name. To turn the receipts into a readable report, you look up every receipt's code in the code book and write the name next to it.

Since the goal is a report of sales, only products that actually have sales matter. A product with no sales has nothing to report, so it should not appear.

Mental Model

Think of a coat check. Each ticket (a sale) has a number on it (product_id). The attendant has a board (the Product table) that says "number 101 is a Sony jacket, number 202 is a Lenovo jacket." For every ticket handed in, the attendant finds the same number on the board and reads out the name. A jacket hanging on the board that nobody has a ticket for is simply never mentioned.

That is exactly what an INNER JOIN does: match by number, and skip anything without a partner.

Thought Process

1. Where does each needed column live? product_name is in Product. year and price are in Sales. Two tables are needed.
2. What links the two tables? product_id, which exists in both.
3. Should products without sales appear? No. The report is about sales, so a product with no sales has nothing to show.
4. Which join keeps only matching rows? INNER JOIN.
5. Do I need any filtering, grouping, or sorting? No. The problem asks for one output row per sale, and any order is accepted.

Approach

Step 1: Start from the Sales table Every output row represents one sale, so Sales is the natural starting point.

Step 2: INNER JOIN the Product table on product_id Match each sale to its product using s.product_id = p.product_id.

Step 3: Select the required columns Take product_name from Product, and year and price from Sales.

SQL Query

SELECT 
    p.product_name,   -- readable name from the Product table
    s.year,           -- year of the sale, from the Sales table
    s.price           -- price per unit, from the Sales table
FROM Sales s
-- INNER JOIN keeps only sales that have a matching product
INNER JOIN Product p 
    ON s.product_id = p.product_id;

Query Explanation

• SELECT p.product_name, s.year, s.price — picks one column from Product and two from Sales. The alias before the dot tells SQL which table each column comes from.
• FROM Sales s — the starting table. The alias s is a short nickname so the rest of the query is easier to read.
• INNER JOIN Product p — brings in the Product table with the nickname p. INNER JOIN drops any row that has no partner on the other side.
• ON s.product_id = p.product_id — the matching rule. A sale and a product are combined only when their product IDs are equal.

There is no WHERE, GROUP BY, or ORDER BY in this query, because none of them are needed.

Dry Run

Original tables

Sales:

Product:

↓

Step 1: For each sale, look for a product with the same product_id

↓

Step 2: Check the product that nobody pointed to

↓

Final output

SQL Execution Flow

FROM Sales s
   ↓
INNER JOIN Product p ON s.product_id = p.product_id
   ↓
Keep only sales that found a matching product
   ↓
SELECT product_name, year, price
   ↓
Return the result (any order)

Time Complexity

Time Complexity: O(n) when Product.product_id is indexed (it is the primary key, so it is) Each sale needs one quick lookup in Product. Without an index, each sale might scan the whole Product table, which would grow toward O(n × m).

Space Complexity: O(1) extra space beyond the output The database reads rows one by one and outputs matches. No sorting or grouping structures are needed.

Why: Because the join column is a primary key on one side, each sale matches at most one product. The output can never be larger than the Sales table.

Common Mistakes to Avoid

Mistake 1: Starting from Product and using a LEFT JOIN

Why it happens: It feels natural to "start with the names."

Wrong:

SELECT p.product_name, s.year, s.price
FROM Product p
LEFT JOIN Sales s ON s.product_id = p.product_id;

Dell would appear with year = NULL and price = NULL, which is not a sale at all.

Correct: Use INNER JOIN, so products with no sales do not appear.

Mistake 2: Not using a table prefix on product_id

Why it happens: The column has the same name in both tables, and it is easy to forget that SQL cannot guess which one you mean.

Wrong:

SELECT p.product_name, s.year, s.price
FROM Sales s
INNER JOIN Product p ON product_id = product_id;

MySQL raises an "ambiguous column" error.

Correct: ON s.product_id = p.product_id.

Mistake 3: Forgetting the ON clause

Why it happens: Some beginners think INNER JOIN alone is enough to connect the tables.

Wrong:

SELECT p.product_name, s.year, s.price
FROM Sales s
INNER JOIN Product p;

MySQL accepts this without an error and treats it like a CROSS JOIN, so every sale gets paired with every product. With 3 sales and 3 products, that is 9 rows instead of 3.

Correct: Always write the ON condition.

Mistake 4: Returning the total amount instead of the price per unit

Why it happens: "Sales report" makes people think of revenue.

Wrong:

SELECT p.product_name, s.year, s.quantity * s.price AS price
FROM Sales s
INNER JOIN Product p ON s.product_id = p.product_id;

The problem asks for the price of one unit. Multiplying by quantity returns a different number (for sale 1, 50000 instead of 5000).

Correct: Return s.price exactly as stored.

Mistake 5: Using DISTINCT "to be safe"

Why it happens: DISTINCT feels like a harmless clean-up step.

Wrong:

SELECT DISTINCT p.product_name, s.year, s.price
FROM Sales s
INNER JOIN Product p ON s.product_id = p.product_id;

If two different sales of the same product happen in the same year at the same price, DISTINCT silently merges them into one row. The report should list every sale.

Correct: Leave DISTINCT out unless the problem asks for unique rows.

Interview Follow-up Questions

• "How would you find the total revenue (quantity times price) for each product in each year?"
• "How would you list products that have never been sold?" (Hint: LEFT JOIN from Product, then filter on a NULL from Sales.)
• "What would happen to the result if Sales contained a product_id that does not exist in Product?"
• "How would you show only the most recent year's sale for each product?"
• "Why is the join fast here? What role does the primary key on Product play?"

Similar Problems

• The INNER JOIN scenarios in the Day 28 concept notes (orders count and revenue per customer)
• Level 1, Problem 3 (Employees With Confirmed Salary Records), which also keeps only matched rows
• Any report that swaps a code or ID for a readable name using a lookup table

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• One table stores an ID and another table stores the name for that ID → join on the ID
• The report is about events (sales, orders, logins) and rows without events should not appear → INNER JOIN starting from the event table
• Both tables share a column name → always use alias prefixes on that column

Problem 2: Minimum Distance Between Points

Problem Statement

You are building a mapping or sensor system that tracks positions along a straight line, like the X-axis. To avoid collisions or to optimize paths, the system needs to know how close the two nearest points are.

Business context Think of robots moving along a rail, or sensors placed along a pipeline. If two of them get too close, something can go wrong. Finding the smallest gap between any two positions is a common safety and optimization check.

Goal Find the shortest distance between any two points in the table.

Input A table with one column, x, holding the position of each point.

Output A single number called shortest, the smallest distance between any two points.

Table Structure

Table: Point

Column Meaning

• x — the X-coordinate of a point on the X-axis. It is the primary key, so every value is unique.

Important Notes

• Because x is unique, two different rows never have the same position. So the distance between two different points is never 0.
• The distance between two points on a line is the absolute difference of their x values: |a - b|.
• Positions can be negative (the example has -1).
• If the table has only one point, there are no pairs to compare. The query returns NULL in that case, because MIN over zero rows is NULL.
• The table is checked for every possible pair of points, not only points that sit next to each other in the table.

Example

Input: Point

Expected Output

CREATE TABLE Point (x INTEGER);
INSERT INTO Point VALUES
  (-1),
  (0),
  (2);

Explanation

• Distance between -1 and 0 is |(-1) - 0| = 1
• Distance between -1 and 2 is |(-1) - 2| = 3
• Distance between 0 and 2 is |0 - 2| = 2
• The smallest of these is 1.

Extra Example 1 (a bigger table)

Pairs and distances: (1,5)=4, (1,6)=5, (1,20)=19, (5,6)=1, (5,20)=15, (6,20)=14. The smallest is 1.

Extra Example 2 (only one point)

There is no second point to compare with, so the result is NULL.

Intuition

To find the two closest points, the simplest honest method is to measure every possible pair and keep the smallest measurement. That means pairing each point with every other point.

Two details make this clean:

• A point should never be measured against itself (that would give 0 and ruin the answer).
• Measuring A to B and B to A is the same measurement. Doing both is wasted work.

A single condition solves both: only pair up rows where the first x is smaller than the second x. Each pair then appears exactly once, and no point pairs with itself.

Mental Model

Imagine a row of fence posts in a field, and you want to know which two posts are closest. You take a measuring tape, and starting from the leftmost post, you measure the distance to every post on its right. Then you move to the second post and measure to every post on its right, and so on. You never measure backwards (that would repeat an old measurement), and you never measure a post to itself.

The rule "measure only to posts on my right" is exactly a.x < b.x.

Thought Process

1. What do I need to compare? Every pair of points. That means the table must be joined to itself.
2. How do I avoid pairing a point with itself, and avoid repeated pairs? Use a.x < b.x as the join condition.
3. How do I turn a pair into a distance? ABS(a.x - b.x).
4. How do I pick the smallest distance from all pairs? Wrap the distance in MIN().
5. Do I need GROUP BY? No. I want one answer for the whole table, so a plain aggregate is enough.

Approach

Step 1: Self join the Point table Give the table two aliases, a and b, so SQL treats them as two copies.

Step 2: Use a.x < b.x as the join condition This creates each unique pair exactly once and removes self-pairs.

Step 3: Calculate each pair's distance Use ABS(a.x - b.x).

Step 4: Take the smallest distance Apply MIN() over all the calculated distances.

SQL Query

SELECT 
    -- Smallest absolute gap among all unique pairs of points
    MIN(ABS(a.x - b.x)) AS shortest
FROM Point a
JOIN Point b
    -- a.x < b.x means: no point is paired with itself,
    -- and each pair (A,B) appears only once, never as (B,A)
    ON a.x < b.x;

Query Explanation

• FROM Point a JOIN Point b — uses the same table twice with two different aliases. This is the self join pattern from the concept notes.
• ON a.x < b.x — this is a non-equality join condition. It is allowed: a join condition does not have to be =. It keeps only pairs where the left value is smaller than the right value.
• ABS(a.x - b.x) — turns the pair into a distance. Because a.x < b.x, the difference a.x - b.x is always negative, and ABS makes it positive. (Using b.x - a.x would give the same result without ABS. ABS is kept because it makes the intention obvious.)
• MIN(...) — an aggregate function. It collapses all the pair distances into one smallest value.
• AS shortest — names the output column as the problem requires.

Dry Run

Original table

↓

Step 1: Build all pairs where a.x < b.x

↓

Step 2: Calculate distance for the kept pairs

↓

Step 3: MIN of the distances

MIN(1, 3, 2) = 1

↓

Final output

SQL Execution Flow

FROM Point a
   ↓
JOIN Point b ON a.x < b.x   (builds each unique pair once)
   ↓
Compute ABS(a.x - b.x) for every pair
   ↓
Aggregate: MIN(...) over all pairs
   ↓
SELECT the single result as "shortest"

Time Complexity

Time Complexity: O(n²) With n points, there are n × (n − 1) / 2 unique pairs, and every pair is measured once.

Space Complexity: O(1) extra space The database only needs to remember the smallest distance seen so far, not every pair.

Why: This method checks all pairs, which is fine for small tables. For very large tables, a smarter approach sorts the points and compares only neighbours, because the closest pair on a line is always two neighbours after sorting. That approach is covered later with window functions.

Common Mistakes to Avoid

Mistake 1: Using <= or no condition at all

Why it happens: It feels natural to say "join the table to itself" without thinking about self-pairs.

Wrong:

SELECT MIN(ABS(a.x - b.x)) AS shortest
FROM Point a
JOIN Point b ON a.x <= b.x;

With <=, every point pairs with itself, giving a distance of 0. The answer becomes 0 every time, which is wrong.

Correct: Use strict <.

Mistake 2: Using != and forgetting ABS

Why it happens: != sounds like "different points," which seems right.

Wrong:

SELECT MIN(a.x - b.x) AS shortest
FROM Point a
JOIN Point b ON a.x != b.x;

Now pairs appear in both directions, so some differences are negative. MIN picks the most negative number, which is not a distance at all.

Correct: Either keep a.x < b.x, or wrap the difference in ABS.

Mistake 3: Using != with ABS and thinking it is the best answer

Why it happens: It gives the right answer, so it looks fine.

Less efficient:

SELECT MIN(ABS(a.x - b.x)) AS shortest
FROM Point a
JOIN Point b ON a.x != b.x;

This is correct, but it builds every pair twice (A to B and B to A), doing double the work.

Correct: a.x < b.x builds each pair once.

Mistake 4: Taking MIN(a.x - b.x) even with a.x < b.x

Why it happens: The ABS seems unnecessary, so it gets removed, but the subtraction is still in the wrong order.

Wrong:

SELECT MIN(a.x - b.x) AS shortest
FROM Point a
JOIN Point b ON a.x < b.x;

Here a.x - b.x is always negative, so MIN returns the largest gap as a negative number. For the example it returns -3.

Correct: Use ABS(a.x - b.x) or b.x - a.x.

Mistake 5: Assuming the closest points are always consecutive integers

Why it happens: A quick look at a tiny example like -1, 0, 2 suggests neighbours differ by 1.

Wrong idea: join on b.x = a.x + 1.

Why it fails: In the extra example (1, 5, 6, 20) the answer comes from 5 and 6, but in another table like (1, 10, 25) no two points differ by 1. The join would return nothing and MIN would be NULL.

Correct: Compare all pairs, as in the main query.

Interview Follow-up Questions

• "How would you also return the two points that are closest to each other?"
• "This query checks all pairs. How would you make it faster on a table with millions of rows?" (Hint: sort the values and compare each one with the next.)
• "What does the query return if the table has only one row? Why?"
• "What would change if x were not unique, and two rows could have the same value?"
• "How would you find the largest distance between any two points?" (Hint: you do not need a join at all. Think MAX(x) − MIN(x).)

Similar Problems

• Level 1, Problem 7 (Employees Earning More Than Their Manager), another self join with an extra condition in ON
• The SELF JOIN section in the Day 28 concept notes
• Any "compare every pair of rows once" problem, like finding duplicate-looking records or the closest two timestamps

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to compare every row with every other row in the same table → self join
• Pairs should not repeat and a row should not pair with itself → use a.col < b.col in ON
• Need the smallest or largest value across all pairs → wrap the pair calculation in MIN() or MAX()
• Need a distance between two numbers → ABS(a − b)

Problem 3: Suspended Accounts

Problem Statement

A company wants to catch fraud by banning accounts that were active from two different IP addresses at the same time. This pattern often means an account is being shared or has been hacked.

Business context A normal person cannot usually be logged in from two different locations at the same moment. If the login logs show overlapping sessions from different IP addresses for the same account, it is a strong warning sign.

Goal Find every account that has been logged in from two different IP addresses at the same moment. Touching at an exact boundary time also counts as being at the same moment.

Input A log table with one row per login session, showing the account, the IP address, and the login and logout times.

Output The account_id of every account that should be banned. The result can be in any order.

Table Structure

Table: LogInfo

Column Meaning

• account_id — the account that logged in
• ip_address — the IP address the session came from
• login — when the session started
• logout — when the session ended

Important Notes

• The table may contain duplicate rows (same account, IP, login, and logout repeated). Duplicates on the same IP must not cause a ban, because a ban needs two different IPs.
• For every row, logout is always later than login, so each row is a valid time range.
• Two sessions overlap if they share at least one moment. Touching at exactly the same second counts. In the example, Account 4 logs out from IP 10 at 17:00:00 and logs in from IP 11 at 17:00:00, and that is an overlap.
• Sessions on different days can never overlap. Account 2 uses two IPs, but on different days.
• Using two different IPs at different times is not enough for a ban. The sessions must overlap. Account 3 shows this.
• The BETWEEN operator is inclusive, meaning it includes both end values. This is exactly what is needed to treat boundary-touching as an overlap.

Example

Input: LogInfo

Expected Output

CREATE TABLE LogInfo (account_id INTEGER, ip_address INTEGER, login TEXT, logout TEXT);
INSERT INTO LogInfo VALUES
  (1, 1, '2021-02-01 09:00:00', '2021-02-01 09:30:00'),
  (1, 2, '2021-02-01 08:00:00', '2021-02-01 11:30:00'),
  (2, 6, '2021-02-01 20:30:00', '2021-02-01 22:00:00'),
  (2, 7, '2021-02-02 20:30:00', '2021-02-02 22:00:00'),
  (3, 9, '2021-02-01 16:00:00', '2021-02-01 16:59:59'),
  (3, 13, '2021-02-01 17:00:00', '2021-02-01 17:59:59'),
  (4, 10, '2021-02-01 16:00:00', '2021-02-01 17:00:00'),
  (4, 11, '2021-02-01 17:00:00', '2021-02-01 17:59:59');

Explanation

• Account 1: IP 1 (09:00 to 09:30) sits fully inside IP 2 (08:00 to 11:30). They overlap, so ban.
• Account 2: two different IPs, but on different days. No overlap, so no ban.
• Account 3: two different IPs, but IP 9 ends at 16:59:59 and IP 13 starts at 17:00:00. There is a one-second gap, so no overlap and no ban.
• Account 4: IP 10 ends at 17:00:00 and IP 11 starts at exactly 17:00:00. They touch at one moment, which counts as an overlap, so ban.

Extra Example (more situations)

Output:

• Account 5: the IP 21 session is completely inside the IP 20 session. Overlap with different IPs, so ban.
• Account 6: the sessions overlap, but both are from the same IP (30). Not two different IPs, so no ban.
• Account 7: an exact duplicate row from the same IP. Same IP, so no ban.

Intuition

Imagine each account has a personal calendar, and every login session is an appointment with a start time and an end time. The appointment also has a "location," which is the IP address. The fraud rule is: the same person cannot be at two different locations at the same time.

So for each account, look at every pair of its appointments. If the two locations are different and the two time ranges overlap, the account gets flagged.

Mental Model

Picture two coloured bars drawn on a timeline, one bar per session. If the two bars share even a single point on the timeline, they overlap.

Account 1:
IP 2:  |------------------------------|      08:00 to 11:30
IP 1:          |------|                      09:00 to 09:30   (inside IP 2, overlap)

Account 4:
IP 10: |----------------|                    16:00 to 17:00
IP 11:                  |----------------|   17:00 to 17:59:59   (touch at 17:00, overlap)

Account 3:
IP 9:  |----------------|                    16:00 to 16:59:59
IP 13:                    |--------------|   17:00 to 17:59:59   (gap, no overlap)

To detect an overlap in SQL, check whether the start of one bar falls inside the other bar.

Thought Process

1. What am I comparing? Sessions against other sessions, of the same account. That is a self join on account_id.
2. What must differ? The IP address: l1.ip_address != l2.ip_address.
3. How do I test if two time ranges overlap? If one session's start (or end) lies inside the other session's range, they overlap. BETWEEN includes the end values, which makes boundary-touching count.
4. Why is checking "start inside the other" enough? The self join examines every pair in both directions (A against B, and B against A). Whichever session starts later will have its start inside the earlier one, so one of the two directions always catches the overlap.
5. What about the same account showing up many times? One ban is enough, so use DISTINCT on account_id.

Approach

Step 1: Self join LogInfo on account_id Pair up sessions that belong to the same account.

Step 2: Require different IP addresses Add l1.ip_address != l2.ip_address to the join condition.

Step 3: Add the overlap test Check whether l1.login or l1.logout falls between l2.login and l2.logout.

Step 4: Return each account once Use SELECT DISTINCT l1.account_id.

SQL Query

SELECT DISTINCT l1.account_id        -- DISTINCT so each account is listed only once
FROM LogInfo l1
JOIN LogInfo l2
    -- Only compare sessions belonging to the same account
    ON l1.account_id = l2.account_id
    -- ...that came from two different IP addresses
    AND l1.ip_address != l2.ip_address
    -- ...and whose time ranges overlap.
    -- BETWEEN includes both end values, so touching at one exact
    -- second (like 17:00:00) counts as an overlap.
    AND (
        (l1.login  BETWEEN l2.login AND l2.logout)
        OR
        (l1.logout BETWEEN l2.login AND l2.logout)
    );

Query Explanation

• FROM LogInfo l1 JOIN LogInfo l2 — the same table used twice. l1 is "session one," l2 is "session two."
• ON l1.account_id = l2.account_id — stops sessions from different accounts from being compared with each other.
• AND l1.ip_address != l2.ip_address — this does two jobs. It enforces "two different IPs," and it also prevents a session from matching itself or its exact duplicate (same IP), which would otherwise always look like an overlap.
• l1.login BETWEEN l2.login AND l2.logout — is session one's start time inside session two's range?
• OR l1.logout BETWEEN l2.login AND l2.logout — is session one's end time inside session two's range?
• SELECT DISTINCT l1.account_id — if an account has several overlapping pairs (or the same pair is found in both directions), DISTINCT makes sure the account is returned only once.

Dry Run

Original table

(Account 1, 3, and 4 sessions are all on Feb 1, 2021.)

↓

Step 1: Pair sessions of the same account with different IPs

Each account has 2 sessions with different IPs, so each account produces 2 pairs (one in each direction).

↓

Step 2: Test the overlap for each pair

Notice that Account 1 is caught in only one of the two directions. That is enough, and it is the reason both directions must be examined.

↓

Step 3: DISTINCT account_id from matching pairs

Matching pairs belong to accounts 1, 4, and 4 again. DISTINCT reduces this to 1 and 4.

↓

Final output

SQL Execution Flow

FROM LogInfo l1
   ↓
JOIN LogInfo l2 ON same account AND different IP AND time ranges overlap
   ↓
Only overlapping, different-IP session pairs remain
   ↓
SELECT DISTINCT l1.account_id
   ↓
Return each suspicious account once

Time Complexity

Time Complexity: O(n²) in the worst case, but closer to O(Σ kᵢ²) in practice Sessions are only compared within the same account. If an account has k sessions, that account creates about k × k comparisons. With an index on account_id, the database does not need to scan the whole table for each row.

Space Complexity: O(a), where a is the number of accounts returned DISTINCT needs to remember which account IDs it has already output.

Why: Most accounts have only a few sessions, so the real cost is far below comparing every row with every other row. A few accounts with thousands of sessions would dominate the cost.

Common Mistakes to Avoid

Mistake 1: Using strict < and > instead of inclusive checks

Why it happens: "Overlap" sounds like "strictly inside."

Wrong:

AND (l1.login > l2.login AND l1.login < l2.logout)

Account 4's sessions touch at exactly 17:00:00. Strict comparisons miss that, and Account 4 would wrongly escape a ban.

Correct: Use BETWEEN (inclusive), or <= and >=.

Mistake 2: Forgetting the different-IP condition

Why it happens: The overlap test feels like the main idea, so the IP check gets dropped.

Wrong:

SELECT DISTINCT l1.account_id
FROM LogInfo l1
JOIN LogInfo l2
    ON l1.account_id = l2.account_id
    AND (l1.login BETWEEN l2.login AND l2.logout);

Every row overlaps with itself, so every account would be banned.

Correct: Add AND l1.ip_address != l2.ip_address.

Mistake 3: Forgetting DISTINCT

Why it happens: The output looks right on a small example.

Wrong:

SELECT l1.account_id
FROM LogInfo l1
JOIN LogInfo l2 ON ... ;

Account 4 matches in both directions, so it appears twice in the output. With more sessions, the repeat count grows.

Correct: SELECT DISTINCT l1.account_id.

Mistake 4: Joining without the account_id condition

Why it happens: Forgetting that sessions from different accounts should never be compared.

Wrong:

JOIN LogInfo l2
    ON l1.ip_address != l2.ip_address
    AND (l1.login BETWEEN l2.login AND l2.logout)

Now one account's session can "overlap" with a different account's session, and innocent accounts get banned.

Correct: Always include l1.account_id = l2.account_id.

Mistake 5: Comparing only the time of day, not the full date and time

Why it happens: Trying to simplify the datetime values.

Wrong idea: compare TIME(l1.login) with TIME(l2.login).

Why it fails: Account 2's sessions are on different days but at the same clock time (20:30 to 22:00). Comparing only the time part would see a perfect overlap and ban an innocent account.

Correct: Compare the full datetime values.

Mistake 6: Banning any account that used two IPs

Why it happens: "Multiple IP addresses" sounds like the whole rule.

Why it fails: Accounts 2 and 3 both used two IPs, but never at the same moment. The rule is about simultaneous use, so the overlap check is required.

Correct: Keep both the different-IP check and the time overlap check.

Interview Follow-up Questions

• "There is a shorter way to test overlap: l1.login <= l2.logout AND l2.login <= l1.logout. Why does this work?"
• "How would you return the two overlapping sessions for each banned account, not only the account ID?"
• "What if a session is still open, so its logout is NULL? How would you handle that?"
• "What index would speed this query up the most?" (Hint: think account_id, then login.)
• "How would you detect an account logged in from three or more IPs at once?"

Similar Problems

• Level 1, Problem 7 (Employees Earning More Than Their Manager), a self join with extra conditions inside ON
• Level 2, Problem 2 (Minimum Distance Between Points), a self join with a non-equality condition
• Hotel booking conflicts, meeting-room double bookings, and shift scheduling overlaps

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to find rows of the same entity that conflict in time (double bookings, simultaneous logins, overlapping shifts) → self join on the entity, then an overlap condition
• Overlap test for two ranges → start1 <= end2 AND start2 <= end1, or the BETWEEN form used here
• Boundary touching counts as a conflict → use inclusive comparisons
• Same entity can match many times but should be listed once → DISTINCT

Problem 4: Find Team Size for Each Employee

Problem Statement

A company stores which team each employee belongs to. For every employee, the company wants to know how big their team is.

Business context Team size matters for planning: workload balance, meeting sizes, and manager span of control. Having the team size next to each employee makes it easy to spot very large or very small teams.

Goal For each employee, return their employee_id and the number of employees in their team (including themselves).

Input An Employee table with one row per employee and the team they belong to.

Output employee_id and team_size, sorted by employee_id in ascending order.

Table Structure

Table: Employee

Column Meaning

• employee_id — the unique ID of an employee, and the primary key
• team_id — the ID of the team the employee belongs to

Important Notes

• Team size includes the employee themselves. An employee alone in a team has a team size of 1, not 0.
• Employees who share a team_id are on the same team.
• The result must be sorted by employee_id ascending.
• If team_id could be NULL, that employee would never match anything in the join (NULL never equals NULL), and with an INNER JOIN they would be missing from the output. This problem does not include NULL teams, but it is a good edge case to know about.

Example

Input: Employee

Expected Output

CREATE TABLE Employee (employee_id INTEGER, team_id INTEGER);
INSERT INTO Employee VALUES
  (1, 8),
  (2, 8),
  (3, 8),
  (4, 7),
  (5, 9),
  (6, 9);

Explanation

• Employees 1, 2, 3 are all in team 8, so each one has a team size of 3.
• Employee 4 is alone in team 7, so the team size is 1.
• Employees 5 and 6 are in team 9, so each has a team size of 2.

Extra Example

Output:

Employees 10 and 14 are each alone in their team, so their size is 1.

Intuition

To know how big your team is, you count how many people have the same team_id as you. The table has everything needed, but each row only knows about itself. To let each employee "see" their teammates, join the table to itself on team_id. Every employee is then matched with all members of their own team, including themselves, and counting those matches gives the team size.

Mental Model

Picture a classroom roll call. Every student wears a coloured badge showing their team. The teacher says: "Everyone, count how many people in the room wear the same colour as you, including yourself." Each student turns around, counts, and reports a number.

The self join is the "turning around and looking at everyone else." The COUNT is the number each student reports.

Thought Process

1. Where does the team size come from? From counting rows that share my team_id.
2. How can one row see other rows? A self join on team_id.
3. Should I exclude myself from the count? No. Team size includes me. So the join must allow a row to match itself.
4. What do I group by? Each employee, because the answer is per employee (not per team).
5. What do I count? The matched teammate rows, COUNT(e2.employee_id).
6. Sorting? ORDER BY e.employee_id.

Approach

Step 1: Self join Employee on team_id e is "the employee," e2 is "a teammate."

Step 2: Count matches per employee For each row of e, the number of matching e2 rows is their team size.

Step 3: Group by employee GROUP BY e.employee_id, so there is one output row per employee.

Step 4: Sort the result ORDER BY e.employee_id.

SQL Query

SELECT 
    e.employee_id,
    -- Counts every employee (including e itself) who shares the same team_id
    COUNT(e2.employee_id) AS team_size
FROM Employee e
-- Self join: pair each employee with every member of their own team
JOIN Employee e2 
    ON e.team_id = e2.team_id
-- One output row per employee
GROUP BY e.employee_id
-- Required sorted output
ORDER BY e.employee_id;

Query Explanation

• FROM Employee e JOIN Employee e2 ON e.team_id = e2.team_id — pairs each employee with every employee on the same team. Since the condition is only about team_id, an employee also matches themselves, which is exactly what makes the count include them.
• COUNT(e2.employee_id) — counts how many teammate rows were matched for the current employee.
• GROUP BY e.employee_id — collapses the matched rows back to one row per employee, so the COUNT is calculated per employee.
• ORDER BY e.employee_id — sorts the final result in ascending order, as required.

Dry Run

Original table

↓

Step 1: Self join on team_id (each employee e paired with every e2 on the same team)

That is 3 + 3 + 3 + 1 + 2 + 2 = 14 joined rows in total.

↓

Step 2: GROUP BY e.employee_id and COUNT the matches

↓

Step 3: ORDER BY employee_id (already in order)

Final output

SQL Execution Flow

FROM Employee e
   ↓
JOIN Employee e2 ON e.team_id = e2.team_id
   ↓
GROUP BY e.employee_id
   ↓
COUNT(e2.employee_id) for each group
   ↓
SELECT employee_id, team_size
   ↓
ORDER BY employee_id

Time Complexity

Time Complexity: O(Σ kᵗ²), where kᵗ is the size of team t A team with k members produces k × k joined rows. If everyone is on one giant team of n people, this becomes O(n²). The grouping and sorting afterwards add O(n log n).

Space Complexity: O(n) The grouped result holds one count per employee.

Why: The self join creates more rows than the original table, since each team of size k produces k² rows. For small and medium teams this is fine. A faster approach counts each team once (GROUP BY team_id) and then joins that small result back, which avoids the k² blow-up.

Common Mistakes to Avoid

Mistake 1: Excluding the employee themselves from the join

Why it happens: "Teammates" sounds like "other people," so e.employee_id != e2.employee_id feels natural.

Wrong:

SELECT e.employee_id, COUNT(e2.employee_id) AS team_size
FROM Employee e
JOIN Employee e2 
    ON e.team_id = e2.team_id AND e.employee_id != e2.employee_id
GROUP BY e.employee_id;

Every team size is off by one, and employee 4 (alone in a team) disappears from the result entirely, because they have no teammate to match.

Correct: Let the employee match themselves, since team size includes them.

Mistake 2: Copying the < trick from Problem 2

Why it happens: Problem 2 used a.x < b.x to avoid self-pairs, so the habit carries over.

Wrong:

JOIN Employee e2 ON e.team_id = e2.team_id AND e.employee_id < e2.employee_id

This counts only the teammates with a bigger ID, giving different, meaningless numbers for each employee.

Correct: The pairing rule depends on the question. Problem 2 needed unique pairs. This problem needs a full count per person.

Mistake 3: Grouping by team_id instead of employee_id

Why it happens: Team size feels like a team-level fact.

Wrong:

SELECT e.team_id, COUNT(*) AS team_size
FROM Employee e
JOIN Employee e2 ON e.team_id = e2.team_id
GROUP BY e.team_id;

This returns one row per team, not per employee, and the counts are also inflated (k × k instead of k).

Correct: GROUP BY e.employee_id, since the answer is required for every employee.

Mistake 4: Forgetting GROUP BY

Why it happens: The join feels like enough.

Wrong:

SELECT e.employee_id, COUNT(e2.employee_id) AS team_size
FROM Employee e
JOIN Employee e2 ON e.team_id = e2.team_id;

Mixing a plain column with an aggregate and no GROUP BY is an error in strict mode, or collapses everything into one meaningless row in loose mode.

Correct: Add GROUP BY e.employee_id.

Mistake 5: Forgetting ORDER BY

Why it happens: The rows often happen to come out in order on small examples.

Why it matters: Without ORDER BY, row order is not guaranteed. The problem explicitly asks for ascending employee_id.

Correct: End with ORDER BY e.employee_id.

Mistake 6: Using COUNT(*) after switching to a LEFT JOIN

Why it happens: Someone tries to protect against employees with no team match.

Risky:

SELECT e.employee_id, COUNT(*) AS team_size
FROM Employee e
LEFT JOIN Employee e2 ON e.team_id = e2.team_id
GROUP BY e.employee_id;

For an employee whose join finds no match (for example, a NULL team_id), LEFT JOIN still creates one row with NULLs, and COUNT(*) counts it as 1. COUNT(e2.employee_id) would correctly give 0.

Correct: Count a column from the joined side, COUNT(e2.employee_id), so NULL rows are not counted.

Interview Follow-up Questions

• "How would you solve this with a window function, without a self join?" (Hint: COUNT(*) OVER (PARTITION BY team_id).)
• "How would you rewrite it to avoid the k² row blow-up?" (Hint: count per team first, then join.)
• "How would you list only employees whose team has more than 2 people?"
• "How would you find the largest team?"
• "What happens to an employee whose team_id is NULL in this query? How would you include them?"

Similar Problems

• Level 2, Problem 2 (Minimum Distance Between Points), another self join, but with a < condition instead of an equality
• The SELF JOIN scenario "Count Direct Reports per Manager" in the Day 28 concept notes
• Any "how many others share my attribute" problem, like how many customers live in my city, or how many orders share my date

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need "how many rows share my group value," shown on every row → self join on the group column + COUNT + GROUP BY
• The answer must include the current row → do not exclude self-matches
• The answer must exclude the current row (for example, "number of other teammates") → add e.employee_id != e2.employee_id
• Output must have one row per original row → GROUP BY the original row's key

Problem 5: Average Experience by Project

Problem Statement

A company tracks which employees work on which projects, and it also keeps each employee's years of experience. Project managers want to know the average experience of the people on each project, to make sure each team has a healthy mix of skill levels.

Business context A project staffed only with beginners may struggle, and one staffed only with veterans may be expensive. The average experience per project gives a quick health check on the skill mix.

Goal For each project, calculate the average years of experience of the employees assigned to it, rounded to 2 decimal places.

Input A Project table linking projects to employees, and an Employee table with experience details.

Output project_id and average_years, sorted by project_id in ascending order.

Table Structure

Table: Project

Table: Employee

Column Meaning

• project_id — the ID of a project
• employee_id (Project) — the ID of an employee working on that project
• employee_id (Employee) — the unique ID of each employee, and the primary key of Employee
• name — the employee's name
• experience_years — how many years the employee has worked; it is never NULL

Important Notes

• The primary key of Project is the pair (project_id, employee_id). An employee can work on many projects, and a project can have many employees. This is a many-to-many relationship, and the Project table is the "linking table" that connects the two sides.
• experience_years is never NULL, so the average is never distorted by missing values. (AVG ignores NULLs anyway, but it is good to know none are present.)
• Only employees who are assigned to a project matter. An employee with no project does not appear in any project's average.
• The same employee can appear in more than one project. Their experience counts once in each project they belong to (Khaled is in both projects in the example).
• The average must be rounded to 2 decimal places, and the output sorted by project_id.

Example

Input: Project

Input: Employee

Expected Output

CREATE TABLE Project (project_id INTEGER, employee_id INTEGER);
INSERT INTO Project VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 1),
  (2, 4);
CREATE TABLE Employee (employee_id INTEGER, name TEXT, experience_years INTEGER);
INSERT INTO Employee VALUES
  (1, 'Khaled', 3),
  (2, 'Ali', 2),
  (3, 'John', 1),
  (4, 'Doe', 2);

Explanation

• Project 1 has employees 1, 2, and 3, so the average is (3 + 2 + 1) / 3 = 2.00.
• Project 2 has employees 1 and 4, so the average is (3 + 2) / 2 = 2.50.

Extra Example (to see rounding)

Using the same Employee table, suppose Project contains:

Output:

• Project 3: only Doe (2) → 2.00
• Project 4: Ali (2) and John (1) → 1.5 → 1.50
• Project 5: Khaled (3), Ali (2), Doe (2) → 7 / 3 = 2.3333... → rounds to 2.33

Intuition

The Project table knows who works on what but has no experience numbers. The Employee table knows each person's experience but has no idea about projects. To compute an average per project, bring the two together so every assignment row carries the person's experience, then group the rows by project and average them.

Mental Model

Picture two sheets of paper. Sheet one is a staffing list: "Project 1: Khaled, Ali, John." Sheet two is an HR list: "Khaled 3 years, Ali 2 years, John 1 year." You write each person's years next to their name on the staffing list. Then, for each project, you add up the years and divide by the number of people. That is joining first, then grouping and averaging.

Thought Process

1. What does the result need? One row per project with an average of experience.
2. Where is each piece of information? project_id is in Project. experience_years is in Employee.
3. What connects them? employee_id.
4. What join type? INNER JOIN. Only employees who are assigned to a project matter, and every assignment has a real employee.
5. How do I get one row per project? GROUP BY p.project_id.
6. How do I get the average? AVG(e.experience_years).
7. How do I format it? ROUND(..., 2), and finish with ORDER BY project_id.

Approach

Step 1: Join Project with Employee On p.employee_id = e.employee_id, so every assignment row gets the employee's experience.

Step 2: Group by project GROUP BY p.project_id.

Step 3: Calculate the average AVG(e.experience_years).

Step 4: Round to 2 decimal places ROUND(AVG(...), 2).

Step 5: Sort ORDER BY p.project_id.

SQL Query

SELECT
    p.project_id,
    -- Average experience of everyone assigned to the project, to 2 decimals
    ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project p
-- Bring each assigned employee's experience next to their project assignment
JOIN Employee e 
    ON p.employee_id = e.employee_id
-- One result row per project
GROUP BY p.project_id
-- Required ascending order
ORDER BY p.project_id;

Query Explanation

• FROM Project p JOIN Employee e ON p.employee_id = e.employee_id — attaches each employee's details (including experience_years) to each of their project assignments. If an employee works on 2 projects, they appear in 2 joined rows.
• GROUP BY p.project_id — puts all the joined rows of the same project into one group.
• AVG(e.experience_years) — for each group, adds up the experience years and divides by the number of rows in the group.
• ROUND(..., 2) — rounds the average to 2 decimal places.
• ORDER BY p.project_id — sorts the output by project.

Dry Run

Original tables

Project:

Employee:

↓

Step 1: JOIN on employee_id

Khaled appears twice because he is on two projects.

↓

Step 2: GROUP BY project_id

↓

Step 3: AVG and ROUND

↓

Final output

SQL Execution Flow

FROM Project p
   ↓
JOIN Employee e ON p.employee_id = e.employee_id
   ↓
GROUP BY p.project_id
   ↓
AVG(e.experience_years) for each group
   ↓
ROUND(..., 2)
   ↓
SELECT project_id, average_years
   ↓
ORDER BY project_id

Time Complexity

Time Complexity: O(n log n) The join is O(n) when Employee.employee_id is indexed (it is the primary key), each Project row does one quick lookup. Grouping usually needs sorting or hashing, which gives O(n log n). Sorting the final result is cheap, since there are only as many rows as projects.

Space Complexity: O(p), where p is the number of projects The database keeps one running sum and count per project.

Why: Every assignment row must be visited once, and the grouping step is what adds the extra log factor.

Common Mistakes to Avoid

Mistake 1: Forgetting GROUP BY

Why it happens: The average feels like a single-value calculation.

Wrong:

SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id;

Without GROUP BY, AVG covers the whole joined table, giving one overall average (or an error in strict mode), not one per project.

Correct: Add GROUP BY p.project_id.

Mistake 2: Using AVG(DISTINCT ...)

Why it happens: DISTINCT seems like a way to be careful about duplicates.

Wrong:

ROUND(AVG(DISTINCT e.experience_years), 2)

If a project has two employees with the same experience (say 2 and 2) and one with 4, the distinct values are {2, 4}, and the average becomes 3 instead of (2 + 2 + 4) / 3 = 2.67. The repeated value is removed, which distorts the result.

Correct: Plain AVG(e.experience_years). Each person counts once because the pair (project_id, employee_id) is already unique.

Mistake 3: Using a LEFT JOIN from Employee

Why it happens: Wanting to "include everyone."

Wrong:

SELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years
FROM Employee e
LEFT JOIN Project p ON p.employee_id = e.employee_id
GROUP BY p.project_id;

Employees with no project form a group with project_id = NULL, which adds an unwanted row to the output.

Correct: Use an INNER JOIN. The report is about projects, so employees without projects do not belong in it.

Mistake 4: Forgetting the ON condition

Why it happens: Assuming the tables are connected automatically.

Wrong:

FROM Project p JOIN Employee e

MySQL treats this as a CROSS JOIN, so every project row is paired with every employee. Each project would then average over all employees' experience, giving the same number for every project.

Correct: Always include ON p.employee_id = e.employee_id.

Mistake 5: Rounding in the wrong place, or not at all

Why it happens: ROUND feels like decoration.

Wrong:

SELECT p.project_id, AVG(e.experience_years) AS average_years

This may return 2.3333 instead of 2.33, which does not match the required format.

Also wrong: AVG(ROUND(e.experience_years, 2)). Rounding each whole number before averaging does nothing useful, and the average itself is still unrounded.

Correct: ROUND(AVG(e.experience_years), 2), rounding the final average.

Mistake 6: Forgetting ORDER BY

Why it happens: Output often looks sorted by accident.

Why it matters: Row order is not guaranteed without ORDER BY, and the problem asks for ascending project_id.

Correct: End with ORDER BY p.project_id.

Interview Follow-up Questions

• "How would you find projects whose average experience is higher than the overall average of all employees?"
• "How would you show the most experienced and the least experienced person on each project too?"
• "If a project exists in a Projects master table but has no employees yet, how would you show it with a NULL average?"
• "What is wrong with averaging with AVG(DISTINCT ...) here? Show a small example."
• "How is this different from averaging experience over the Employee table alone?"

Similar Problems

• The earlier Query Quality Analysis problem, which also used AVG and ROUND with GROUP BY
• The INNER JOIN scenario "Revenue per customer" in the Day 28 concept notes (join, then group, then aggregate)
• Any report that needs an average, sum, or count per group after attaching details from a second table

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• The grouping column is in one table and the value to average is in another → join first, then GROUP BY
• Two entities are related many-to-many through a table with two ID columns → that table is a linking table, and it is usually the starting point of the join
• Output needs averages with a fixed number of decimals → ROUND(AVG(...), n)
• Tempted to use DISTINCT inside an aggregate "to be safe" → stop and check whether it would remove legitimately repeated values

Problem 6: Warehouse Stock Manager

Problem Statement

A warehouse manager needs to know how much physical space the stored products take up in each warehouse. This matters for logistics, expansion planning, and inventory control.

Business context If a warehouse is almost full by volume, it cannot take more stock, no matter how few product types it holds. Volume (in cubic feet) is the real measure of space used.

Goal Calculate the total volume, in cubic feet, occupied by the inventory in each warehouse.

Input A Warehouse table (which products and how many units are stored in each warehouse) and a Products table (the size of each product).

Output warehouse_name and volume for each warehouse. The result can be in any order.

Table Structure

Table: Warehouse

Table: Products

Column Meaning

• name — the name of the warehouse
• product_id (Warehouse) — the product stored there; it points to the Products table (a foreign key)
• units — how many units of that product are stored in that warehouse
• product_id (Products) — the unique ID of each product, and the primary key
• product_name — the name of the product
• Width, Length, Height — the size of one unit of the product, in feet

Important Notes

• The primary key of Warehouse is (name, product_id). A warehouse can store many products, and a product can be stored in many warehouses.
• The volume of one unit is Width × Length × Height. The volume used by a product in a warehouse is that number times units.
• The total volume of a warehouse is the sum of those values over all products stored in it.
• Volume is in cubic feet because the dimensions are in feet. No unit conversion is needed.
• MySQL column names are not case sensitive, so Width and width refer to the same column.
• If any dimension were NULL, the product of the dimensions would be NULL, and SUM would silently ignore that row. This problem has no NULL dimensions, but it is a good edge case to remember.

Example

Input: Warehouse

Input: Products

Expected Output

CREATE TABLE Warehouse (name TEXT, product_id INTEGER, units INTEGER);
INSERT INTO Warehouse VALUES
  ('VCHouse1', 1, 1),
  ('VCHouse1', 2, 10),
  ('VCHouse1', 3, 5),
  ('VCHouse2', 1, 2),
  ('VCHouse2', 2, 2),
  ('VCHouse3', 4, 1);
CREATE TABLE Products (product_id INTEGER, product_name TEXT, Width INTEGER, Length INTEGER, Height INTEGER);
INSERT INTO Products VALUES
  (1, 'VC-TV', 5, 50, 40),
  (2, 'VC-KeyChain', 5, 5, 5),
  (3, 'VC-Phone', 2, 10, 10),
  (4, 'VC-T-Shirt', 4, 10, 20);

Explanation

Volume of one unit: VC-TV = 5×50×40 = 10000, VC-KeyChain = 5×5×5 = 125, VC-Phone = 2×10×10 = 200, VC-T-Shirt = 4×10×20 = 800.

• VCHouse1: 1×10000 + 10×125 + 5×200 = 10000 + 1250 + 1000 = 12250
• VCHouse2: 2×10000 + 2×125 = 20000 + 250 = 20250
• VCHouse3: 1×800 = 800

Extra Example

Suppose a new warehouse stores these items:

Volume: 4×200 + 2×800 = 800 + 1600 = 2400

Intuition

Each line in the Warehouse table says "this warehouse holds N units of this product." But the size of a unit is stored in a different table. To get the volume taken by a line, find the product's size, multiply it out, then multiply by the number of units. Finally, add up all the lines that belong to the same warehouse.

Mental Model

Imagine packing boxes. A line in the Warehouse table reads: "10 boxes of key chains." The Products table tells you each key chain box is 5 by 5 by 5 feet. So those 10 boxes take 10 × 125 = 1250 cubic feet. You do this for every line, then add up the lines per warehouse to see how much of the floor is used.

The important thing: the multiplication (size × units) happens line by line first, and only then are the results added together.

Thought Process

1. What columns are needed? name and units from Warehouse; Width, Length, Height from Products.
2. How are the tables connected? product_id.
3. What join? INNER JOIN. We only care about products that are actually stored somewhere.
4. What do I calculate per row? Width × Length × Height × units.
5. How do I combine rows per warehouse? SUM(...) with GROUP BY w.name.
6. Why must the multiplication be inside the SUM? Each product has its own size and its own units. Adding first and multiplying later would mix up different products.

Approach

Step 1: Join Warehouse with Products on product_id Each stock line now carries the product's dimensions.

Step 2: Calculate each line's volume Width × Length × Height × units.

Step 3: Sum the line volumes per warehouse SUM(...) and GROUP BY w.name.

Step 4: Return the warehouse name and total volume Give the columns the required names: warehouse_name and volume.

SQL Query

SELECT
    w.name AS warehouse_name,
    -- Volume of one unit (W x L x H) times the units stored,
    -- calculated for each row FIRST, then added up per warehouse
    SUM(p.width * p.length * p.height * w.units) AS volume
FROM Warehouse w
-- Attach each product's dimensions to its stock line
JOIN Products p 
    ON w.product_id = p.product_id
-- One result row per warehouse
GROUP BY w.name;

Query Explanation

• FROM Warehouse w JOIN Products p ON w.product_id = p.product_id — matches every stock line with its product, so dimensions and units sit on the same row.
• p.width * p.length * p.height — the volume of one unit.
• * w.units — the volume used by all units of that product in that warehouse.
• SUM(...) — adds up the line volumes inside each group.
• GROUP BY w.name — makes one group per warehouse, so SUM is calculated per warehouse.
• AS warehouse_name, AS volume — names the output columns as the problem requires.

Dry Run

Original tables

Warehouse:

Products:

↓

Step 1: JOIN on product_id and calculate each line's volume

↓

Step 2: GROUP BY warehouse and SUM the line volumes

↓

Final output

SQL Execution Flow

FROM Warehouse w
   ↓
JOIN Products p ON w.product_id = p.product_id
   ↓
GROUP BY w.name
   ↓
For every row: width * length * height * units
   ↓
SUM(...) within each warehouse group
   ↓
SELECT warehouse_name, volume

Time Complexity

Time Complexity: O(n log n), or O(n) with hash-based grouping Each Warehouse row does one lookup in Products (fast, since product_id is the primary key). The grouping step adds the log factor when it uses sorting.

Space Complexity: O(w), where w is the number of warehouses The database keeps one running total for each warehouse.

Why: Every stock line must be visited once, and the arithmetic per row is constant work. The only extra cost comes from grouping.

Common Mistakes to Avoid

Mistake 1: Multiplying the totals instead of the rows

Why it happens: Splitting the formula into "total volume times total units" seems logical.

Wrong:

SELECT w.name, SUM(p.width * p.length * p.height) * SUM(w.units) AS volume
FROM Warehouse w
JOIN Products p ON w.product_id = p.product_id
GROUP BY w.name;

For VCHouse1, the sum of unit volumes is 10000 + 125 + 200 = 10325 and the sum of units is 16, giving 165200 instead of 12250. Each product's size must be paired with its own units.

Correct: Multiply inside the SUM, row by row.

Mistake 2: Forgetting to multiply by units

Why it happens: Focusing on the dimension formula only.

Wrong:

SUM(p.width * p.length * p.height) AS volume

VCHouse1 becomes 10325 (one unit of each product) instead of 12250.

Correct: Include * w.units.

Mistake 3: Forgetting GROUP BY

Why it happens: The join and SUM look complete.

Wrong:

SELECT w.name AS warehouse_name, SUM(p.width * p.length * p.height * w.units) AS volume
FROM Warehouse w
JOIN Products p ON w.product_id = p.product_id;

This errors in strict mode, or collapses everything into one row in loose mode.

Correct: GROUP BY w.name.

Mistake 4: Grouping by the wrong column

Why it happens: Both tables have several ID-like columns.

Wrong:

GROUP BY p.product_id

This returns the total volume of each product across all warehouses, not the volume per warehouse.

Correct: Group by the thing named in the question: the warehouse, w.name.

Mistake 5: Starting from Products with a LEFT JOIN

Why it happens: Trying to include "everything."

Wrong:

SELECT w.name AS warehouse_name, SUM(p.width * p.length * p.height * w.units) AS volume
FROM Products p
LEFT JOIN Warehouse w ON w.product_id = p.product_id
GROUP BY w.name;

A product that is not stored in any warehouse would create a group with name = NULL, adding an unwanted row with a NULL warehouse and NULL volume.

Correct: Use an INNER JOIN. The report is about warehouses that actually store something.

Mistake 6: Mixing up ambiguous column names

Why it happens: Both tables have a product_id column.

Wrong:

JOIN Products p ON product_id = product_id

MySQL reports an "ambiguous column" error.

Correct: ON w.product_id = p.product_id.

Interview Follow-up Questions

• "How would you find the warehouse that uses the most space?" (Hint: add ORDER BY volume DESC LIMIT 1.)
• "How would you show warehouses that store nothing at all, with a volume of 0?" (Hint: you would need a Warehouses master table and a LEFT JOIN.)
• "If a warehouse has a maximum capacity, how would you show what percentage is used?"
• "How would you find the single biggest product (by total volume) in each warehouse?" (A window function problem, for later.)
• "What happens to the total if a product has a NULL dimension? How would you catch that?"

Similar Problems

• Level 2, Problem 5 (Average Experience by Project), the same "join, then aggregate per group" shape
• The "Revenue per customer" scenario in the Day 28 concept notes, which also multiplied values and summed them per group
• Any cost-or-weight total, like order totals (quantity × price) or shipment weight (units × weight per unit)

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need "quantity times size (or price, or weight)" added up per group → SUM(a * b * ...) with GROUP BY
• Information needed for one calculation is split across two tables → join them first
• Each row has its own multiplier (here, its own units) → do the multiplication per row inside SUM, never after
• Tempted to write SUM(x) * SUM(y) → stop, and check whether the two values belong together on each row; if they do, multiply first, then sum

Level 2 Recap

The common thread: in every one of these problems, a join is only the first step. The real work happens in what comes next: a condition in ON, an aggregate function, or arithmetic. When you read a new problem, first decide which tables hold the data you need and how to connect them. Then decide what happens to the joined rows.