Merging Query Results Problems

Problem 1: Combine Active and Archived Users

Problem Statement

A platform stores user information in two separate tables: one for currently active users, and one for archived (inactive or removed) users. To generate a consolidated report, the company wants a single, unique list of every user_id that has ever existed on the platform, across both tables combined.

Business context Over time, users move between "active" and "archived" states. For a report that simply asks "how many unique users has this platform ever had," you need to look across both tables at once, while making sure someone who happens to appear in both isn't double-counted.

Goal Return every unique user_id that appears in either the ActiveUsers table or the ArchivedUsers table (or both).

Input Two separate tables, each listing user IDs.

Output One combined list of unique user_ids.

Table Structure

Table: ActiveUsers

Table: ArchivedUsers

Column Meaning

• user_id — a unique identifier for a user. In both tables, this column is the primary key of that specific table, meaning a user_id can't repeat within ActiveUsers, and can't repeat within ArchivedUsers, but the exact same user_id value can appear in both tables at once, representing the same real user who has, at some point, existed in both states.

Important Notes

• The problem explicitly states: "Some users may appear in both tables." This is the whole reason deduplication matters here, without it, a user present in both tables would be counted twice in a "total users" report.
• Both tables have the exact same single-column structure (user_id), which makes this about as simple as a UNION problem can get, there's no need to worry about mismatched column counts or types.
• The output order doesn't matter here, so no ORDER BY is required.

Example

Input: ActiveUsers

Input: ArchivedUsers

Expected Output

CREATE TABLE ActiveUsers (user_id INTEGER);
INSERT INTO ActiveUsers VALUES
  (1),
  (2),
  (3);
CREATE TABLE ArchivedUsers (user_id INTEGER);
INSERT INTO ArchivedUsers VALUES
  (3),
  (4),
  (5);

Explanation: User 3 appears in both ActiveUsers and ArchivedUsers, but is shown only once in the final combined result, since the requirement specifically asks for unique user_ids.

Intuition

Think of this like combining a guest list from two separate parties into one master attendance record for the whole event weekend. If someone attended both parties, you don't want them counted twice in your final "who came to this event" list, you just want to know that they came, once.

This is the most textbook-simple use case for UNION: two tables of the same shape, needing to become one deduplicated list.

Mental Model

Picture two transparent sheets, each with a list of numbers written on it, one sheet for ActiveUsers, one for ArchivedUsers. If you stack the two sheets directly on top of each other and hold them up to the light, any number that appears on both sheets will look "doubled up" and darker where the ink overlaps. UNION is like tracing over that stacked image just once for every number you see, regardless of whether it looked "doubled" or not, you end up with one clean, final list where every number appears exactly once.

Thought Process

Before writing SQL, ask the two key questions this problem is testing:

1. Am I combining rows vertically (stacking), or horizontally (side by side)? Since both tables share the exact same single column (user_id), and the goal is "all user_ids from either table," this is clearly a vertical stacking operation, not a join.
2. Do I need to keep duplicates, or remove them? The requirement explicitly says "unique list," and explicitly warns that some users may appear in both tables, this is a direct, clear signal to use UNION (not UNION ALL), since UNION's whole job is guaranteeing uniqueness in the final result.

Approach

Step 1: Select user_id from ActiveUsers A simple SELECT user_id FROM ActiveUsers.

Step 2: Select user_id from ArchivedUsers A simple SELECT user_id FROM ArchivedUsers.

Step 3: Combine both using UNION Since duplicates need to be removed (a user in both tables should only show up once), use UNION rather than UNION ALL.

SQL Query

SELECT user_id
FROM ActiveUsers
-- UNION automatically removes duplicate user_ids that appear in both tables
UNION
SELECT user_id
FROM ArchivedUsers;

Query Explanation

• SELECT user_id FROM ActiveUsers — the first half of the combined result, every user_id currently marked active.
• UNION — combines this with the second SELECT's results, and removes any exact duplicate rows in the final combined output.
• SELECT user_id FROM ArchivedUsers — the second half of the combined result, every user_id currently archived.

Since both SELECT statements return just one column (user_id), and that column has the same data type in both tables, this satisfies UNION's requirement of matching column counts and compatible types.

Dry Run

ActiveUsers

ArchivedUsers

↓

Step 1: Combine both result sets (conceptually, before deduplication)

↓

