US-East KV Store Entries
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- Amazon
Problem
Pull every KV store entry in the us-east-1 region. For each entry, return the entry ID, key, value, TTL in seconds, region, creation time, and expiration time.
KV store inventory. us-east-1.
Tables
kv_store
Columns: entry_id INTEGER, kv_key TEXT, kv_value TEXT, ttl_secs INTEGER, region TEXT, created TEXT, expires TEXT
| entry_id | kv_key | kv_value | ttl_secs | region | created | expires |
|---|---|---|---|---|---|---|
| 141 | cache:1001 | 42 | 97 | us-west-2 | 2026-02-02 01:00:00 | 2026-03-02 06:00:00 |
| 182 | config:1002 | active | 134 | eu-west-1 | 2026-03-03 02:00:00 | 2026-04-03 07:00:00 |
| 223 | lock:1003 | {"user_id": 3, "role": "admin"} | 171 | ap-south-1 | 2026-04-04 03:00:00 | 2026-05-04 08:00:00 |
| 264 | rate:1004 | n/a | 208 | us-east-1 | 2026-05-05 04:00:00 | 2026-06-05 09:00:00 |
| 305 | user:1005 | active | NULL | us-west-2 | 2026-06-06 05:00:00 | NULL |
| 346 | temp:1006 | {"user_id": 6, "role": "admin"} | 282 | eu-west-1 | 2026-07-07 06:00:00 | 2026-08-07 11:00:00 |
| 387 | flag:1007 | 294 | 319 | ap-south-1 | 2026-08-08 07:00:00 | 2026-09-08 12:00:00 |
| 428 | session:1008 | active | 356 | us-east-1 | 2026-09-09 08:00:00 | 2026-10-09 13:00:00 |
| 469 | cache:1009 | {"user_id": 9, "role": "admin"} | 393 | us-west-2 | 2026-01-10 09:00:00 | 2026-02-10 14:00:00 |
| 510 | config:1010 | 420 | NULL | eu-west-1 | 2026-02-11 10:00:00 | NULL |
Expected output
| entry_id | kv_key | kv_value | ttl_secs | region | created | expires |
|---|---|---|---|---|---|---|
| 264 | rate:1004 | n/a | 208 | us-east-1 | 2026-05-05 04:00:00 | 2026-06-05 09:00:00 |
| 428 | session:1008 | active | 356 | us-east-1 | 2026-09-09 08:00:00 | 2026-10-09 13:00:00 |
| 592 | rate:1012 | {"user_id": 12, "role": "admin"} | 504 | us-east-1 | 2026-04-13 12:00:00 | 2026-05-13 17:00:00 |
| 756 | session:1016 | 672 | 652 | us-east-1 | 2026-08-17 16:00:00 | 2026-09-17 21:00:00 |
| 920 | rate:1020 | active | NULL | us-east-1 | 2026-03-21 20:00:00 | NULL |
Worked solution and explanation
Why this problem exists in real interviews
This is a basic single-table filter. Interviewers use it to verify that you can write a clean WHERE clause and select all requested columns.
Break down the requirements
Step 1: Filter to us-east-1 region
WHERE region = 'us-east-1' restricts the 20M row table to entries in the target region.
Step 2: Select all requested columns
Return entry_id, kv_key, kv_value, ttl_secs, region, created, and expires.
The solution
SELECT entry_id, kv_key, kv_value, ttl_secs, region, created, expires
FROM kv_store
WHERE region = 'us-east-1'Common follow-up questions
- How would you find entries that have already expired? (Add AND expires < DATETIME('now') to filter expired entries.)
- What if you needed entries from multiple regions? (Use WHERE region IN ('us-east-1', 'us-west-2') for multi-region queries.)
- How would the performance change if the table were partitioned by region? (Partition pruning eliminates scanning other regions entirely.)