Learn SQL one idea at a time.

You should see one row for each category and every column stored on the categories table. The exact row order is not part of the question.

Read the column headings first, then scan a few rows. The result is a direct view of the table; the query has not filtered, sorted, or changed any values.

One output row per product with exactly three columns: sku, name, and unit_price.

The number of product rows is unchanged; only the width of the result is smaller. Naming columns also makes the query easier to understand later.

Two columns named product_name and price, with one row for every product.

Compare the headings with Q002. The values come from the same stored columns, but the result now has names chosen by the query.

Each brand appears once, even when the store has several products from that brand.

The result answers “which brands exist?” rather than “which product rows exist?” The order is not guaranteed because no ORDER BY is present.

Products appear from the lowest SKU value to the highest SKU value.

Look at the sku column from top to bottom. The ordering is now part of the answer, not an accidental property of how PostgreSQL happened to read the table.

The highest SKU sorts first and the lowest SKU sorts last.

Compare this result with Q005: it should contain the same rows in the opposite SKU direction.

Brands are grouped alphabetically; inside a brand, cheaper products come first; tied prices sort by SKU.

Pick one brand with several products and verify the second and third rules inside that brand. This is the basic pattern for deterministic multi-key ordering.

Exactly five products: the five cheapest, with price ties resolved by SKU.

This is a common “top N” pattern. The important idea is that LIMIT is applied to an ordered result so the chosen five are predictable.

Every returned row has status delivered; orders in other states are absent.

Scan the status column: the filter should make every visible value identical.

Only products with unit_price greater than 100.00.

Use the price column to verify the boundary: no returned value should be 100.00 or less.

Only products whose active flag is false.

These are products the store retains in its history but no longer considers active.

Products that are both active and cheaper than 25.00.

A row that satisfies only one of the two rules must not appear.

Only cancelled and refunded orders.

The result should contain two possible status values and no others.

Inactive customers only.

This demonstrates that SQL conditions can be composed, not only compared with operators.

Orders placed through web or mobile, and no orders from other channels.

IN is equivalent here to two equality checks joined with OR, but is easier to scan.

Products priced from 25.00 through 50.00, inclusive.

Check boundary rows carefully; values at exactly 25.00 or 50.00 are valid matches.

Customers whose email value is missing; the email cell is NULL for each row.

This is the basic pattern for finding incomplete data.

Customers with a stored phone number.

A quick scan should show a concrete phone value on every row.

Only products whose SKU begins with ANCHOR-.

These are deliberately controlled products in the practice dataset, making the prefix easy to verify.

Products whose name contains mouse in any mixture of upper- or lower-case letters.

The condition changes how matching works; it does not convert the displayed name to another case.

Each product row shows its stored unit_price and a calculated price_with_tax.

Use one row to confirm that price_with_tax is exactly unit_price multiplied by 1.13.

One full_name value per customer containing first name, one space, then last name.

The database is formatting a display value from existing columns; the stored names remain separate.

Every brand_upper value is the product brand displayed in upper case.

This is a simple example of applying the same scalar function independently to every selected row.

Each product has its name plus an integer showing how many characters that name contains.

Pick a short name and count its characters manually to connect the function with the output.

Every order shows its order_number and the calendar year taken from ordered_at.

Orders from different years should display the corresponding year value while keeping the same order identity.

For each customer, the calculated timestamp is exactly 30 days after signup_at.

Compare the two timestamp columns on the same row; only the date/time calculation changes.

Every row has a display_email value, even when the stored email is NULL.

Customers with real email addresses keep them; only missing emails receive the fallback text.

Every product receives a readable activity_label of Active or Inactive.

CASE translates a stored machine-friendly value into a user-friendly label inside the result.

The final result contains sku and name for active products only.

The CTE does not add new data; it gives the filtered intermediate result a clear name. This becomes valuable when a larger query has several understandable stages.

One row per product with sku and the calculated price_with_tax column.

Read the statement from top to bottom as two steps: first calculate, then select the final shape. This is the mental model we will reuse in more complex exercises.

One row per order, with order_number and the matching customer's external_ref.

The output grain is still one row per order. The join did not summarize anything; it only made customer information available beside each order.

One output row per order item, containing order_item_id, sku, and quantity.

Notice that a product can appear on many order-item rows. The join repeats the product's SKU on each matching transaction row because the output grain is one row per order item.

One row per order line with order_number, sku, and quantity.

An order containing three products appears three times because the result grain is an order line, not an order. That row multiplication is expected and is exactly why identifying grain matters before joining.

Product categories appear once per matching product, and the intentionally empty Seasonal category appears with a NULL sku.

The NULL does not mean the category name is missing. It means this preserved category row had no matching product row on the right side.

One row per product. Products without an inventory row show NULL; zero-stock products show 0.

NULL and 0 mean different things here: NULL means no inventory record exists, while 0 means an inventory record exists and explicitly says nothing is currently on hand.

At least the empty Seasonal category with NULL sku and the ANCHOR-UNCATEGORIZED product with NULL category_name.

Rows with NULL sku are left-only categories; rows with NULL category_name are right-only products. Those two directions are the defining difference between FULL OUTER JOIN and LEFT JOIN.

Exactly one row and one column named order_count.

The number is the table cardinality: the total number of order records currently loaded.

