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.