Query with sort key conditions in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Query with sort key condition using BETWEEN

Given a DynamoDB table Orders with CustomerID as the partition key and OrderDate as the sort key (stored as ISO date strings), which query will return all orders for CustomerID = 'C123' placed between 2023-01-01 and 2023-01-31 inclusive?

A KeyConditionExpression = 'CustomerID = :cid AND OrderDate > :start AND OrderDate < :end', ExpressionAttributeValues = {':cid': 'C123', ':start': '2023-01-01', ':end': '2023-01-31'}

B KeyConditionExpression = 'CustomerID = :cid AND OrderDate >= :start AND OrderDate <= :end', ExpressionAttributeValues = {':cid': 'C123', ':start': '2023-01-01', ':end': '2023-01-31'}

C KeyConditionExpression = 'CustomerID = :cid AND OrderDate BETWEEN :start AND :end', ExpressionAttributeValues = {':cid': 'C123', ':start': '2023-01-01', ':end': '2023-01-31'}

D KeyConditionExpression = 'CustomerID = :cid AND OrderDate = :start', ExpressionAttributeValues = {':cid': 'C123', ':start': '2023-01-01'}

💡 Hint

Use the BETWEEN operator to include both start and end dates.

Option C uses BETWEEN which includes both boundary values. Option C is invalid because it uses two conditions on the sort key, which is not allowed in KeyConditionExpression. Option C excludes boundary dates. Option C only queries one date.

❓ query_result

intermediate

Query with sort key condition using begins_with

In a DynamoDB table Messages with UserID as partition key and MessageID as sort key, which query will return all messages for UserID = 'U456' where MessageID starts with '2023-06-'?

A KeyConditionExpression = 'UserID = :uid AND begins_with(MessageID, :prefix)', ExpressionAttributeValues = {':uid': 'U456', ':prefix': '2023-06-'}

B KeyConditionExpression = 'UserID = :uid AND MessageID = :prefix', ExpressionAttributeValues = {':uid': 'U456', ':prefix': '2023-06-'}

C KeyConditionExpression = 'UserID = :uid AND MessageID BETWEEN :prefix AND :prefixEnd', ExpressionAttributeValues = {':uid': 'U456', ':prefix': '2023-06-', ':prefixEnd': '2023-06-99'}

D KeyConditionExpression = 'UserID = :uid AND contains(MessageID, :prefix)', ExpressionAttributeValues = {':uid': 'U456', ':prefix': '2023-06-'}

💡 Hint

Use the begins_with function to filter sort keys starting with a prefix.

Option A correctly uses begins_with on the sort key. Option A checks for exact match, which is too strict. Option A tries BETWEEN but the end value is invalid and may miss some keys. Option A uses contains which is not allowed in KeyConditionExpression.

📝 Syntax

advanced

Identify the syntax error in sort key condition

Which of the following KeyConditionExpression strings will cause a syntax error when querying a DynamoDB table with partition key PK and sort key SK?

A PK = :pk AND SK > :sk

B PK = :pk AND SK BETWEEN :start AND :end

C PK = :pk AND begins_with(SK, :prefix)

D PK = :pk AND SK IN (:sk1, :sk2)

💡 Hint

Check which operators are allowed in KeyConditionExpression.

The IN operator is not supported in KeyConditionExpression. Options A, C, and D use valid operators: >, BETWEEN, and begins_with.

❓ optimization

advanced

Optimize query to reduce read capacity usage

You want to query a DynamoDB table Events with partition key EventType and sort key EventTimestamp. You want to get all events of type 'click' after 2024-01-01T00:00:00Z. Which query condition is most efficient to minimize read capacity units consumed?

A KeyConditionExpression = 'EventType = :et AND EventTimestamp >= :ts', ExpressionAttributeValues = {':et': 'click', ':ts': '2024-01-01T00:00:00Z'}

B FilterExpression = 'EventTimestamp >= :ts', KeyConditionExpression = 'EventType = :et', ExpressionAttributeValues = {':et': 'click', ':ts': '2024-01-01T00:00:00Z'}

C KeyConditionExpression = 'EventType = :et', ExpressionAttributeValues = {':et': 'click'}

D FilterExpression = 'EventType = :et AND EventTimestamp >= :ts', ExpressionAttributeValues = {':et': 'click', ':ts': '2024-01-01T00:00:00Z'}

💡 Hint

Use key conditions to reduce data scanned, not filters.

Option A uses both partition key and sort key conditions in KeyConditionExpression, which efficiently narrows data. Option A and D use FilterExpression which filters after reading data, consuming more read capacity. Option A does not filter by timestamp, returning more data.

🧠 Conceptual

expert

Understanding limitations of sort key conditions

Which statement about DynamoDB KeyConditionExpression for sort keys is FALSE?

A The <code>begins_with</code> function only works on string sort keys.

B You can combine multiple sort key conditions with <code>AND</code> to form complex filters.

C You can use <code>=</code>, <code>&gt;</code>, <code>&lt;</code>, <code>BETWEEN</code>, and <code>begins_with</code> operators on sort keys.

D Sort key conditions must be part of the <code>KeyConditionExpression</code> and cannot be in <code>FilterExpression</code>.

💡 Hint

Consider how many conditions on sort key are allowed in KeyConditionExpression.

Option B is false because you cannot combine multiple sort key conditions with AND; only one condition on the sort key is allowed in KeyConditionExpression. Options A, B, and D are true statements.

Practice

1. In DynamoDB, when you use a Query operation with a sort key condition, which of the following is always required?

easy

A. Specify only the sort key condition without the partition key

B. Use a scan operation instead of query

C. Specify the partition key with '=' and a condition on the sort key

D. Specify only the partition key without any sort key condition

Solution