Step 2: Remove exact duplicate rows (UNION's deduplication step)

user_id 3 appears twice in the combined list above, one instance is removed, keeping just a single occurrence.

↓

Final output

SQL Execution Flow

SELECT user_id FROM ActiveUsers
   ↓
SELECT user_id FROM ArchivedUsers
   ↓
Combine both result sets together
   ↓
Compare every row against every other row (full row comparison)
   ↓
Remove exact duplicate rows
   ↓
Return final unique combined output

Time Complexity

Time Complexity: O(n + m) for reading both tables, plus the cost of deduplication, which is typically O((n + m) log(n + m)) if the database sorts the combined result to find duplicates, where n and m are the row counts of ActiveUsers and ArchivedUsers respectively.

Space Complexity: O(n + m) The database needs to hold the combined result set (or enough of it) in memory or temporary storage while comparing rows to identify and remove duplicates.

Why: Unlike a simple filter or a single-table scan, UNION's deduplication step requires looking at the combined set of rows from both tables together, which means the cost scales with the total number of rows across both sources, not just one of them.

Common Mistakes to Avoid

Mistake 1: Using UNION ALL when uniqueness is explicitly required

Why it happens: UNION ALL is faster, so it can be tempting to default to it, especially after learning that performance matters.

Wrong:

SELECT user_id FROM ActiveUsers
UNION ALL
SELECT user_id FROM ArchivedUsers;

This would return user_id 3 twice in the output, directly violating the requirement for a unique list of users.

Correct: Use plain UNION here, since the problem explicitly asks for unique user_ids, and explicitly warns that overlap between the two tables should be expected.

Mistake 2: Trying to solve this with a JOIN instead of UNION

Why it happens: Beginners sometimes reach for JOIN by default whenever two tables are involved, without pausing to consider whether the goal is actually to stack rows vertically, not match them side by side.

Wrong (overcomplicated and conceptually incorrect for this problem):

SELECT a.user_id
FROM ActiveUsers a
LEFT JOIN ArchivedUsers ar ON a.user_id = ar.user_id;

This doesn't even achieve the right goal, a JOIN combines columns from matching rows, it doesn't stack two lists of the same column into one combined list.

Correct: Recognize that "combine values from two sources into one unique list" is a vertical stacking problem, which is exactly what UNION is built for, not a join.

Mistake 3: Selecting extra columns that break the UNION's column matching requirement

Why it happens: If either table happened to have more columns in a more complex version of this problem, accidentally selecting mismatched columns between the two SELECT statements would cause an error.

Why it matters: Both SELECT statements involved in a UNION must return the exact same number of columns. Even in this simple single-column example, it's worth building the habit of double-checking this for every UNION query you write, especially as tables grow more complex.

Interview Follow-up Questions

• "How would you find users who exist ONLY in ActiveUsers, but not in ArchivedUsers?"
• "How would you count the total number of unique users, instead of listing them all individually?"
• "What would happen if you accidentally used UNION ALL here? How would the output differ?"
• "How would this query need to change if the two tables had different column names for the user identifier?"

Similar Problems

• Any "merge two lists into one unique combined list" problem, such as combining subscriber lists from two marketing platforms
• Problems that specifically require distinguishing between UNION and UNION ALL based on whether duplicates should survive

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to combine values from two or more tables that share the same column structure, into one list → UNION (vertical stacking), not a JOIN (horizontal combining)
• Requirement explicitly asks for "unique" values, or explicitly warns about overlapping data between sources → UNION, not UNION ALL

Problem 2: Merge Recent Orders from Multiple Sources

Problem Statement

A company stores customer orders in two different systems: one for online orders, and one for in-store orders. The analytics team needs a single, unified report of recent orders, filtered to a specific date range, and sorted by order amount.

Business context Retail businesses that sell both online and in physical stores often maintain separate transaction systems. For a combined sales report, both sources need to be merged, filtered consistently, and presented in a single sorted view.

Goal Retrieve all unique orders from both tables where the order_date is on or after '2023-01-01', sorted by amount in descending order.

Input Two tables, OnlineOrders and StoreOrders, each with the same three columns.

Output A combined, filtered, and sorted list of orders, with columns in the order: order_id, order_date, amount.

Table Structure

Table: OnlineOrders

Table: StoreOrders

Column Meaning

• order_id — a unique identifier for the order, and the primary key within each individual table
• order_date — the date the order was placed
• amount — the monetary value of the order

Important Notes

• Both tables share the identical column structure (order_id, order_date, amount), which is a requirement for combining them with UNION.
• The filtering condition (order_date >= '2023-01-01') needs to be applied to each table individually, before combining them, not as one single filter applied after the fact.
• The requirement says "all unique orders," and an order can apparently exist in both tables (as shown in the example, order 3 appears in both), so UNION (not UNION ALL) is the correct choice here, exactly like Problem 1.
• The final sort (ORDER BY amount DESC) applies to the entire combined result, and is written only once, at the very end of the query.

Example

Input: OnlineOrders

Input: StoreOrders

Expected Output

CREATE TABLE OnlineOrders (order_id INTEGER, order_date TEXT, amount INTEGER);
INSERT INTO OnlineOrders VALUES
  (1, '2023-01-05', 500),
  (2, '2022-12-20', 300),
  (3, '2023-02-01', 700);
CREATE TABLE StoreOrders (order_id INTEGER, order_date TEXT, amount INTEGER);
INSERT INTO StoreOrders VALUES
  (3, '2023-02-01', 700),
  (4, '2023-03-10', 400);

Explanation

• Order 2 is filtered out entirely, since its order_date (2022-12-20) is before the cutoff of 2023-01-01.
• Order 3 appears in both OnlineOrders and StoreOrders with identical values across every column, so it's shown only once in the combined result (UNION removes this exact duplicate).
• The final result is sorted by amount in descending order: 700, 500, 400.

Intuition

Think of this as two separate cash registers, one for the website, one for the physical store, each printing a daily transaction log. To build one combined report, you first throw out any transaction older than the report's start date from each log individually, then merge what's left of both logs together, making sure no transaction accidentally gets counted twice if it somehow got logged in both places, and finally, sort the combined pile from biggest sale to smallest.

The key sequencing detail here is that filtering happens before combining, not after, each source needs to be trimmed down to only its relevant recent orders first, and only then do the two trimmed lists get merged together.

Mental Model

Picture two separate inboxes, one for online order confirmations, one for in-store receipts. Before merging them into one folder, you first go through each inbox separately and delete anything older than your cutoff date. Only after both inboxes have been cleaned up do you pour everything remaining into one combined folder, remove any exact duplicate confirmation that happens to appear in both inboxes, and then sort the whole folder by amount, largest first.

Thought Process

Before writing SQL, break this problem into its distinct pieces:

1. What filter applies, and where? order_date >= '2023-01-01', and it needs to be applied separately to each table's SELECT statement, since each table is being filtered independently before merging.
2. Do I need unique results, or should duplicates be preserved? The problem says "all unique orders," and shows an example where an identical order (order 3) exists in both tables and should only appear once, this is a clear signal for UNION, not UNION ALL.
3. How should the final result be sorted? By amount in descending order, applied once, to the whole combined result, at the very end of the query.
4. What column order does the output need? Specifically order_id, order_date, amount, in that exact order, in both SELECT statements, so they line up correctly when combined.

Approach

Step 1: Filter each table individually Apply WHERE order_date >= '2023-01-01' to both the OnlineOrders SELECT and the StoreOrders SELECT.

Step 2: Combine both filtered results using UNION Since duplicates should be removed (as shown by order 3 in the example), use UNION, not UNION ALL.

Step 3: Apply the final sort Add ORDER BY amount DESC once, after the entire UNION, so it sorts the whole combined result set.

Step 4: Ensure matching column order and types Both SELECT statements must select order_id, order_date, and amount, in that exact order, so the combined result lines up correctly.

SQL Query

SELECT
    order_id,
    order_date,
    amount
FROM OnlineOrders
WHERE order_date >= '2023-01-01'
-- UNION removes the duplicate order (order 3, identical in both tables)
UNION
SELECT
    order_id,
    order_date,
    amount
FROM StoreOrders
WHERE order_date >= '2023-01-01'
-- Sort the entire combined result, applied only once, at the very end
ORDER BY amount DESC;

Query Explanation

• WHERE order_date >= '2023-01-01' — applied separately inside each SELECT statement, filtering out old orders from each source before they get combined.
• UNION — merges the two filtered result sets together, and removes any row that is an exact duplicate across every column (order_id, order_date, and amount all matching).
• ORDER BY amount DESC — placed after the entire UNION expression, this sorts the final, already-combined-and-deduplicated result set by amount, from highest to lowest.

Dry Run

OnlineOrders, after filtering (order_date >= '2023-01-01')

(Order 2 is excluded here, since 2022-12-20 is before the cutoff.)

StoreOrders, after filtering (order_date >= '2023-01-01')

↓

Combine both filtered results (before deduplication)

↓

Remove exact duplicate rows (UNION's deduplication)

Order 3 appears identically in both sources (same order_id, order_date, and amount), so one copy is removed.

↓

Apply ORDER BY amount DESC

SQL Execution Flow

FROM OnlineOrders
   ↓
WHERE order_date >= '2023-01-01' (filter OnlineOrders)
   ↓
FROM StoreOrders
   ↓
WHERE order_date >= '2023-01-01' (filter StoreOrders)
   ↓
UNION (combine both filtered sets, remove exact duplicates)
   ↓
ORDER BY amount DESC (sort the entire combined result)
   ↓
Return final sorted output

Time Complexity

Time Complexity: O((n + m) log(n + m)) Filtering each table is O(n) and O(m) individually. Combining with UNION requires deduplication, typically involving a sort or hash step across the combined filtered rows. The final ORDER BY also requires sorting, though this can often be combined with the deduplication sort the database already needs to perform for UNION.

Space Complexity: O(n + m) The combined, filtered result set needs to be held in memory or temporary storage to perform deduplication and sorting.

Why: This query layers three separate costs, filtering each source, deduplicating the combined result, and sorting the final output, though a good query optimizer may be able to combine some of these steps efficiently.

Common Mistakes to Avoid

Mistake 1: Filtering only after combining, instead of filtering each table first

Why it happens: It might seem simpler to combine everything first, then filter once at the end.

Risky (and sometimes outright invalid syntax, depending on the database, since a WHERE clause generally can't follow a UNION directly without wrapping it in a subquery):

SELECT order_id, order_date, amount FROM OnlineOrders
UNION
SELECT order_id, order_date, amount FROM StoreOrders
WHERE order_date >= '2023-01-01';

In many databases, this WHERE clause would only apply to the second SELECT statement (StoreOrders), not to the combined result as a whole, silently producing wrong results by leaving old OnlineOrders rows in the output.

Correct: Apply the WHERE filter inside each individual SELECT statement, before the UNION combines them:

SELECT order_id, order_date, amount FROM OnlineOrders WHERE order_date >= '2023-01-01'
UNION
SELECT order_id, order_date, amount FROM StoreOrders WHERE order_date >= '2023-01-01';

Mistake 2: Adding ORDER BY to each individual SELECT statement

Why it happens: It feels natural to sort each piece before combining them.

Wrong (often a syntax error):

SELECT order_id, order_date, amount FROM OnlineOrders WHERE order_date >= '2023-01-01' ORDER BY amount DESC
UNION
SELECT order_id, order_date, amount FROM StoreOrders WHERE order_date >= '2023-01-01' ORDER BY amount DESC;

Correct: Only one ORDER BY is needed, applied after the entire UNION, sorting the final combined result as a whole.

Mistake 3: Using UNION ALL and getting a duplicated order 3 in the output

Why it happens: Assuming UNION ALL is always the "default" or "safe" choice without checking the specific requirement.

Wrong:

SELECT order_id, order_date, amount FROM OnlineOrders WHERE order_date >= '2023-01-01'
UNION ALL
SELECT order_id, order_date, amount FROM StoreOrders WHERE order_date >= '2023-01-01'
ORDER BY amount DESC;

This would show order 3 twice in the final output, which directly contradicts the expected output shown in the example, where order 3 appears only once.

Correct: Use plain UNION here, since the problem's example explicitly demonstrates that an identical order appearing in both tables should be shown only once.

Mistake 4: Getting the column order wrong or mismatched between the two SELECT statements

Why it happens: It's easy to accidentally list columns in a different order in the second SELECT statement, especially when copy-pasting and editing quickly.

Wrong:

SELECT order_id, order_date, amount FROM OnlineOrders WHERE order_date >= '2023-01-01'
UNION
SELECT amount, order_date, order_id FROM StoreOrders WHERE order_date >= '2023-01-01'
ORDER BY amount DESC;

Even though all the same columns are present, UNION matches columns strictly by position, not by name, so this would silently combine order_id values with amount values, producing nonsensical results.

Correct: Always keep the exact same column order in every SELECT statement involved in a UNION.

Interview Follow-up Questions

• "How would you modify this query to show which source system (online or in-store) each order came from?"
• "What would happen if the two tables had order_id values that overlapped, but represented genuinely different orders (not true duplicates)? How would UNION handle that?"
• "How would you rewrite this using a subquery, filtering and sorting only after the UNION, instead of filtering inside each SELECT?"
• "How would this query's performance be affected if order_date wasn't indexed in either table?"

Similar Problems

• Any multi-source reporting problem requiring per-source filtering before combining results
• Problems that test the specific interaction between WHERE, UNION, and a single trailing ORDER BY
• Problems distinguishing between UNION and UNION ALL based on whether an example explicitly shows an overlapping record being deduplicated or preserved

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to filter two separate sources the same way, before merging them into one report → apply the WHERE clause inside each individual SELECT, not after the UNION
• Need one final sort across a combined multi-table result → write ORDER BY only once, at the very end of the entire UNION chain
• Multiple columns need to line up correctly across combined SELECT statements → always double check that column order (not just column names) matches exactly between every SELECT involved

Problem 3: Combine Sales Records Without Deduplication

Problem Statement

A company stores sales data separately for domestic and international transactions. For auditing purposes, the finance team needs a complete list of every sales record from both systems, even if some entries appear to be identical across both.

Business context In auditing, seeing the same record appear twice across two systems isn't noise to be cleaned away, it might be exactly the kind of red flag an auditor is looking for (like the same sale being logged in two separate systems, or a possible data entry mistake). Silently deduplicating this data would hide potentially important information.

Goal Return every sales record from both tables, preserving duplicates exactly as they exist in the source data.

Input Two tables, DomesticSales and InternationalSales, sharing the same column structure.

Output A combined list of every sales record, including duplicates, ordered by sale_id and sale_date ascending.

Table Structure

Table: DomesticSales

Table: InternationalSales

Column Meaning

• sale_id — a unique identifier for the sale, and the primary key within each individual table (it's worth noting: since each table has its own separate primary key constraint, the same sale_id value can legitimately appear in both tables, exactly as shown in the example)
• sale_date — the date the sale occurred
• amount — the monetary value of the sale

Important Notes

• This is the direct counterpart to Problems 1 and 2. Here, the explicit requirement is the opposite: preserve every record, including duplicates, rather than removing them.
• The phrase "including duplicates if any exist" is the clearest possible signal that UNION ALL, not UNION, is required here.
• The final sort is by two columns, sale_id first, then sale_date, both ascending.

Example

Input: DomesticSales

Input: InternationalSales

Expected Output

CREATE TABLE DomesticSales (sale_id INTEGER, sale_date TEXT, amount INTEGER);
INSERT INTO DomesticSales VALUES
  (1, '2023-01-05', 500),
  (2, '2023-01-06', 300);
CREATE TABLE InternationalSales (sale_id INTEGER, sale_date TEXT, amount INTEGER);
INSERT INTO InternationalSales VALUES
  (2, '2023-01-06', 300),
  (3, '2023-01-07', 700);

Explanation: Sale 2 appears in both DomesticSales and InternationalSales with identical values, and is shown twice in the final output, since the requirement specifically says duplicates must be preserved, not removed.

Intuition

Think of an auditor physically stapling together two separate stacks of receipts, one from the domestic sales office, one from the international sales office, without ever throwing a single receipt away, even if two receipts happen to look identical. To the auditor, "this receipt appears twice" is itself a finding worth investigating, not something to quietly discard before they even get a chance to look at it.

This is the exact opposite mindset from Problems 1 and 2, here, duplication is data, not noise.

Mental Model

Picture two file folders being emptied directly into one big pile, with absolutely no sorting, filtering, or checking happening during the emptying itself. Every single sheet of paper from both folders lands in the pile, exactly as it was, even if two sheets happen to be word-for-word identical. Only after everything has landed in the pile does anyone bother to organize it (in this case, by sorting it by sale_id, then sale_date).

Thought Process

Before writing SQL, identify the one critical difference from the earlier problems:

1. Should duplicates be removed or kept? The requirement explicitly says "including duplicates if any exist," this directly tells you to use UNION ALL, not UNION.
2. What columns need to be selected, and in what order? sale_id, sale_date, amount, matching exactly between both SELECT statements.
3. How should the final result be sorted? By sale_id first, then by sale_date, both ascending, this is a two-column sort, not a single-column one.

Approach

Step 1: Select the same columns from both tables sale_id, sale_date, and amount, from both DomesticSales and InternationalSales.

Step 2: Combine using UNION ALL, not UNION Since duplicates must be preserved, deliberately avoid UNION (and avoid adding DISTINCT anywhere), using UNION ALL instead.

Step 3: Sort the final combined result Apply ORDER BY sale_id, sale_date once, at the very end, covering the entire combined result set.

SQL Query

SELECT
    sale_id,
    sale_date,
    amount
FROM DomesticSales
-- UNION ALL keeps every row, including exact duplicates, since this is an audit
UNION ALL
SELECT
    sale_id,
    sale_date,
    amount
FROM InternationalSales
ORDER BY sale_id, sale_date;

Query Explanation

• UNION ALL — combines both result sets directly, with absolutely no row comparison or duplicate removal happening at all.
• ORDER BY sale_id, sale_date — sorts the entire combined result first by sale_id (ascending), and then by sale_date (ascending) as a secondary sort key, used to break ties when multiple rows share the same sale_id (as happens here with the two identical sale_id 2 rows).

Dry Run

DomesticSales

InternationalSales

↓

Combine both result sets directly, with UNION ALL (no deduplication at all)

Notice: both sale_id 2 rows survive here, exactly as they should, since UNION ALL never checks for duplicates.

↓

Apply ORDER BY sale_id, sale_date

Since the data already happens to be in sale_id order from the combination step above, the sort doesn't visibly reorder anything further in this particular example, but it's still a required, explicit step in the query.

SQL Execution Flow

FROM DomesticSales
   ↓
FROM InternationalSales
   ↓
UNION ALL (combine directly, no duplicate checking)
   ↓
ORDER BY sale_id, sale_date (sort the entire combined result)
   ↓
Return final output, every original row preserved

Time Complexity

Time Complexity: O(n + m + (n+m) log(n+m)) Reading both tables is O(n + m). Since UNION ALL performs no deduplication, that part of the cost is avoided entirely (unlike Problems 1 and 2). The final ORDER BY still requires sorting the combined result, which is typically O((n+m) log(n+m)).

Space Complexity: O(n + m) The combined result set needs to be held long enough to be sorted, though no additional space is needed for duplicate-checking, since none happens.

Why: This query is cheaper than Problems 1 and 2 in one specific way, it skips the deduplication step entirely, which is real, avoidable computational work. The only remaining cost beyond reading both tables is the final sort.

Common Mistakes to Avoid

Mistake 1: Using UNION instead of UNION ALL

Why it happens: UNION is often the more familiar, more frequently used command, making it an easy default to reach for without re-checking the specific requirement.

Wrong:

SELECT sale_id, sale_date, amount FROM DomesticSales
UNION
SELECT sale_id, sale_date, amount FROM InternationalSales
ORDER BY sale_id, sale_date;

This would silently collapse the two identical sale_id 2 rows down into just one, directly contradicting the expected output, which explicitly shows sale_id 2 appearing twice.

Correct: Use UNION ALL, exactly as the requirement explicitly asks for ("including duplicates if any exist").

Mistake 2: Adding DISTINCT anywhere in the query "just to be safe"

Why it happens: DISTINCT is often taught as a general-purpose "clean up my results" habit, without considering whether cleaning up is actually the goal here.

Wrong:

SELECT DISTINCT sale_id, sale_date, amount FROM DomesticSales
UNION ALL
SELECT DISTINCT sale_id, sale_date, amount FROM InternationalSales
ORDER BY sale_id, sale_date;

Even though UNION ALL itself is used correctly here, adding DISTINCT inside each individual SELECT (or anywhere else) working against the entire point of this audit-focused requirement, which explicitly wants duplicates preserved.

Correct: Do not add DISTINCT anywhere in this query. Let UNION ALL do its job of preserving every row exactly as it exists in the source tables.

Mistake 3: Sorting by only sale_id, forgetting the secondary sort key (sale_date)

Why it happens: It's easy to focus on the first sort column and forget that a second one was also specified.

Incomplete:

SELECT sale_id, sale_date, amount FROM DomesticSales
UNION ALL
SELECT sale_id, sale_date, amount FROM InternationalSales
ORDER BY sale_id;

This technically satisfies sorting by sale_id, but doesn't guarantee any particular order for sale_date when multiple rows share the same sale_id, which doesn't fully match the requirement of sorting by "sale_id and sale_date."

Correct:

ORDER BY sale_id, sale_date;

Interview Follow-up Questions

• "How would you count how many duplicate sale records exist between the two tables, without losing the ability to still list every individual record?"
• "What would change about this query if the requirement instead asked for duplicates to be flagged with an extra column, rather than just shown twice in the output?"
• "How would you identify specifically which sale_ids appear in both tables, using this same dataset?" (This is a great bridge into the Intersection technique from the concept notes.)
• "Why might UNION ALL be the safer default choice in an auditing context, compared to UNION?"

Similar Problems

• The Intersection technique from the concept notes, which specifically builds on UNION ALL plus GROUP BY and HAVING to find exactly which sale_ids are shared between both tables
• Any audit, compliance, or reconciliation problem where preserving every record, duplicates included, is a hard requirement rather than an oversight to fix

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement explicitly says "including duplicates," "preserve all records," or mentions auditing/compliance context → UNION ALL, never plain UNION
• Sorting requirement mentions two or more columns → use a comma-separated ORDER BY list, in the exact order the columns should be prioritized
• Tempted to add DISTINCT reflexively → pause and re-check whether uniqueness was actually part of the requirement, or whether it would silently remove meaningful data

Problem 4: Reshape Products Data

Problem Statement

A multi-store inventory system tracks product pricing per store, but the data is stored in wide format, meaning each store's price lives in its own separate column. To generate reports or feed dashboards, this data needs to be reshaped into a different format, where each row represents one product, in one specific store, with its price.

Business context Wide-format data (one column per category) is common in spreadsheets and simple systems, but it doesn't scale well and doesn't fit naturally into most reporting and dashboard tools, which usually expect one row per data point (a format often called "long format"). This transformation, converting columns into rows, is a classic and very common real-world data reshaping task, sometimes called "unpivoting."

Goal Transform the wide-format Products table (with separate store1, store2, store3 columns) into a long-format result, where each row shows product_id, store name, and price, excluding any store where the product isn't available (price is NULL).

Input A single table with one row per product, and separate price columns for each store.

Output A reshaped table with one row per product-store combination that actually has a price, sorted by product_id, store, and price.

Table Structure

Table: Products

Column Meaning

• product_id — a unique identifier for the product, and the primary key of this table
• store1, store2, store3 — the price of the product in each respective store. Each of these is nullable, a NULL value specifically means the product is not available in that store, it does not mean the product is free or priced at zero.

Important Notes

• A NULL in any of the store columns must be excluded entirely from the final result, not shown as a row with a NULL or zero price.
• The output has a completely different shape than the input: 1 row with 3 store columns becomes up to 3 separate rows (fewer, if any store has a NULL price for that product), each with just one store name and one price.
• The final sort is by three columns: product_id, then store, then price, all ascending.

Example

Input: Products table

Expected Output

CREATE TABLE Products (product_id INTEGER, store1 INTEGER, store2 INTEGER, store3 INTEGER);
INSERT INTO Products VALUES
  (0, 95, 100, 105),
  (1, 70, NULL, 80);

Explanation

• Product 0 is available in all three stores, so it produces three separate rows, one per store.
• Product 1 is available only in store1 and store3 (its store2 price is NULL), so store2 is completely excluded for this product, it does not appear as a row with a NULL price, it simply doesn't appear at all.

Intuition

Picture a single spreadsheet row for a product, with three side-by-side price tags labeled "Store 1," "Store 2," and "Store 3." Reshaping this data means taking that one wide row and literally tearing it into up to three separate index cards, one card per store, each card holding just the product's ID, the store's name, and that store's specific price. If a price tag was blank (NULL) to begin with, you simply don't create a card for that store at all, there's nothing to write down.

This is fundamentally different from the earlier problems in this chapter. Problems 1 through 3 combined rows from separate tables that already shared the same shape. This problem instead reshapes columns from a single table into rows, and it does this by treating each store column as if it were its own mini "table" being selected from, then stacking all three mini-selections together using UNION ALL.

Mental Model

Imagine three transparent overlays, each one representing a different store's pricing sheet for the same list of products. Overlay 1 shows product IDs next to store1's prices. Overlay 2 shows the same product IDs next to store2's prices. Overlay 3 shows them next to store3's prices.

To build the final long-format report, you take all three overlays and simply lay them one after another in a single pile (that's the UNION ALL part), but before adding an overlay's sheet to the pile, you first check: does this specific product actually have a real price on this particular overlay? If the price slot is blank (NULL), you skip adding that specific sheet to the pile entirely.

Thought Process

Before writing SQL, recognize that this problem needs a different technique than Problems 1 through 3, even though it still uses UNION ALL.

1. What does each "piece" of the final result represent? One row needs: product_id, a store name (as literal text, like 'store1'), and that store's price. This means each "piece" isn't really selecting from a different table, it's selecting a different column from the same table, and re-labeling it.
2. How do I turn a column into a labeled row? By writing a separate SELECT for each store column, manually including the store's name as a literal string value (like 'store1' AS store), alongside that store's specific price column, renamed to a shared, common alias (like AS price), so all three SELECTs end up with matching column shapes.
3. How do I exclude NULL prices? Add a WHERE store1 IS NOT NULL (and the equivalent for store2 and store3) to each individual SELECT, so that a NULL price for a given store simply never produces a row for that store at all.
4. How do I combine the three SELECTs? Since this is fundamentally reshaping data from the same single source table, and there's no reason to expect "duplicate" rows in the sense UNION cares about (each SELECT is inherently about a different store), UNION ALL is both simpler and more efficient here, there's no need to pay for deduplication that isn't actually needed.
5. How should the final result be sorted? By product_id, then store, then price, all ascending, a three-column sort.

Approach

Step 1: Build one SELECT per store column For store1: SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL. Repeat this same pattern for store2 and store3.

Step 2: Exclude NULL prices in each SELECT Each SELECT includes its own WHERE storeX IS NOT NULL condition, so products unavailable in that specific store simply produce no row for it.

Step 3: Combine all three SELECTs using UNION ALL Since each SELECT represents a genuinely different store (not a duplicate of the same information), UNION ALL is the appropriate, efficient choice.

Step 4: Sort the final combined result Apply ORDER BY product_id, store, price once, at the very end.

SQL Query

-- Turn the store1 column into its own set of (product_id, store, price) rows
SELECT product_id, 'store1' AS store, store1 AS price
FROM Products
WHERE store1 IS NOT NULL

UNION ALL

-- Turn the store2 column into its own set of (product_id, store, price) rows
SELECT product_id, 'store2' AS store, store2 AS price
FROM Products
WHERE store2 IS NOT NULL

UNION ALL

-- Turn the store3 column into its own set of (product_id, store, price) rows
SELECT product_id, 'store3' AS store, store3 AS price
FROM Products
WHERE store3 IS NOT NULL

ORDER BY product_id, store, price;

Query Explanation

• 'store1' AS store — this isn't pulling a value from a column, it's a literal, fixed text string being inserted into every row this specific SELECT produces, labeling every row from this SELECT as belonging to "store1."
• store1 AS price — renames the store1 column to a shared, common alias (price), so that when this result gets combined with the store2 and store3 SELECTs (which use the same alias, price, for their own respective columns), all three results line up correctly under one shared column name.
• WHERE store1 IS NOT NULL — ensures that if a specific product has no price in store1, that product simply produces no row at all from this particular SELECT, rather than producing a row with a NULL price.
• UNION ALL — combines all three SELECTs (one per store) into one single result set. UNION ALL (not UNION) is used here because there's no need for expensive deduplication, each SELECT is inherently distinguishing itself by its own store label, so there's no risk of two SELECTs accidentally producing identical rows that need collapsing.
• ORDER BY product_id, store, price — sorts the final combined result first by product_id, then alphabetically by store name, then by price, exactly as the requirement specifies.

Dry Run

Original table (wide format)

↓

SELECT 1: store1 column reshaped

SELECT product_id, 'store1' AS store, store1 AS price
FROM Products
WHERE store1 IS NOT NULL;

(Both products have a store1 price, so both produce a row here.)

↓

SELECT 2: store2 column reshaped

SELECT product_id, 'store2' AS store, store2 AS price
FROM Products
WHERE store2 IS NOT NULL;

(Product 1's store2 price is NULL, so product 1 produces no row here at all.)

↓

SELECT 3: store3 column reshaped

SELECT product_id, 'store3' AS store, store3 AS price
FROM Products
WHERE store3 IS NOT NULL;

(Both products have a store3 price, so both produce a row here.)

↓

Combine all three with UNION ALL

↓

Apply ORDER BY product_id, store, price

SQL Execution Flow

SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL
   ↓
UNION ALL
   ↓
SELECT product_id, 'store2' AS store, store2 AS price FROM Products WHERE store2 IS NOT NULL
   ↓
UNION ALL
   ↓
SELECT product_id, 'store3' AS store, store3 AS price FROM Products WHERE store3 IS NOT NULL
   ↓
Combine all three result sets (no deduplication, since UNION ALL is used)
   ↓
ORDER BY product_id, store, price
   ↓
Return final reshaped, sorted output

The table Products is effectively scanned three separate times here, once for each store column's SELECT, which is worth being aware of on very large tables, though this is simply the nature of reshaping a fixed, small number of columns into rows this way.

Time Complexity

Time Complexity: O(n) per store column scanned, so roughly O(3n), which simplifies to O(n), where n is the number of products, plus the cost of the final sort, O(r log r), where r is the number of rows in the final reshaped result (which is at most 3n, but often less, due to excluded NULLs).

Space Complexity: O(r), where r is the final reshaped row count The combined result needs to be held for sorting, but since UNION ALL performs no deduplication, no extra space is needed purely for duplicate-checking.

Why: Since there are only 3 store columns in this specific problem, the table is scanned a small, fixed number of times (3), which keeps this efficient and roughly linear overall, this would clearly scale worse if there were, say, 50 store columns needing the same treatment, each requiring its own SELECT and its own scan of the table.

Common Mistakes to Avoid

Mistake 1: Including rows with NULL prices instead of excluding them

Why it happens: It's easy to forget the WHERE clause for one or more of the three SELECTs, especially when copy-pasting the pattern quickly.

Wrong:

SELECT product_id, 'store2' AS store, store2 AS price
FROM Products;
-- missing: WHERE store2 IS NOT NULL

This would incorrectly include a row like (1, 'store2', NULL) in the final output, directly contradicting the requirement to exclude products unavailable in a given store.

Correct: Always include WHERE storeX IS NOT NULL in every one of the three SELECT statements, one for each store column.

Mistake 2: Using UNION instead of UNION ALL, adding unnecessary deduplication cost

Why it happens: UNION can feel like the "safer default," without considering whether deduplication is actually needed.

Less efficient (though not technically incorrect, since exact duplicate rows are genuinely unlikely here given each SELECT has a distinct store label):

SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' AS store, store2 AS price FROM Products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' AS store, store3 AS price FROM Products WHERE store3 IS NOT NULL
ORDER BY product_id, store, price;

Since each SELECT inherently produces rows labeled with a different, unique store name, there's no realistic way for two of these three SELECTs to ever produce an identical row, making the deduplication work of UNION pure wasted effort here.

Correct: Use UNION ALL, since the store label itself already guarantees uniqueness across the three SELECTs, there's nothing left for UNION's deduplication step to usefully do.

Mistake 3: Forgetting to alias the store column consistently across all three SELECTs

Why it happens: It's easy to accidentally leave one SELECT's price column with its original name (like store2), instead of renaming it to the shared price alias.

Wrong:

SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2' AS store, store2 FROM Products WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3' AS store, store3 AS price FROM Products WHERE store3 IS NOT NULL
ORDER BY product_id, store, price;

While this might still technically combine correctly by position (UNION matches columns positionally, not by name, as covered in the concept notes), it's a readability and maintenance risk, and the final result's column name would come from the very first SELECT anyway, making an inconsistent alias in later SELECTs confusing and easy to overlook during review.

Correct: Use the exact same alias (price) in every SELECT's third column, for clarity and consistency.

Mistake 4: Forgetting the literal string quotes around the store name

Why it happens: It's easy to mistakenly think store1 in the SELECT list refers to the actual column, when the intention is to insert the literal text "store1" as a label.

Wrong (this would actually try to select the store1 column's value, not the label "store1"):

SELECT product_id, store1 AS store, store1 AS price
FROM Products
WHERE store1 IS NOT NULL;

This mistakenly puts the price in both the store column and the price column, since store1 without quotes refers to the actual column, not literal text.

Correct: Use single quotes around the store name to make it a literal string: 'store1' AS store.

Interview Follow-up Questions

• "How would this query need to change if there were 10 stores instead of just 3?"
• "What would you do differently if the number of store columns could change dynamically, and wasn't known in advance?"
• "How would you reverse this transformation, converting the long-format result back into the original wide format?" (This introduces the concept of pivoting, the opposite operation.)
• "How would you modify this to also show which products have NO price in any store at all?"

Similar Problems

• Any "wide-to-long" or "unpivoting" reshape problem, where a fixed, known set of columns needs to become rows
• Problems reshaping survey data, quarterly financial columns (Q1, Q2, Q3, Q4), or multi-language translation columns into a normalized row-per-value format
• The opposite operation (long-to-wide, or "pivoting"), often built using CASE expressions combined with aggregate functions like SUM or MAX, similar to the conditional aggregation techniques covered in the CASE Expression notes

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Data is stored with one column per category (wide format), and needs to become one row per category (long format) → one SELECT per original column, combined with UNION ALL, each SELECT relabeling its column with a literal string
• A column can be NULL to represent "not applicable" or "not available" → filter that specific column's SELECT with WHERE columnName IS NOT NULL, so it simply produces no row, rather than a row with a NULL value
• Each SELECT in the UNION ALL chain inherently produces a distinguishable value (like a unique label) → UNION ALL is safe and efficient here, true duplicate rows are structurally very unlikely, making UNION's extra deduplication cost unnecessary