Why QUERY is Google Sheets' most powerful function - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this QUERY formula?

Given the data range A1:B5 with values:

A1: Name, B1: Score
A2: Alice, B2: 85
A3: Bob, B3: 90
A4: Carol, B4: 78
A5: Dave, B5: 92

What is the output of the formula:

=QUERY(A1:B5, "SELECT Col1 WHERE Col2 > 80 ORDER BY Col2 DESC")

A

Dave
Bob
Alice

B

Alice
Bob
Dave

C

Bob
Dave
Alice

D

Dave
Alice
Bob

💡 Hint

Think about filtering scores greater than 80 and sorting by score descending.

The QUERY selects names where scores are above 80 and orders them from highest to lowest score. Dave (92), Bob (90), and Alice (85) meet the condition, sorted descending.

❓ Function Choice

intermediate

Which QUERY formula sums scores by name?

You have data in A1:B6:

A1: Name, B1: Score
A2: Alice, B2: 10
A3: Bob, B3: 15
A4: Alice, B4: 20
A5: Bob, B5: 25
A6: Carol, B6: 30

Which QUERY formula correctly sums scores for each name?

A =QUERY(A1:B6, "SELECT Col1, SUM(Col2) ORDER BY Col2")

B =QUERY(A1:B6, "SELECT Col1, SUM(Col2) GROUP BY Col1")

C =QUERY(A1:B6, "SELECT Col1, Col2 SUM GROUP BY Col1")

D =QUERY(A1:B6, "SELECT Col1, SUM(Col2) WHERE Col2 > 10")

💡 Hint

Grouping is needed to sum scores by name.

Option B uses correct syntax: SELECT Col1, SUM(Col2) GROUP BY Col1. Others have syntax errors or wrong clauses.

❓ data_analysis

advanced

How many rows does this QUERY return?

Given data in A1:C5:

A1: Product, B1: Category, C1: Price
A2: Pen, B2: Stationery, C2: 1.5
A3: Pencil, B3: Stationery, C3: 0.5
A4: Mug, B4: Kitchen, C4: 5
A5: Plate, B5: Kitchen, C5: 7

What is the number of rows returned by:

=QUERY(A1:C5, "SELECT Col1, Col3 WHERE Col2 = 'Stationery' AND Col3 > 1")

A 1

B 4

C 3

D 2

💡 Hint

Filter rows where Category is Stationery and Price is greater than 1.

Only 'Pen' matches both conditions (Stationery and Price > 1). Pencil is Stationery but price is 0.5, so excluded.

🎯 Scenario

advanced

Which QUERY formula extracts unique categories sorted alphabetically?

You have data in A1:B6:

A1: Item, B1: Category
A2: Chair, B2: Furniture
A3: Table, B3: Furniture
A4: Apple, B4: Fruit
A5: Banana, B5: Fruit
A6: Pen, B6: Stationery

Which QUERY formula returns a list of unique categories sorted A to Z?

A =QUERY(A1:B6, "SELECT Col2 ORDER BY Col2")

B =QUERY(A1:B6, "SELECT UNIQUE(Col2) ORDER BY Col2")

C =QUERY(A1:B6, "SELECT Col2 GROUP BY Col2 ORDER BY Col2")

D =QUERY(A1:B6, "SELECT Col2 WHERE Col2 IS UNIQUE ORDER BY Col2")

💡 Hint

Use GROUP BY to get unique values in QUERY.

Option C correctly groups by category and orders them alphabetically. UNIQUE() and IS UNIQUE are not valid in QUERY syntax.

🧠 Conceptual

expert

What error does this QUERY formula produce?

Given data in A1:B4:

A1: Name, B1: Age
A2: John, B2: 25
A3: Jane, B3: 30
A4: Joe, B4: 22

What error occurs when using:

=QUERY(A1:B4, "SELECT A, SUM(B) GROUP BY A")

A #VALUE! error

B #DIV/0! error

C #REF! error

D #NAME? error

💡 Hint

QUERY does not support column letters like A or B; use Col1, Col2, or header names.

The query uses invalid column identifiers 'A' and 'B'. Valid options are 'Col1', 'Col2', etc., or exact header labels like 'Name'. This produces a #NAME? error.

Practice

1. What is the main advantage of using the QUERY function in Google Sheets?

easy

A. It lets you filter and organize data without changing the original table.

B. It automatically formats your spreadsheet with colors.

C. It creates charts from your data instantly.

D. It deletes duplicate rows from your data.

Solution

1. Step 1: Understand QUERY's purpose

   The QUERY function is designed to filter, sort, and summarize data without altering the original dataset.

2. Step 2: Compare options to QUERY's features

   Options B, C, and D describe other functions or manual actions, not QUERY's main use.

3. Final Answer:

   It lets you filter and organize data without changing the original table. -> Option A

4. Quick Check:

   QUERY filters and organizes data [OK]