1. Step 1: Understand Query requirements

   A DynamoDB Query must always specify the partition key with '=' to identify the partition.

2. Step 2: Add sort key condition

   You can add conditions on the sort key to filter items within that partition.

3. Final Answer:

   Specify the partition key with '=' and a condition on the sort key -> Option C

4. Quick Check:

   Partition key '=' + sort key condition = required [OK]

Hint: Partition key '=' is mandatory in Query with sort key condition [OK]

Common Mistakes:

• Trying to query without specifying partition key
• Using scan instead of query for sort key filtering
• Specifying only sort key condition without partition key

2. Which of the following is the correct syntax for a DynamoDB Query with a sort key condition to find items where the sort key Timestamp is greater than 1000?

easy

A. KeyConditionExpression: 'PartitionKey > :pk AND Timestamp > :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

B. KeyConditionExpression: 'PartitionKey = :pk OR Timestamp > :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

C. KeyConditionExpression: 'PartitionKey = :pk AND Timestamp == :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

D. KeyConditionExpression: 'PartitionKey = :pk AND Timestamp > :ts', ExpressionAttributeValues: { ':pk': 'User1', ':ts': 1000 }

Solution

1. Step 1: Use '=' for partition key

   The partition key must be compared with '=' in the KeyConditionExpression.

2. Step 2: Use '>' for sort key condition

   The sort key condition can use operators like '>' to filter items.

3. Final Answer:

   KeyConditionExpression: 'PartitionKey = :pk AND Timestamp > :ts' -> Option D

4. Quick Check:

   PartitionKey '=' and sort key '>' correct syntax [OK]

Hint: Partition key '=' and sort key condition combined with AND [OK]

Common Mistakes:

• Using OR instead of AND in KeyConditionExpression
• Using '>' for partition key instead of '='
• Using '==' instead of '=' for partition key

3. Given a DynamoDB table with partition key UserID and sort key OrderDate, what will the following query return?

KeyConditionExpression: 'UserID = :uid AND OrderDate BETWEEN :start AND :end'
ExpressionAttributeValues: { ':uid': 'user123', ':start': '2023-01-01', ':end': '2023-01-31' }

medium

A. All orders for 'user123' placed between January 1 and January 31, 2023 inclusive

B. All orders for all users placed between January 1 and January 31, 2023

C. All orders for 'user123' placed before January 1, 2023

D. Syntax error due to BETWEEN usage in KeyConditionExpression

Solution

1. Step 1: Partition key '=' filters user

   The query filters items where UserID equals 'user123'.

2. Step 2: Sort key BETWEEN filters date range

   The BETWEEN operator selects OrderDate values from '2023-01-01' to '2023-01-31' inclusive.

3. Final Answer:

   All orders for 'user123' placed between January 1 and January 31, 2023 inclusive -> Option A

4. Quick Check:

   Partition key '=' + sort key BETWEEN returns filtered range [OK]

Hint: BETWEEN filters sort key range within one partition [OK]

Common Mistakes:

• Assuming query returns all users' orders
• Thinking BETWEEN excludes boundary dates
• Believing BETWEEN is invalid in KeyConditionExpression

4. You wrote this DynamoDB query but it returns no results:

KeyConditionExpression: 'UserID = :uid AND OrderDate > :date'
ExpressionAttributeValues: { ':uid': 'user123', ':date': '2023-12-31' }

What is the most likely reason?

medium

A. No items exist with OrderDate after 2023-12-31 for user123

B. Using '>' operator is not allowed in KeyConditionExpression

C. Partition key UserID should use '>' instead of '='

D. ExpressionAttributeValues keys must not start with ':'

Solution

1. Step 1: Check operator validity

   The '>' operator is valid for sort key conditions in DynamoDB queries.

2. Step 2: Consider data existence

   If no items have OrderDate after '2023-12-31' for 'user123', query returns empty.

3. Final Answer:

   No items exist with OrderDate after 2023-12-31 for user123 -> Option A

4. Quick Check:

   Valid syntax but no matching data = empty result [OK]

Hint: Empty results often mean no matching data, not syntax error [OK]

Common Mistakes:

• Thinking '>' is invalid in KeyConditionExpression
• Using '>' for partition key instead of '='
• Misunderstanding ExpressionAttributeValues syntax

5. You want to query a DynamoDB table with partition key CustomerID and sort key InvoiceDate. You need to find all invoices for CustomerID = 'C123' where InvoiceDate is either before '2023-01-01' or after '2023-12-31'. Which approach correctly achieves this?

hard

A. Use KeyConditionExpression with BETWEEN operator for the two date ranges

B. Run two separate queries: one with InvoiceDate < '2023-01-01' and another with InvoiceDate > '2023-12-31', then combine results

C. Use a scan operation with filter expression for the date conditions

D. Use a single query with KeyConditionExpression: CustomerID = :cid AND InvoiceDate < '2023-01-01' OR InvoiceDate > '2023-12-31'

Solution

1. Step 1: Understand KeyConditionExpression limits

   DynamoDB KeyConditionExpression supports only AND between partition key and sort key conditions, no OR.

2. Step 2: Query with OR on sort key requires multiple queries

   To get items before '2023-01-01' OR after '2023-12-31', run two queries and merge results.

3. Final Answer:

   Run two separate queries: one with InvoiceDate < '2023-01-01' and another with InvoiceDate > '2023-12-31', then combine results -> Option B

4. Quick Check:

   OR on sort key = multiple queries combined [OK]

Hint: DynamoDB Query KeyConditionExpression uses AND only; split OR into queries [OK]

Common Mistakes:

• Trying to use OR in KeyConditionExpression
• Using BETWEEN for non-continuous ranges
• Using scan instead of efficient queries