One row with units_ordered equal to the sum of all order-line quantities.

Compare the meaning with COUNT(*): row count and unit count are different business measures.

One row with the overall average product price.

The average is a summary of the product catalog, not the price of any particular product.

One row containing the cheapest unit_price in the catalog.

This tells you the lower boundary of product prices but does not identify which product owns that price.

One row containing the highest unit_price in the catalog.

This gives the upper price boundary, not the whole product row associated with it.

One row per distinct status with the number of orders in that status.

Adding all order_count values should reproduce the overall order count from Q037.

One row for each sales channel with that channel's summed order value.

This is intentionally 'order value,' not accounting revenue: the dataset includes statuses such as cancelled and refunded, and the query does not exclude them. Precise business definitions matter.

One row for each status and sales_channel combination that actually occurs.

The result grain is not one row per status and not one row per channel; it is one row per pair. State that grain before interpreting the numbers.

One row containing the number of different customers represented in orders.

This is the number of ordering customers, which is lower than or equal to both total orders and total customers.

Customers with at least five orders, including the deterministic frequent-customer anchor.

Every returned row has one customer_id and that customer's aggregate order_count.

One row per product that has sold, with total units_sold.

Never-sold products do not appear because an INNER JOIN starts from order_items, which has no row for them.

Every category appears; Seasonal has product_count 0.

This is the standard pattern for 'show every parent and how many children it has, including zero.'

One row with total_orders, delivered_orders, and cancelled_orders.

Each conditional count is a subset of total_orders, and the three calculations share the same input table scan logically.

Products whose unit_price is strictly greater than the overall average product price.

The average is calculated from the same products table but is used as a benchmark for each outer row.

One row for each customer who has placed at least one order.

Customers with many orders still appear once because the outer result grain remains one row per customer.

Customers with no matching order rows, including CUST-000002.

This is an anti-match: the output shows absence of a related row rather than presence.

Two distinct brand rows: Northstar and Harbour Home. The row order is not guaranteed.

The inputs were chosen from deterministic anchor products so the exercise demonstrates UNION consistently on every supported dataset profile and seed.

Two rows, both containing Northstar.

The duplicate is intentional evidence of UNION ALL behavior. Replacing UNION ALL with UNION would collapse these two identical rows to one.

Exactly one row: Northstar.

Because both deterministic inputs contain the same value, the overlap is guaranteed and the meaning of INTERSECT is visible without relying on random background data.

Exactly one row: Northstar.

Direction matters. Reversing the two SELECT statements would instead return Harbour Home.

Every product remains visible and receives a unique row number from 1 upward.

Unlike GROUP BY, no product rows disappear. The new number is additional information attached to each product row.

Products numbered independently within category, with the most expensive categorized item numbered 1 in each group.

This is row-level data plus a within-group position; it is not a grouped summary.

Equal prices share rank; after a tie, the next rank contains a gap.

Use the intentionally tied mouse prices to observe how equal values receive the same rank.

Equal prices share rank and the following distinct price receives the next consecutive rank.

Compare Q059 and Q060 around a tied price to see the exact behavioral difference between RANK and DENSE_RANK.

One row per active month; the first row has NULL previous_month_count and later rows show the prior month's count.

Now current and previous counts are on the same row, which is the starting point for calculating month-over-month changes or detecting spikes.

The five anchor orders for CUST-000001 in chronological order, each pointing to the next order except the last.

Subtracting ordered_at from next_ordered_at later could measure time between purchases, but this lesson focuses only on LEAD itself.

One output row per order, plus a repeated customer-level total beside each order.

Compare the row count with orders: it is unchanged, even though a customer-level aggregate has been calculated.

Orders in chronological order with running_order_value increasing by the current row's total_amount each time.

The first running total equals the first order value; the final running total equals the sum of all order values.

One latest order for every customer who has at least one order.

Customers with no orders cannot appear because the ranking starts from the orders table.

Exactly the two most expensive categorized products from each populated category, with deterministic price-tie handling.

LIMIT 2 would keep two rows overall; ranking within partitions is what makes the limit effectively apply separately to every category.

One row for every customer who has at least one order, with that customer's order count.

The important change happens inside the CTE: the grain becomes one row per customer_id. The outer query does not recalculate anything; it simply makes the intermediate result visible.

One row per ordering customer, showing a readable external_ref beside that customer's order count.

The join does not change what the count means. It only replaces an internal numeric customer key with the external identifier that a person can recognize more easily.

One customer-level row containing customer_id, order_count, and total_spend.

Both CTEs have the same grain, so joining them on customer_id is straightforward. The main lesson is structural: separate calculations can be named and combined later.

Exactly five rows representing the five highest customer spending totals in deterministic rank order.

Read the query from top to bottom as a small pipeline. Each CTE has one responsibility, so the final filtering rule is easy to understand without re-reading the aggregation and ranking logic.

One row per sales channel that has at least one shipped or delivered order, with its fulfilled order count.

The CTE name carries meaning that the raw status list does not. In larger queries, naming important business subsets can make the SQL much easier to review and maintain.

One row for every order, showing its order_number and total number of units across all order lines.

The CTE deliberately changes line-item detail into an order-level summary before the join. That makes the grain of the final result easy to predict and protects later calculations from accidental duplication.