Hint: QUERY filters data without changing original table [OK]

Common Mistakes:

• Thinking QUERY formats cells automatically
• Confusing QUERY with chart creation
• Assuming QUERY deletes data

2. Which of the following is the correct syntax to select columns A and B from a range using QUERY?

easy

A. =QUERY(A1:C10, "SELECT 1, 2")

B. =QUERY(A1:C10, "SELECT Col1, Col2")

C. =QUERY(A1:C10, "SELECT A, B")

D. =QUERY(A1:C10, "SELECT A:B")

Solution

1. Step 1: Recall QUERY column naming

   In QUERY, columns are referenced by Col1, Col2, etc. in the SELECT clause.

2. Step 2: Check each option's syntax

   =QUERY(A1:C10, "SELECT Col1, Col2") uses correct syntax. =QUERY(A1:C10, "SELECT 1, 2") uses invalid numbers, =QUERY(A1:C10, "SELECT A, B") uses invalid letters, =QUERY(A1:C10, "SELECT A:B") uses invalid range format.

3. Final Answer:

   =QUERY(A1:C10, "SELECT Col1, Col2") -> Option B

4. Quick Check:

   Use Col1, Col2 in SELECT [OK]

Hint: Use Col1, Col2 in QUERY SELECT clause [OK]

Common Mistakes:

• Using numbers instead of Col1, Col2 for columns
• Trying to select a range like A:B inside SELECT
• Using column letters A, B instead of Col1, Col2

3. Given the data in A1:B5:

A    B
1    10
2    20
3    30
4    40
5    50

What will =QUERY(A1:B5, "SELECT Col1 WHERE Col2 > 25") return?

medium

A. [1, 2]

B. [30, 40, 50]

C. [3, 4, 5]

D. [10, 20, 30]

Solution

1. Step 1: Understand the QUERY condition

   The query selects column A (Col1) where values in column B (Col2) are greater than 25.

2. Step 2: Identify rows where B > 25

   Rows with B=30, 40, 50 correspond to A=3, 4, 5.

3. Final Answer:

   [3, 4, 5] -> Option C

4. Quick Check:

   Filter B > 25, select A [OK]

Hint: Filter rows by condition, then select column [OK]

Common Mistakes:

• Selecting values from column B instead of A
• Including rows where B ≤ 25
• Confusing column letters with values

4. The formula =QUERY(A1:C10, "SELECT Col1, Col2, SUM(Col3) GROUP BY Col1") returns an error. What is the likely cause?

medium

A. All selected columns must be in GROUP BY or aggregated

B. SUM(Col3) cannot be used without aliasing

C. Column C must be included in SELECT when grouping

D. Missing a WHERE clause before GROUP BY

Solution

1. Step 1: Understand GROUP BY rules in QUERY

   When using GROUP BY, all selected columns must be either grouped or aggregated.

2. Step 2: Analyze the formula's SELECT clause

   Col1 is grouped, SUM(Col3) is aggregated, but Col2 is neither, causing the error.

3. Final Answer:

   All selected columns must be in GROUP BY or aggregated -> Option A

4. Quick Check:

   GROUP BY requires all columns grouped or aggregated [OK]

Hint: Group or aggregate all selected columns [OK]

Common Mistakes:

• Thinking WHERE is required before GROUP BY
• Believing aliasing fixes aggregation errors
• Ignoring GROUP BY rules for selected columns

5. You have a sales table with columns Date (A), Product (B), and Sales (C). How would you write a QUERY to find total sales per product for sales greater than 100, sorted by total sales descending?

hard

A. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) GROUP BY Col2 WHERE Col3 > 100 ORDER BY SUM(Col3) DESC")

B. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) ORDER BY SUM(Col3) DESC WHERE Col3 > 100 GROUP BY Col2")

C. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 ORDER BY SUM(Col3) DESC GROUP BY Col2")

D. =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 GROUP BY Col2 ORDER BY SUM(Col3) DESC")

Solution

1. Step 1: Identify correct QUERY clause order

   The correct order is SELECT, WHERE, GROUP BY, ORDER BY.

2. Step 2: Check each option's clause order and syntax

   =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 GROUP BY Col2 ORDER BY SUM(Col3) DESC") follows correct order and syntax. Options A, C, and D have clauses in wrong order causing errors.

3. Final Answer:

   =QUERY(A1:C100, "SELECT Col2, SUM(Col3) WHERE Col3 > 100 GROUP BY Col2 ORDER BY SUM(Col3) DESC") -> Option D

4. Quick Check:

   Clause order: SELECT, WHERE, GROUP BY, ORDER BY [OK]

Hint: Remember QUERY clause order: SELECT, WHERE, GROUP BY, ORDER BY [OK]

Common Mistakes:

• Placing WHERE after GROUP BY or ORDER BY
• Forgetting to group by product when summing sales
• Incorrect clause order causing syntax errors